# # Id: postgresql.conf,v 1.8.2.11 2003/07/15 11:15:43 pnixon Exp $ # # Configuration for the SQL module, when using Postgresql. # # The database schema is available at: # # src/radiusd/src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql # sql { # Database type # Current supported are: rlm_sql_mysql, rlm_sql_postgresql, rlm_sql_iodbc, rlm_sql_oracle driver = "rlm_sql_postgresql" # Connect info server = "localhost" # The following credentials will most likely work on a default install of Postgresql # If they do work however, it means that you have a HUGE GAPING SECURITY RISK on your # server! Please change the "postgres" users password and setup a separate radius user. login = "raduser" password = "radpass" # Database table configuration radius_db = "radius" # If you want both stop and start records logged to the # same SQL table, leave this as is. If you want them in # different tables, put the start table in acct_table1 # and stop table in acct_table2 acct_table1 = "radacct" acct_table2 = "radacct" # Allow for storing data after authentication postauth_table = "radpostauth" 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 = yes sqltracefile = ${logdir}/sqltrace.sql # number of sql connections to make to server num_sql_socks = 5 # number of seconds to dely retrying on a failed database # connection (per_socket) #connect_failure_retry_delay = 60 ####################################################################### # 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}" ####################################################################### # Authorization Queries ####################################################################### # These queries compare the check items for the user # in ${authcheck_table} and setup the reply items in # ${authreply_table}. You can use any query/tables # you want, but the return data for each row MUST # be in the following order: # # 0. Row ID (currently unused) # 1. UserName/GroupName # 2. Item Attr Name # 3. Item Attr Value # 4. Item Attr Operation ####################################################################### # Use these for case sensitive usernames. WARNING: Slower queries! #authorize_check_query = "SELECT id,UserName,Attribute,Value,Op FROM ${authcheck_table} WHERE STRCMP(Username, '%{SQL -User-Name}') = 0 ORDER BY id" #authorize_reply_query = "SELECT id,UserName,Attribute,Value,Op FROM ${authreply_table} WHERE STRCMP(Username, '%{SQL -User-Name}') = 0 ORDER BY id" simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL" #simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingSt ationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL" #authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \ # FROM ${authcheck_table} \ # WHERE Username = '%{SQL-User-Name}' and enabled = 'T' \ #and (SELECT AcctStopTime FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' order by AcctStartTime desc limit 1) is not null \ # ORDER BY id" authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \ FROM ${authcheck_table} \ WHERE (Username = '%{SQL-User-Name}' and enabled = 'T' \ and (select count(*) from ${acct_table1} where UserName = '%{SQL-User-Name}' and AcctStopTime is null) <> 1) or (serviceid = '19' and Username = '%{SQL-User-Name}' ) \ ORDER BY id" authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \ FROM ${authreply_table} \ WHERE Username = '%{SQL-User-Name}' \ ORDER BY id" # Use these for case sensitive usernames. WARNING: Slower queries! #authorize_group_check_query = "SELECT ${groupcheck_table}.id,${groupcheck_table}.GroupName,${groupcheck_table}.Attri bute,${groupcheck_table}.Value,${groupcheck_table}.Op FROM ${groupcheck_table},${usergroup_table} WHERE STRCMP(${usergroup_ta ble}.Username, '%{SQL-User-Name}') = 0 AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName ORDER BY ${groupcheck _table}.id" #authorize_group_reply_query = "SELECT ${groupreply_table}.id,${groupreply_table}.GroupName,${groupreply_table}.Attri bute,${groupreply_table}.Value,${groupreply_table}.Op FROM ${groupreply_table},${usergroup_table} WHERE STRCMP(${usergroup_ta ble}.Username, '%{SQL-User-Name}') = 0 AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY ${groupreply _table}.id" authorize_group_check_query = "SELECT ${groupcheck_table}.id, ${groupcheck_table}.GroupName, \ ${groupcheck_table}.Attribute, ${groupcheck_table}.Value,${groupcheck_table}.Op \ FROM ${groupcheck_table}, ${usergroup_table} \ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupcheck_table} .GroupName \ ORDER BY ${groupcheck_table}.id" authorize_group_reply_query = "SELECT ${groupreply_table}.id, ${groupreply_table}.GroupName, ${groupreply_table}.Attr ibute, \ ${groupreply_table}.Value, ${groupreply_table}.Op \ FROM ${groupreply_table},${usergroup_table} \ WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupreply_table} .GroupName \ ORDER BY ${groupreply_table}.id" ####################################################################### # Authentication Query ####################################################################### # This query is used only to get the Password for the # user we want to authenticate. The password MUST # be the first field in the return row data. ####################################################################### authenticate_query = "SELECT Value,Attribute FROM ${authcheck_table} \ WHERE UserName = '%{User-Name}' AND ( Attribute = 'User-Password' OR Attribute = 'Crypt-Password' ) \ ORDER BY Attribute DESC" ####################################################################### # Simultaneous Use Checking Queries ####################################################################### # simul_count_query - query for the number of current connections # - If this is not defined, no simultaneouls use checking # - will be performed by this module instance # simul_verify_query - query to return details of current connections for verification # - Leave blank or commented out to disable verification step # - Note that the returned field order should not be changed. ####################################################################### # Uncomment simul_count_query to enable simultaneous use checking # simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL " # simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingS tationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL" ####################################################################### # Accounting Queries ####################################################################### # accounting_onoff_query - query for Accounting On/Off packets # accounting_update_query - query for Accounting update packets # accounting_update_query_alt - query for Accounting update packets # (alternate in case first query fails) # 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) ####################################################################### accounting_onoff_query = "UPDATE ${acct_table1} \ SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \ AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zo ne - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \ AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}' \ WHERE AcctSessionTime IS NULL AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTim e <= now()" accounting_update_query = "UPDATE ${acct_table1} \ SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \ AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zo ne - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \ AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \ AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint) \ WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_update_query_alt = "INSERT into ${acct_table1} \ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, \ AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \ ServiceType, FramedProtocol, FramedIPAddress) \ values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}' , \ '%{NAS-Port}', '%{NAS-Port-Type}', (now() - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::i nterval), \ '%{Acct-Session-Time}', '%{Acct-Authentic}', \ (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \ (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \ '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \ NULLIF('%{Framed-IP-Address}', '')::inet)" accounting_start_query = "INSERT into ${acct_table1} \ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthe ntic, \ ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStart Delay) \ values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}' , \ '%{NAS-Port}', '%{NAS-Port-Type}', (now() - '%{Acct-Delay-Time:-0}'::interval), '%{Acct-Authentic}', '%{Conne ct-Info}', \ '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \ NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}')" accounting_start_query_alt = "UPDATE ${acct_table1} \ SET AcctStartTime = (now() - '%{Acct-Delay-Time:-0}'::interval), AcctStartDelay = '%{Acct-Delay-Time:-0}', \ ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-N ame}' \ AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_stop_query = "UPDATE ${acct_table2} \ SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \ AcctSessionTime = NULLIF('%{Acct-Session-Time}', '')::bigint, \ AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \ AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), \ AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}', \ FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, ConnectInfo_stop = '%{Connect-Info}' \ WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \ AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_stop_query_alt = "INSERT into ${acct_table2} \ (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopT ime, \ AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, Calling StationId, \ AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) \ values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}' , \ '%{NAS-Port}', '%{NAS-Port-Type}', (now() - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::i nterval), \ (now() - '%{Acct-Delay-Time:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, \ '%{Acct-Authentic}', '%{Connect-Info}', \ (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \ (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \ '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', \ NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}')" ####################################################################### # Group Membership Queries ####################################################################### # group_membership_query - Check user group membership ####################################################################### group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}'" ####################################################################### # Authentication Logging Queries ####################################################################### # postauth_query - Insert some info after authentication ####################################################################### postauth_query = "INSERT into ${postauth_table} (username, pass, reply, authdate) \ values ('%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())" }