4 # Configuration for the SQL module, when using Postgresql.
6 # The database schema is available at:
8 # doc/examples/postgresql.sql
14 # Current supported are: rlm_sql_mysql, rlm_sql_postgresql,
15 # rlm_sql_unixodbc, rlm_sql_oracle.
16 driver = "rlm_sql_postgresql"
21 # The following credentials will most likely work on a default
22 # install of Postgresql. If they do work however, it means that
23 # you have a HUGE GAPING SECURITY RISK on your server! Please
24 # change the "postgres" users password and setup a separate
29 # Database table configuration
32 # If you want both stop and start records logged to the
33 # same SQL table, leave this as is. If you want them in
34 # different tables, put the start table in acct_table1
35 # and stop table in acct_table2
36 acct_table1 = "radacct"
37 acct_table2 = "radacct"
39 # Allow for storing data after authentication
40 postauth_table = "radpostauth"
42 authcheck_table = "radcheck"
43 authreply_table = "radreply"
45 groupcheck_table = "radgroupcheck"
46 groupreply_table = "radgroupreply"
48 # Table to keep group info
49 usergroup_table = "usergroup"
51 # Remove stale session if checkrad does not see a double login
52 deletestalesessions = yes
54 # Print all SQL statements when in debug mode (-x)
56 sqltracefile = ${logdir}/sqltrace.sql
58 # number of sql connections to make to server
61 # number of seconds to dely retrying on a failed database
62 # connection (per_socket)
63 #connect_failure_retry_delay = 60
65 #######################################################################
66 # Query config: Username
67 #######################################################################
68 # This is the username that will get substituted, escaped, and added
69 # as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used
70 # below everywhere a username substitution is needed so you you can
71 # be sure the username passed from the client is escaped properly.
73 # Uncomment the next line, if you want the sql_user_name to mean:
75 # Use Stripped-User-Name, if it's there.
76 # Else use User-Name, if it's there,
77 # Else use hard-coded string "none" as the user name.
79 #sql_user_name = "%{Stripped-User-Name:-%{User-Name:-none}}"
81 sql_user_name = "%{User-Name}"
84 #######################################################################
85 # Authorization Queries
86 #######################################################################
87 # These queries compare the check items for the user
88 # in ${authcheck_table} and setup the reply items in
89 # ${authreply_table}. You can use any query/tables
90 # you want, but the return data for each row MUST
91 # be in the following order:
93 # 0. Row ID (currently unused)
94 # 1. UserName/GroupName
97 # 4. Item Attr Operation
98 #######################################################################
100 # Use these for case insensitive usernames. WARNING: Slower queries!
101 # authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
102 # FROM ${authcheck_table} \
103 # WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
105 # authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \
106 # FROM ${authreply_table} \
107 # WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}') \
110 authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
111 FROM ${authcheck_table} \
112 WHERE Username = '%{SQL-User-Name}' \
115 authorize_reply_query = "SELECT id, UserName, Attribute, Value, Op \
116 FROM ${authreply_table} \
117 WHERE Username = '%{SQL-User-Name}' \
120 # Use these for case insensitive usernames. WARNING: Slower queries!
121 # authorize_group_check_query = "SELECT ${groupcheck_table}.id, ${groupcheck_table}.GroupName, \
122 # ${groupcheck_table}.Attribute, ${groupcheck_table}.Value, ${groupcheck_table}.Op \
123 # FROM ${groupcheck_table}, ${usergroup_table} \
124 # WHERE LOWER(${usergroup_table}.UserName) = LOWER('%{SQL-User-Name}') AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
125 # ORDER BY ${groupcheck_table}.id"
126 # authorize_group_reply_query = "SELECT ${groupreply_table}.id, ${groupreply_table}.GroupName, \
127 # ${groupreply_table}.Attribute, ${groupreply_table}.Value, ${groupreply_table}.Op \
128 # FROM ${groupreply_table}, ${usergroup_table} \
129 # WHERE LOWER(${usergroup_table}.UserName) = LOWER('%{SQL-User-Name}') AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
130 # ORDER BY ${groupreply_table}.id"
132 authorize_group_check_query = "SELECT ${groupcheck_table}.id, ${groupcheck_table}.GroupName, \
133 ${groupcheck_table}.Attribute, ${groupcheck_table}.Value,${groupcheck_table}.Op \
134 FROM ${groupcheck_table}, ${usergroup_table} \
135 WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName \
136 ORDER BY ${groupcheck_table}.id"
138 authorize_group_reply_query = "SELECT ${groupreply_table}.id, ${groupreply_table}.GroupName, ${groupreply_table}.Attribute, \
139 ${groupreply_table}.Value, ${groupreply_table}.Op \
140 FROM ${groupreply_table},${usergroup_table} \
141 WHERE ${usergroup_table}.Username = '%{SQL-User-Name}' AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName \
142 ORDER BY ${groupreply_table}.id"
144 #######################################################################
145 # Authentication Query
146 #######################################################################
147 # This query is used only to get the Password for the
148 # user we want to authenticate. The password MUST
149 # be the first field in the return row data.
150 #######################################################################
152 authenticate_query = "SELECT Value,Attribute FROM ${authcheck_table} \
153 WHERE UserName = '%{User-Name}' AND ( Attribute = 'User-Password' OR Attribute = 'Crypt-Password' ) \
154 ORDER BY Attribute DESC"
156 #######################################################################
157 # Simultaneous Use Checking Queries
158 #######################################################################
159 # simul_count_query - query for the number of current connections
160 # - If this is not defined, no simultaneouls use checking
161 # - will be performed by this module instance
162 # simul_verify_query - query to return details of current connections for verification
163 # - Leave blank or commented out to disable verification step
164 # - Note that the returned field order should not be changed.
165 #######################################################################
167 # Uncomment simul_count_query to enable simultaneous use checking
168 # simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
169 # simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime IS NULL"
173 #######################################################################
175 #######################################################################
176 # accounting_onoff_query - query for Accounting On/Off packets
177 # accounting_update_query - query for Accounting update packets
178 # accounting_update_query_alt - query for Accounting update packets
179 # (alternate in case first query fails)
180 # accounting_start_query - query for Accounting start packets
181 # accounting_start_query_alt - query for Accounting start packets
182 # (alternate in case first query fails)
183 # accounting_stop_query - query for Accounting stop packets
184 # accounting_stop_query_alt - query for Accounting start packets
185 # (alternate in case first query doesn't
186 # affect any existing rows in the table)
187 #######################################################################
189 accounting_onoff_query = "UPDATE ${acct_table1} \
190 SET AcctStopTime = ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), \
191 AcctSessionTime = (EXTRACT(EPOCH FROM('%S'::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
192 AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}' \
193 WHERE AcctSessionTime IS NULL AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'::timestamp"
195 accounting_update_query = "UPDATE ${acct_table1} \
196 SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
197 AcctSessionTime = (EXTRACT(EPOCH FROM('%S'::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
198 AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
199 AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint) \
200 WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
201 AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
203 accounting_update_query_alt = "INSERT into ${acct_table1} \
204 (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, \
205 AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
206 ServiceType, FramedProtocol, FramedIPAddress, XAscendSessionSvrKey) \
207 values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
208 '%{NAS-Port}', '%{NAS-Port-Type}', ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
209 '%{Acct-Session-Time}', '%{Acct-Authentic}', \
210 (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
211 (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
212 '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
213 NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr-Key}')"
215 accounting_start_query = "INSERT into ${acct_table1} \
216 (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, \
217 ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) \
218 values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
219 '%{NAS-Port}', '%{NAS-Port-Type}', ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', \
220 '%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
221 NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}', '%{X-Ascend-Session-Svr-Key}')"
223 accounting_start_query_alt = "UPDATE ${acct_table1} \
224 SET AcctStartTime = ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), AcctStartDelay = '%{Acct-Delay-Time:-0}', \
225 ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
226 AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
228 accounting_stop_query = "UPDATE ${acct_table2} \
229 SET AcctStopTime = ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), \
230 AcctSessionTime = NULLIF('%{Acct-Session-Time}', '')::bigint, \
231 AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
232 AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), \
233 AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}', \
234 FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, ConnectInfo_stop = '%{Connect-Info}' \
235 WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
236 AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
238 accounting_stop_query_alt = "INSERT into ${acct_table2} \
239 (AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, \
240 AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
241 AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay, XAscendSessionSvrKey) \
242 values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
243 '%{NAS-Port}', '%{NAS-Port-Type}', ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
244 ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, \
245 '%{Acct-Authentic}', '%{Connect-Info}', \
246 (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
247 (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
248 '%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', \
249 NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}', '%{X-Ascend-Session-Svr-Key}')"
251 #######################################################################
252 # Group Membership Queries
253 #######################################################################
254 # group_membership_query - Check user group membership
255 #######################################################################
257 # Use these for case insensitive usernames. WARNING: Slower queries!
258 # group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE LOWER(UserName) = LOWER('%{SQL-User-Name}')"
260 group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}'"
262 #######################################################################
263 # Authentication Logging Queries
264 #######################################################################
265 # postauth_query - Insert some info after authentication
266 #######################################################################
267 postauth_query = "INSERT INTO ${postauth_table} (username, pass, reply, authdate) VALUES ('%{User-Name}', '%{User-Password:-Chap-Password}', '%{reply:Packet-Type}', NOW())"