I've got this:
Module: Loaded SQL rlm_sql (sql): Driver rlm_sql_postgresql (module rlm_sql_postgresql) loaded
and linked
rlm_sql (sql): Attempting to connect to [EMAIL PROTECTED]:/radacct
rlm_sql (sql): starting 0
rlm_sql (sql): Attempting to connect rlm_sql_postgresql #0
rlm_sql (sql): Connected new DB handle, #0
.
.
.
rlm_sql (sql): starting 24
rlm_sql (sql): Attempting to connect rlm_sql_postgresql #24
rlm_sql (sql): Connected new DB handle, #24
Module: Instantiated sql (sql)
Is it right if it goes up to 24 ?
Yes.
In radiusd.conf, we changed the following:
- bind_address = *
+ bind_address = 1.2.3.4
- port = 0
+ port = 1812
- with_cisco_vsa_hack = no
+ with_cisco_vsa_hack = yes
- $INCLUDE ${confdir}/sql.conf
+ $INCLUDE ${confdir}/pgsql-voip.conf # (cfr here attached)
accounting {
+ sql
}And instead of the sql.conf, we based our config on the attached file pgsql-voip.conf.
Check this link for more info:
http://www.freeradius.org/cgi-bin/cvsweb.cgi/%7Echeckout%7E/radiusd/src/billing/README?rev=1.5&content-type=text/plain
What you can do also to test is activate the following in pgsql-voip.conf:
# Print all SQL statements when in debug mode (-x)
sqltrace = yes
sqltracefile = ${logdir}/sqltrace.sqlWhen you start freeradius -x, you should see something like this when FreeRADIUS is receiving packets:
...rlm_sql...
Listening on IP address 1.2.3.4, ports 1812/udp and 1813/udp.
Ready to process requests.
rad_recv: Accounting-Request packet from host 5.6.7.8:1646, id=18, length=452
Acct-Session-Id = "0A00000000000080"
Calling-Station-Id = "123"
Called-Station-Id = "0123456789"
h323-setup-time = "h323-setup-time=15:05:24.010 UTC Fri Apr 9 2004"
h323-gw-id = "h323-gw-id=mygwid"
h323-conf-id = "h323-conf-id=002E1B53 3AA8911D 0C002E65 0A1F0207"
h323-call-origin = "h323-call-origin=originate"
h323-call-type = "h323-call-type=VoIP"
Cisco-AVPair = "h323-incoming-conf-id=002E1B53 3AA8911D 0C002E65 0A1F0207"
User-Name = "user-name"
Cisco-AVPair = "connect-progress=Call Up"
Acct-Status-Type = Start
Service-Type = Login-User
NAS-IP-Address = 5.6.7.8
Acct-Delay-Time = 0
rlm_sql (sql): Reserving sql socket id: 24
rlm_sql_postgresql: query: INSERT INTO StartVoIP (RadiusServerName, UserName, NASIPAddress, AcctTime, CalledStationId, CallingStationId, AcctDelayTime, h323gwid, h323callorigin, h323setuptime, h323confid) VALUES ('myservername', 'user-name', '5.6.7.8', now(), '0123456789', '123', '0', 'mygwid', 'originate', strip_dot('15:05:24.010 UTC Fri Apr 9 2004'), '002E1B53 3AA8911D 0C002E65 0A1F0207')
rlm_sql_postgresql: Status: PGRES_COMMAND_OK
rlm_sql_postgresql: affected rows = 1
rlm_sql (sql): Released sql socket id: 24
Sending Accounting-Response of id 18 to 5.6.7.8:1646
I hope this will help you...
Thanks for your help!
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Pascal Polleunus Sent: Tuesday, April 20, 2004 11:01 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Freeradius + PostgreSQL not working
VoipOne NOC wrote:
Hi
I have freeradius 0.9.3, compiled on a Debian Unstable system for PostgreSQL support.
Once I installe everything, it seems to work right. Following is the final output from "freeradius -xxyz -l stdout" :
Do you have something like this: Module: Loaded SQL rlm_sql (sql): Driver rlm_sql_postgresql (module rlm_sql_postgresql) loaded and linked rlm_sql (sql): Attempting to connect to user@:/dbname rlm_sql (sql): starting 0 rlm_sql (sql): Attempting to connect rlm_sql_postgresql #0 rlm_sql (sql): Connected new DB handle, #0 ...
Listening on IP address *, ports 1812/udp and 1813/udp, with proxy on 1814/udp. Ready to process requests.
And when I try to send the radius packets for accounting from my Cisco router, it just doesn't work
Do you receive some output in debug mode, from your router?
I have the following lines changed in my radiusd.conf:
with_cisco_vsa_hack = yes
$INCLUDE ${confdir}/postgresql.conf #unix (wtmp file) * commented out
#radutmp * commented out
Added sql instead of the unix accounting method.
If anyone has experience with this, please let me know what I can do.
We succeeded to make it work with Debian Sarge + PostgreSQL 7.4.2 + Cisco ;-)
- List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
# Id: postgresql.conf,v 1.8.2.11 2003/07/15 11:15:43 pnixon Exp $
#
# Configuration for the SQL module, when doing H323 VoIP billing.
#
# The database schema is available at:
#
# src/radiusd/src/billing/h323_db_postgresql.sql
#
sql {
# Database type currently must be rlm_sql_postgresql to work with this setup.
driver = "rlm_sql_postgresql"
# Connect info
server = "localhost"
login = "postgres"
password = ""
# Database configuration
radius_db = "radius"
# Database table configuration
acct_table1 = "Start"
acct_table2 = "Stop"
authcheck_table = "radcheck"
authreply_table = "radreply"
groupcheck_table = "radgroupcheck"
groupreply_table = "radgroupreply"
usergroup_table = "usergroup"
# Remove stale session if checkrad does not see a double login
deletestalesessions = yes
# Print all SQL statements when in debug mode (-x)
sqltrace = no
sqltracefile = ${logdir}/sqltrace.sql
# number of sql connections to make to server
num_sql_socks = 25
# Radius server name so you can tell which radius server handled a request
# when you have multiple radius servers and one database.
radius_server_name = myservername
#######################################################################
# Query config: Username
#######################################################################
# This is the username that will get substituted, escaped, and added
# as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below
# everywhere a username substitution is needed so you you can be sure
# the username passed from the client is escaped properly.
#
# Uncomment the next line, if you want the sql_user_name to mean:
#
# Use Stripped-User-Name, if it's there.
# Else use User-Name, if it's there,
# Else use hard-coded string "none" as the user name.
#
#sql_user_name = "%{Stripped-User-Name:-%{User-Name:-none}}"
#
sql_user_name = "%{User-Name}"
#######################################################################
# Accounting Queries
#######################################################################
# accounting_onoff_query - query for Accounting On/Off packets
# accounting_update_query - query for Accounting update packets
# accounting_start_query - query for Accounting start packets
# accounting_start_query_alt - query for Accounting start packets
# (alternate in case first query fails)
# accounting_stop_query - query for Accounting stop packets
# accounting_stop_query_alt - query for Accounting start packets
# (alternate in case first query doesn't
# affect any existing rows in the table)
#######################################################################
# Note: The VoIP accouting does not need Alternate queries in the shipped
# configuration as all queries are INSERTS, hence should always work.
# If they do not work then you probably have duplicate records in your table.
accounting_start_query = "INSERT into ${acct_table1}%{h323-call-type} \
(RadiusServerName, UserName, NASIPAddress, AcctTime, CalledStationId, \
CallingStationId, AcctDelayTime, h323gwid, h323callorigin,
h323setuptime, h323confid) \
values('${radius_server_name}', '%{SQL-User-Name}', \
'%{NAS-IP-Address}', now(), '%{Called-Station-Id}', \
'%{Calling-Station-Id}', '%{Acct-Delay-Time:-0}', '%{h323-gw-id}', \
'%{h323-call-origin}', strip_dot('%{h323-setup-time}'),
'%{h323-conf-id}')"
accounting_stop_query = "INSERT into ${acct_table2}%{h323-call-type} \
(RadiusServerName, UserName, NASIPAddress, AcctTime, \
AcctSessionTime, AcctInputOctets, AcctOutputOctets, CalledStationId,
CallingStationId, \
AcctDelayTime, H323RemoteAddress, CiscoNASPort, h323callorigin,
h323confid, \
h323connecttime, h323disconnectcause, h323disconnecttime, h323gwid,
h323setuptime) \
values('${radius_server_name}', '%{SQL-User-Name}',
'%{NAS-IP-Address}', now(), '%{Acct-Session-Time:-0}', \
'%{Acct-Input-Octets:-0}', '%{Acct-Output-Octets:-0}',
'%{Called-Station-Id}', '%{Calling-Station-Id}', \
'%{Acct-Delay-Time:-0}', NULLIF('%{h323-remote-address}', '')::inet,
'%{Cisco-NAS-Port}', \
'%{h323-call-origin}', '%{h323-conf-id}',
strip_dot('%{h323-connect-time}'), '%{h323-disconnect-cause}', \
strip_dot('%{h323-disconnect-time}'), '%{h323-gw-id}',
strip_dot('%{h323-setup-time}'))"
}
