HI,
When i tried with the instructions from "FreeRadius and MySQL by Scott Bartlett" and
applied for postgresql i encountered query failure problem. What may be the reason. I
could'nt trace it out. I am sending the output from the radius server. here i am
enclosing all the details about tables. Kindly go through this and reply me.
Thanks.
Bharani.
Tables what i have created in "radius" database is :
Table UserGroup:
id|username |groupname
--+----------+----------
1|bharani |static
2|kavitha |static
Table radreply:
id|username |attribute |value
--+----------+--------------------+--------------------
1|bharani |Framed-IP-Address |192.192.1.211
1|bharani |Reply-Message |Hai Bharani
2|kavitha |Reply-Message |Hai kavitha
2|kavitha |Framed-IP-Address |192.192.1.211
Table radcheck:
id|username |attribute |value
--+----------+----------+----------
1|bharani |password |raji
2|kavitha |password |kavi
Table radgroupreply:
id|groupname |attribute |value
--+----------+--------------------+---------------
1|bharani |Auth-Type |Sql
2|kavitha |Auth-Type |Sql
1|bharani |Service-Type |Framed-User
2|kavitha |Service-Type |Framed-User
2|kavitha |Framed-Protocol |PPP
1|bharani |Framed-Protocol |PPP
The output::
Starting - reading configuration files ...
reread_config: reading radiusd.conf
Config: including file: /usr/local/etc/raddb/proxy.conf
Config: including file: /usr/local/etc/raddb/clients.conf
Config: including file: /usr/local/etc/raddb/sql.conf
main: prefix = "/usr/local"
main: localstatedir = "/usr/local/var"
main: logdir = "/usr/local/var/log/radius"
main: libdir = "/usr/local/lib"
main: radacctdir = "/usr/local/var/log/radius/radacct"
main: hostname_lookups = no
read_config_files: reading dictionary
read_config_files: reading clients
read_config_files: reading realms
read_config_files: reading naslist
main: max_request_time = 30
main: cleanup_delay = 5
main: max_requests = 1024
main: delete_blocked_requests = 0
main: port = 0
main: allow_core_dumps = no
main: log_stripped_names = no
main: log_auth = no
main: log_auth_badpass = no
main: log_auth_goodpass = no
main: pidfile = "/usr/local/var/run/radiusd.pid"
main: user = "root"
main: group = "root"
main: usercollide = no
main: lower_user = "no"
main: lower_pass = "no"
main: nospace_user = "no"
main: nospace_pass = "no"
main: proxy_requests = no
proxy: retry_delay = 5
proxy: retry_count = 3
proxy: synchronous = no
proxy: dead_time = 120
main: debug_level = 0
read_config_files: entering modules setup
Module: Library search path is /usr/local/lib
Module: Loaded SQL
sql: driver = "rlm_sql_postgresql"
sql: server = "localhost"
sql: port = ""
sql: login = "root"
sql: password = "mascon"
sql: radius_db = "radius"
sql: acct_table = "radacct"
sql: acct_table2 = "radacct"
sql: authcheck_table = "radcheck"
sql: authreply_table = "radreply"
sql: groupcheck_table = "radgroupcheck"
sql: groupreply_table = "radgroupreply"
sql: usergroup_table = "usergroup"
sql: nas_table = "nas"
sql: dict_table = "dictionary"
sql: sqltrace = no
sql: sqltracefile = "/usr/local/var/log/radius/sqltrace.sql"
sql: deletestalesessions = yes
sql: num_sql_socks = 5
sql: sql_user_name = "%{User-Name}"
sql: authorize_check_query = "SELECT id,UserName,Attribute,Value FROM radcheck
WHERE Username = '%{SQL-User-Name}' ORDER BY id"
sql: authorize_reply_query = "SELECT id,UserName,Attribute,Value FROM radreply
WHERE Username = '%{SQL-User-Name}' ORDER BY id"
sql: authorize_group_check_query = "SELECT radgroupcheck.id,radgroupcheck.Group
Name,radgroupcheck.Attribute,radgroupcheck.Value FROM radgroupcheck,usergroup WH
ERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupch
eck.GroupName ORDER BY radgroupcheck.id"
sql: authorize_group_reply_query = "SELECT radgroupreply.id,radgroupreply.Group
Name,radgroupreply.Attribute,radgroupreply.Value FROM radgroupreply,usergroup WH
ERE usergroup.Username = '%{SQL-User-Name}' AND usergroup.GroupName = radgroupre
ply.GroupName ORDER BY radgroupreply.id"
sql: authenticate_query = "SELECT Value,Attribute FROM radcheck WHERE UserName
= '%{User-Name}' AND ( Attribute = 'Password' OR Attribute = 'Crypt-Password' )
ORDER BY Attribute DESC"
sql: accounting_onoff_query = "UPDATE radacct SET AcctStopTime='%S', AcctSessio
nTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime), AcctTerminateCause='
%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time} WHERE AcctSessionTi
me=0 AND AcctStopTime=0 AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime
<= '%S'"
sql: accounting_update_query = "UPDATE radacct SET FramedIPAddress = '%{Framed-
IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-Us
er-Name}' AND NASIPAddress= '%{NAS-IP-Address}'"
sql: accounting_start_query = "INSERT into radacct (RadAcctId, AcctSessionId, A
cctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTim
e, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_
stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, Acct
TerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, Ac
ctStopDelay) values('', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQ
L-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port-Id}', '%{NAS-Port-Ty
pe}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0', '%{C
alled-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Pr
otocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"
sql: accounting_start_query_alt = "UPDATE radacct SET AcctStartTime = '%S', Acc
tStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE
AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIP
Address = '%{NAS-IP-Address}'"
sql: accounting_stop_query = "UPDATE radacct SET AcctStopTime = '%S', AcctSessi
onTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctO
utputOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Ca
use}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}
' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' A
ND NASIPAddress = '%{NAS-IP-Address}'"
sql: accounting_stop_query_alt = "INSERT into radacct (RadAcctId, AcctSessionId
, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStart
Time, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectIn
fo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, A
cctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay,
AcctStopDelay) values('', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%
{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port-Id}', '%{NAS-Port
-Type}', '0', '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '', '%{Connect-
Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Frame
d-Protocol}', '%{Framed-IP-Address}', '0', '%{Acct-Delay-Time}')"
sql: connect_failure_retry_delay = 60
rlm_sql: Driver rlm_sql_postgresql loaded and linked
rlm_sql: Attempting to connect to root@localhost:/radius
rlm_sql: Connected new DB handle, #0
rlm_sql: Connected new DB handle, #1
rlm_sql: Connected new DB handle, #2
rlm_sql: Connected new DB handle, #3
rlm_sql: Connected new DB handle, #4
Module: Instantiated sql (sql)
Module: Loaded preprocess
preprocess: huntgroups = "/usr/local/etc/raddb/huntgroups"
preprocess: hints = "/usr/local/etc/raddb/hints"
preprocess: with_ascend_hack = no
preprocess: ascend_channels_per_line = 23
preprocess: with_ntdomain_hack = no
preprocess: with_specialix_jetstream_hack = no
preprocess: with_cisco_vsa_hack = no
Module: Instantiated preprocess (preprocess)
Module: Loaded realm
realm: format = "suffix"
realm: delimiter = "@"
Module: Instantiated realm (suffix)
Module: Loaded files
files: usersfile = "/usr/local/etc/raddb/users"
files: acctusersfile = "/usr/local/etc/raddb/acct_users"
files: compat = "no"
Module: Instantiated files (files)
Module: Loaded detail
detail: detailfile = "/usr/local/var/log/radius/radacct/%{Client-IP-Address}/de
tail"
detail: detailperm = 384
detail: dirperm = 493
Module: Instantiated detail (detail)
Module: Loaded radutmp
radutmp: filename = "/usr/local/var/log/radius/radutmp"
radutmp: perm = 384
radutmp: callerid = yes
Module: Instantiated radutmp (radutmp)
main: smux_password = ""
main: snmp_write_access = no
SMUX connect try 1
Can't connect to SNMP agent with SMUX: Connection refused
Listening on IP address *, ports 1812/udp and 1813/udp.
Ready to process requests.
rad_recv: Access-Request packet from host 192.192.1.211:1025, id=179, length=54
User-Name = "steve"
Password = "\247\220P-\213\362\343\204\264\215\010\216\327JN\211"
NAS-IP-Address = 255.255.255.255
NAS-Port-Id = "0"
modcall: entering group authorize
modcall[authorize]: module "preprocess" returns ok
modcall[authorize]: module "suffix" returns ok
rlm_sql: Reserving sql socket id: 4
radius_xlat: 'steve'
sql_escape in: 'steve'
sql_escape out: 'steve'
sql_set_user: escaped user --> 'steve'
radius_xlat: 'SELECT id,UserName,Attribute,Value FROM radcheck WHERE Username =
'steve' ORDER BY id'
Backend message type 0x45 arrived while idle
PostgreSQL Query failed Error: PQsendQuery() -- There is no connection to the ba
ckend.
rlm_sql_getvpdata: database query error
rlm_sql: SQL query error; rejecting user
rlm_sql: Released sql socket id: 4
modcall[authorize]: module "sql" returns invalid
modcall: group authorize returns invalid
Sending Access-Reject of id 179 to 192.192.1.211:1025
Finished request 0
Going to the next request
SMUX connect try 2
Can't connect to SNMP agent with SMUX: Connection refused
--- Walking the entire request list ---
Waking up in 6 seconds...
SMUX connect try 3
Can't connect to SNMP agent with SMUX: Connection refused
--- Walking the entire request list ---
Cleaning up request 0 ID 179 with timestamp 3c1878ad
Nothing to do. Sleeping until we see a request.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
