2
# Id: postgresql.conf,v 1.8.2.11 2003/07/15 11:15:43 pnixon Exp $
2
# $Id: postgresql.conf,v 1.16.2.2.2.3 2006/08/18 10:50:30 pnixon Exp $
4
4
# Configuration for the SQL module, when using Postgresql.
6
6
# The database schema is available at:
8
# src/radiusd/src/modules/rlm_sql/drivers/rlm_sql_postgresql/db_postgresql.sql
8
# doc/examples/postgresql.sql
14
# Current supported are: rlm_sql_mysql, rlm_sql_postgresql, rlm_sql_iodbc, rlm_sql_oracle
14
# Current supported are: rlm_sql_mysql, rlm_sql_postgresql,
15
# rlm_sql_unixodbc, rlm_sql_oracle.
15
16
driver = "rlm_sql_postgresql"
18
19
server = "localhost"
19
# The following credentials will most likely work on a default install of Postgresql
20
# If they do work however, it means that you have a HUGE GAPING SECURITY RISK on your
21
# server! Please change the "postgres" users password and setup a separate radius user.
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
25
29
# Database table configuration
26
30
radius_db = "radius"
28
32
# If you want both stop and start records logged to the
29
33
# same SQL table, leave this as is. If you want them in
30
34
# different tables, put the start table in acct_table1
31
35
# and stop table in acct_table2
32
36
acct_table1 = "radacct"
33
37
acct_table2 = "radacct"
35
39
# Allow for storing data after authentication
36
40
postauth_table = "radpostauth"
38
42
authcheck_table = "radcheck"
39
43
authreply_table = "radreply"
41
45
groupcheck_table = "radgroupcheck"
42
46
groupreply_table = "radgroupreply"
48
# Table to keep group info
44
49
usergroup_table = "usergroup"
46
51
# Remove stale session if checkrad does not see a double login
47
52
deletestalesessions = yes
60
65
#######################################################################
61
66
# Query config: Username
62
67
#######################################################################
63
# This is the username that will get substituted, escaped, and added
64
# as attribute 'SQL-User-Name'. '%{SQL-User-Name}' should be used below
65
# everywhere a username substitution is needed so you you can be sure
66
# the username passed from the client is escaped properly.
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.
68
# Uncomment the next line, if you want the sql_user_name to mean:
73
# Uncomment the next line, if you want the sql_user_name to mean:
70
75
# Use Stripped-User-Name, if it's there.
71
76
# Else use User-Name, if it's there,
91
96
# 3. Item Attr Value
92
97
# 4. Item Attr Operation
93
98
#######################################################################
94
# Use these for case sensitive usernames. WARNING: Slower queries!
95
#authorize_check_query = "SELECT id,UserName,Attribute,Value,Op FROM ${authcheck_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id"
96
#authorize_reply_query = "SELECT id,UserName,Attribute,Value,Op FROM ${authreply_table} WHERE STRCMP(Username, '%{SQL-User-Name}') = 0 ORDER BY id"
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}') \
98
110
authorize_check_query = "SELECT id, UserName, Attribute, Value, Op \
99
111
FROM ${authcheck_table} \
105
117
WHERE Username = '%{SQL-User-Name}' \
108
# Use these for case sensitive usernames. WARNING: Slower queries!
109
#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 STRCMP(${usergroup_table}.Username, '%{SQL-User-Name}') = 0 AND ${usergroup_table}.GroupName = ${groupcheck_table}.GroupName ORDER BY ${groupcheck_table}.id"
110
#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 STRCMP(${usergroup_table}.Username, '%{SQL-User-Name}') = 0 AND ${usergroup_table}.GroupName = ${groupreply_table}.GroupName ORDER BY ${groupreply_table}.id"
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"
112
132
authorize_group_check_query = "SELECT ${groupcheck_table}.id, ${groupcheck_table}.GroupName, \
113
133
${groupcheck_table}.Attribute, ${groupcheck_table}.Value,${groupcheck_table}.Op \
153
173
#######################################################################
154
174
# Accounting Queries
155
175
#######################################################################
156
# accounting_onoff_query - query for Accounting On/Off packets
157
# accounting_update_query - query for Accounting update packets
158
# accounting_update_query_alt - query for Accounting update packets
159
# (alternate in case first query fails)
160
# accounting_start_query - query for Accounting start packets
161
# accounting_start_query_alt - query for Accounting start packets
162
# (alternate in case first query fails)
163
# accounting_stop_query - query for Accounting stop packets
164
# accounting_stop_query_alt - query for Accounting start packets
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
165
185
# (alternate in case first query doesn't
166
186
# affect any existing rows in the table)
167
187
#######################################################################
169
189
accounting_onoff_query = "UPDATE ${acct_table1} \
170
SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \
171
AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
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, \
172
192
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}' \
173
WHERE AcctSessionTime IS NULL AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= now()"
193
WHERE AcctSessionTime IS NULL AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'::timestamp"
175
195
accounting_update_query = "UPDATE ${acct_table1} \
176
196
SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
177
AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
197
AcctSessionTime = (EXTRACT(EPOCH FROM('%S'::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \
178
198
AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
179
199
AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint) \
180
200
WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
183
203
accounting_update_query_alt = "INSERT into ${acct_table1} \
184
204
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, \
185
205
AcctSessionTime, AcctAuthentic, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
186
ServiceType, FramedProtocol, FramedIPAddress) \
206
ServiceType, FramedProtocol, FramedIPAddress, XAscendSessionSvrKey) \
187
207
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
188
'%{NAS-Port}', '%{NAS-Port-Type}', (now() - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
208
'%{NAS-Port}', '%{NAS-Port-Type}', ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
189
209
'%{Acct-Session-Time}', '%{Acct-Authentic}', \
190
210
(('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
191
211
(('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
192
212
'%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
193
NULLIF('%{Framed-IP-Address}', '')::inet)"
213
NULLIF('%{Framed-IP-Address}', '')::inet, '%{X-Ascend-Session-Svr-Key}')"
195
215
accounting_start_query = "INSERT into ${acct_table1} \
196
216
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctAuthentic, \
197
ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay) \
217
ConnectInfo_start, CalledStationId, CallingStationId, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, XAscendSessionSvrKey) \
198
218
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
199
'%{NAS-Port}', '%{NAS-Port-Type}', (now() - '%{Acct-Delay-Time:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', \
219
'%{NAS-Port}', '%{NAS-Port-Type}', ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), '%{Acct-Authentic}', '%{Connect-Info}', \
200
220
'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{Service-Type}', '%{Framed-Protocol}', \
201
NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}')"
221
NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}', '%{X-Ascend-Session-Svr-Key}')"
203
223
accounting_start_query_alt = "UPDATE ${acct_table1} \
204
SET AcctStartTime = (now() - '%{Acct-Delay-Time:-0}'::interval), AcctStartDelay = '%{Acct-Delay-Time:-0}', \
224
SET AcctStartTime = ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), AcctStartDelay = '%{Acct-Delay-Time:-0}', \
205
225
ConnectInfo_start = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
206
226
AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
208
228
accounting_stop_query = "UPDATE ${acct_table2} \
209
SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \
229
SET AcctStopTime = ('%S'::timestamp - '%{Acct-Delay-Time:-0}'::interval), \
210
230
AcctSessionTime = NULLIF('%{Acct-Session-Time}', '')::bigint, \
211
231
AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
212
232
AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), \
218
238
accounting_stop_query_alt = "INSERT into ${acct_table2} \
219
239
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, \
220
240
AcctSessionTime, AcctAuthentic, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, \
221
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay) \
241
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStopDelay, XAscendSessionSvrKey) \
222
242
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', \
223
'%{NAS-Port}', '%{NAS-Port-Type}', (now() - '%{Acct-Delay-Time:-0}'::interval - '%{Acct-Session-Time:-0}'::interval), \
224
(now() - '%{Acct-Delay-Time:-0}'::interval), NULLIF('%{Acct-Session-Time}', '')::bigint, \
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, \
225
245
'%{Acct-Authentic}', '%{Connect-Info}', \
226
246
(('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
227
247
(('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint), '%{Called-Station-Id}', \
228
248
'%{Calling-Station-Id}', '%{Acct-Terminate-Cause}', '%{Service-Type}', '%{Framed-Protocol}', \
229
NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}')"
249
NULLIF('%{Framed-IP-Address}', '')::inet, '%{Acct-Delay-Time:-0}', '%{X-Ascend-Session-Svr-Key}')"
231
251
#######################################################################
232
252
# Group Membership Queries
233
253
#######################################################################
234
254
# group_membership_query - Check user group membership
235
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}')"
236
260
group_membership_query = "SELECT GroupName FROM ${usergroup_table} WHERE UserName='%{SQL-User-Name}'"
238
262
#######################################################################