# # 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 = "postgres" password = "" # Database table configuration radius_db = "DBA" # 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 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}.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}.Attribute, \ ${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, CallingStationId, 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 zone - '%{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 AcctStartTime <= 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 zone - '%{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}'::interval), \ '%{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, AcctAuthentic, \ ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay) \ 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}', '%{Connect-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-Name}' \ AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL" accounting_stop_query = "UPDATE ${acct_table2} \ SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), AcctSessionTime = '%{Acct-Session-Time}', \ 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, AcctStopTime, \ AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \ 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}'::interval), \ (now() - '%{Acct-Delay-Time:-0}'::interval), '%{Acct-Session-Time}', '%{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())" }