rlm_sql - authenticatione vs authorization - update internal attribute within sql module

duffy reg.marcos at yahoo.it
Mon Apr 8 12:51:58 CEST 2013


hi list,
i'm trying to set one custom attribute during rad_check to use it while 
selecting proper vsa in rad_reply.
these are my 4 steps: 1) add custom attribute to the dictionary; 2) get 
it set by rad_check, if necessary; 3) modify dialup.conf to pass my 
custom attribute to rad_reply [using control variable?]; 4) check my 
custom attribute to choose reply vendor specific attributes;
i would set this custom attribute by returning it from radcheck query 
but radreply query doesn't recognize it: where am i wrong? how can i 
update custom attribute within sql module? can anyone please help me?
thanks,
duffy



############ my dictionary

$INCLUDE        /usr/share/freeradius/dictionary

VENDOR          Me                  35612

BEGIN-VENDOR    Me

ATTRIBUTE       Service-Type     1               integer
VALUE           Service-Type     Hotline         0
VALUE           Service-Type     DynamicUser     1
VALUE           Service-Type     StaticUser      2

ATTRIBUTE       Hotlining-Enabled     2          integer
VALUE           Hotlining-Enabled     False      0
VALUE           Hotlining-Enabled     True       1

ATTRIBUTE       Hotlining-Class       3          integer
VALUE           Hotlining-Class       User-New-Provisioning      0
VALUE           Hotlining-Class       User-Already-Connected     1
VALUE           Hotlining-Class       User-Not-Enabled           2

END-VENDOR      Me


############ my radcheck stored procedure

SELECT * INTO userinfo FROM users WHERE inner_id = ParmInnerId;

  hotlining = userinfo.hotline;

  IF userinfo.isactive = False THEN
    hotlining = TRUE;
  END IF;

  IF hotlining = TRUE THEN
    RETURN QUERY SELECT 0, ParmInnerId, 'Hotlining-Enabled'::varchar, 
':='::char(2), 'True'::varchar;
  END IF;

  RETURN QUERY
   SELECT * FROM vsa_check
    WHERE inner_id = ParmInnerId
   ORDER BY id;
  RETURN;
END


############ my dialup.conf

...
authorize_reply_query="\
SELECT id, inner_id, attribute, val, op \
   FROM radreply(\
   '%{SQL-User-Name}@%{Realm}',\
   '%{NAS-Ip-Address}'::inet, \
   COALESCE(NULLIF('%{control:Hotlining-Enabled}', ''), 'FALSE')::boolean);"
...


############ my radiusd -X output

...
rlm_sql (sql): Reserving sql socket id: 2
[sql]   expand: SELECT id, inner_id, attribute, val, op   FROM radcheck( 
   COALESCE(NULLIF('%{Event-Timestamp}', ''), '%S')::timestamp with time 
zone,   '%{SQL-User-Name}',   '%{Realm}',   '%{Nas-Ip-Add
ress}'::inet,   '%{Calling-Station-Id}'::macaddr, 
parse_bssid(NULLIF('%{WiMAX-BS-Id}', '')), 
COALESCE(NULLIF('%{WiMAX-PDFID}', ''), '0')::smallint); -> SELECT id, 
inner_id, attribute, val, op   FROM
  radcheck(   COALESCE(NULLIF('Apr  4 2013 18:42:05 CEST', ''), 
'2013-04-04 18:42:42')::timestamp with time zone,   'test', 
'freeradius',   '192.168.98.101'::inet,   '0C4C399C58F7'::macaddr, 
parse_bs
sid(NULLIF('0x303030303230303630313030', '')),   COALESCE(NULLIF('', 
''), '0')::smallint);rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 3 , fields = 5
[sql] User found in radcheck table
[sql]   expand: SELECT id, inner_id, attribute, val, op   FROM radreply( 
  '%{SQL-User-Name}@%{Realm}',  '%{NAS-Ip-Address}'::inet, 
COALESCE(NULLIF('%{control:Linkem-Hotlining-Enabled}', ''), 'FALSE'):
:boolean); -> SELECT id, inner_id, attribute, val, op   FROM radreply( 
'test at freeradius',  '192.168.98.101'::inet,   COALESCE(NULLIF('', ''), 
'FALSE')::boolean);
...


############# from my psql console

csnrads=# SELECT id, inner_id, attribute, val, op   FROM radcheck( 
COALESCE(NULLIF('Apr  4 2013 18:42:05 CEST', ''), '2013-04-04 
18:42:42')::timestamp with time zone,   'test',   'freeradius', 
'192.168.98.101'::inet,   '0C4C399C58F7'::macaddr, 
parse_bssid(NULLIF('0x303030303230303630313030', '')), 
COALESCE(NULLIF('', ''), '0')::smallint);
    id   |    inner_id     |        attribute         |  val   | op
--------+-----------------+--------------------------+--------+----
       0 | test at freeradius | Hotlining-Enabled        | True   | :=
  702095 | test at freeradius | Cleartext-Password       | test01 | :=
  702096 | test at freeradius | Simultaneous-Use         | 1      | :=
(3 rows)

csnrads=# SELECT id, inner_id, attribute, val, op   FROM radreply( 
'test at freeradius',  '192.168.98.101'::inet,   COALESCE(NULLIF('True', 
''), 'FALSE')::boolean);
  id  |    inner_id     |        attribute         | 
        val                            | op
-----+-----------------+--------------------------+----------------------------------------------------------+----
    6 | test at freeradius | Hotline-Indicator        | active 
                                       | :=
    7 | test at freeradius | Hotline-Profile-Id       | 
freeradius/hotprofile                                    | :=
   11 | test at freeradius | Termination-Action       | Radius-Request 
                                       | :=
   35 | test at freeradius | Session-Timeout          | 900 
                                       | :=
(4 rows)


More information about the Freeradius-Users mailing list