First of all, you should use freeradius-snapshot to work with op field.
1) db_postgresql.sql is not still modified, so just do
ALTER TABLE radcheck ADD COLUMN op varchar(2);
ALTER TABLE radreply ADD COLUMN op varchar(2);
ALTER TABLE radgroupcheck ADD COLUMN op varchar(2);
ALTER TABLE radgroupreply ADD COLUMN op varchar(2);
on your postgres database
2) Then take my sql.conf as example (modify it and copy to raddb)
3) insert something to radcheck and radreply (or to radgroupcheck/reply)
ex.:
insert into radcheck (username,attribute,value,op) values
('steve','Password','test','==');
insert into radreply (username,attribute,value,op) values
('steve','Reply-Message','Test passed','=');
4) run radiusd -X
5) test it with radtest
(radtest steve test localhost 0 your_secret_key )
6) Did i miss something? :)
On Thu, 14 Feb 2002, please please wrote:
> I have installed freeradius 0.4 with postgreSQL.
> But if I uncomment "sql" in authorize, authenticate
> and accounting (radiusd.conf), I can not see any
> RADIUS packet when I execute "radiusd -X" (so I can
> not know why a request is rejected).
> What can I do?
>
> By the way, I would like to know why the "op" field
> does not exist in radcheck, radreply, radgroupcheck
> and radgroupreply (Look at db_postgresql.sql).
>
> Regards.
>
> __________________________________________________
> Do You Yahoo!?
> Send FREE Valentine eCards with Yahoo! Greetings!
> http://greetings.yahoo.com
>
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
--
cron-ripe
#
# Configuration for the SQL module.
#
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"
login = "radius"
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"
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 = 15
########################################################################
# 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.
#
#sql_user_name = "%{Stripped-User-Name}:-%{User-Name}}"
# ^^^ --That doesn't work because someone screwed up decode_attribute()
#sql_user_name = "%{Stripped-User-Name}";
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"
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. WANRING: 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 STRCMP(${usergroup_table}.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}.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"
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_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 = 'Password' OR Attribute = 'Crypt-Password'
) ORDER BY Attribute DESC"
########################################################################
# Accounting Queries
########################################################################
# accounting_onoff_query - query for Accounting On/Off packets
# accounting_update_query - query for Accounting update packets
# 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)
########################################################################
# I've changed this section... removed inserting of radacctid (it's autoinctremented
by database)
# And unix_timestamp is mysql fuction... extract (epoch.. seem to work
# I also removed insertition of AcctStopTime from "Start" query (it fails with
postgres cause it doesn't like 0 in timestamp field)
# and added NAS-IP-Address to Stop query (strange, but radius can not determine
#username when updating field with ip address and query is empty) - that was before i
found out that solution was to use %{User-Name} in update query
# also i changed NAS-Port-id to NAS-Port (cisco nas gives me NAS-Port)
# When session starts, acctsessiontime is set to -1 (To let me know that user is
online, i can list online users - select * from radacct where acctsessiontime=-1;)
accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S',
AcctSessionTime=extract(epoch from (timestamp('%S') - timestamp(AcctStartTime))),
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = %{Acct-Delay-Time} WHERE
AcctSessionTime=-1 AND AcctStopTime=AcctStartTime AND NASIPAddress=
'%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress =
'%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName =
'%{User-Name}' AND NASIPAddress= '%{NAS-IP-Address}' and NASPortId='%{NAS-Port}' "
accounting_start_query = "INSERT into radacct (AcctSessionId, AcctUniqueId,
UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctSessionTime,
AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, AcctOutputOctets,
CalledStationId, CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol,
FramedIPAddress, AcctStartDelay, AcctStopDelay) values('%{Acct-Session-Id}',
'%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{NAS-Port}', '%{NAS-Port-Type}', '%S', '-1', '%{Acct-Authentic}', '%{Connect-Info}',
'', '0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}',
'%{Framed-Protocol}', '%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"
accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime = '%S',
AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start = '%{Connect-Info}' WHERE
AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND
NASIPAddress = '%{NAS-IP-Address}'"
accounting_stop_query = "UPDATE ${acct_table1} SET AcctStopTime = '%S',
AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}',
AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause =
'%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', FramedIPAddress =
'%{Framed-IP-Address}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId =
'%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress =
'%{NAS-IP-Address}' and NASPortId='%{NAS-Port}' and acctstarttime > (now()-interval('2
days')) "
accounting_stop_query_alt = "INSERT into radacct ( AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType, AcctStartTime,
AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop,
AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId,
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay,
AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',
'%{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}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0',
'%{Acct-Delay-Time}')"
}
