Postgresql groupreply problem

31 Aug 2015

      Hello @ all,

I want to change the user MAC-authentication with an HP-Switch from the users-file to postgresql. The first thing is, that I have a some lines which check whether it is a Call-Check or not. If not then the radius has to reject this. But I think it's ok if I put this into the users-file. I have written the Cleartext-Password in this file, too, because I get an chap error (chap: ERROR: &control:Cleartext-Password is required for authentication), when I put this into the database.

In the Moment my configurations looks like this:

-------------- users file --------------
DEFAULT Service-Type != Call-Check, Auth-Type := Reject
        Fall-Through = Yes

DEFAULT Cleartext-Password := "xxxXXXxxx"
        Fall-Through = Yes

DEFAULT
        Egress-VLANID := 0x3200005C   #<-- check in server.conf; reject if it's the same

-------------- server.conf --------------
...
authorize {
        suffix
        if (Realm != "NULL") {
                reject
        }
        chap
        files.sw-hp-mac-v3
        sql.sw-hp-mac-v3
        if (reply:Egress-VLANID == "0x3200005C") {
                reject
        }
}

-------------- Database --------------
(Hope that the format is ok. The tables radreply and radgroupcheck are empty)

+----------------------------------------------------------------------+
| radcheck                                                             |
+----+-------------------+--------------------+----+-------------------+
| id | username          | attribute          | op | value             |
+----+-------------------+--------------------+----+-------------------+
|  0 | 00:11:22:33:44:55 | Calling-Station-Id | == | 00-11-22-33-44-55 |
+----+-------------------+--------------------+----+-------------------+

+-----------------------------------------------+
| radusergroup                                  |
+----+-------------------+-----------+----------+
| id | username          | groupname | priority | 
+----+-------------------+-----------+----------+
|  0 | 00:11:22:33:44:55 |       abc |        0 |
+----+-------------------+-----------+----------+

+--------------------------------------------------+
| radgroupreply                                    |
+----+-----------+---------------+----+------------+
| id | groupname | attribute     | op | value      |
+----+-----------+---------------+----+------------+
|  0 |       abc | Egress-VLANID | := | 0x320000E0 |
+----+-----------+---------------+----+------------+

The main problem is that the freeradius does not insert the GroupName in the sql-commands and so there is no correct reply.

-------------- Debug-Log --------------
(0) Received Access-Request Id 138 from 172.16.xx.xx:1812 to 172.16.xx.xx:1816 length 321
(0)   Framed-MTU = 1466
(0)   NAS-IP-Address = 172.16..xx.xx
(0)   NAS-Identifier = "sw01"
(0)   User-Name = "00:11:22:33:44:55"
(0)   Service-Type = Call-Check
(0)   Framed-Protocol = PPP
(0)   NAS-Port = 23
(0)   NAS-Port-Type = Ethernet
(0)   NAS-Port-Id = "A23"
(0)   Called-Station-Id = "a1-a2-a3-a4-a5-a6"
(0)   Calling-Station-Id = "00-11-22-33-44-55"
(0)   Connect-Info = "CONNECT Ethernet 100Mbps Full duplex"
(0)   CHAP-Password = 0x366421f13cb0e52b71c59e9ec39f5a83fe
(0)   Message-Authenticator = 0x61c0cb3d1d3b1a2f5f634817119d999f
(0)   MS-RAS-Vendor = 11
(0)   HP-Capability-Advert = 0x011a0000000b28
(0)   HP-Capability-Advert = 0x011a0000000b2e
(0)   HP-Capability-Advert = 0x011a0000000b30
(0)   HP-Capability-Advert = 0x011a0000000b3d
(0)   HP-Capability-Advert = 0x0138
(0)   HP-Capability-Advert = 0x013a
(0)   HP-Capability-Advert = 0x0140
(0)   HP-Capability-Advert = 0x0141
(0)   HP-Capability-Advert = 0x0151
(0) # Executing section authorize from file /etc/freeradius/sites-enabled/sw-hp-mac
(0)   authorize {
(0) suffix: Checking for suffix after "@"
(0) suffix: No '@' in User-Name = "00:11:22:33:44:55", looking up realm NULL
(0) suffix: Found realm "NULL"
(0) suffix: Adding Stripped-User-Name = "00:11:22:33:44:55"
(0) suffix: Adding Realm = "NULL"
(0) suffix: Authentication realm is LOCAL
(0)     [suffix] = ok
(0)     if (Realm != "NULL") {
(0)     if (Realm != "NULL")  -> FALSE
(0) chap:   &control:Auth-Type := CHAP
(0)     [chap] = ok
(0) files.sw-hp-mac-v3: users: Matched entry DEFAULT at line 4
(0) files.sw-hp-mac-v3: users: Matched entry DEFAULT at line 7
(0)     [files.sw-hp-mac-v3] = ok
(0) sql.sw-hp-mac-v3: EXPAND %{User-Name}
(0) sql.sw-hp-mac-v3:    --> 00:11:22:33:44:55
(0) sql.sw-hp-mac-v3: SQL-User-Name set to '00:11:22:33:44:55' 
rlm_sql (sql.sw-hp-mac-v3): Reserved connection (0)
(0) sql.sw-hp-mac-v3: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
(0) sql.sw-hp-mac-v3:    --> SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '00:11:22:33:44:55' ORDER BY id
(0) sql.sw-hp-mac-v3: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '00:11:22:33:44:55' ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 1 , fields = 5
(0) sql.sw-hp-mac-v3: User found in radcheck table
(0) sql.sw-hp-mac-v3: Conditional check items matched, merging assignment check items
(0) sql.sw-hp-mac-v3: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id
(0) sql.sw-hp-mac-v3:    --> SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = '00:11:22:33:44:55' ORDER BY id
(0) sql.sw-hp-mac-v3: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = '00:11:22:33:44:55' ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 0 , fields = 5
(0) sql.sw-hp-mac-v3: EXPAND SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority
(0) sql.sw-hp-mac-v3:    --> SELECT GroupName FROM radusergroup WHERE UserName='00:11:22:33:44:55' ORDER BY priority
(0) sql.sw-hp-mac-v3: Executing select query: SELECT GroupName FROM radusergroup WHERE UserName='00:11:22:33:44:55' ORDER BY priority
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 1 , fields = 1
(0) sql.sw-hp-mac-v3: User found in the group table
(0) sql.sw-hp-mac-v3: EXPAND SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '%{Sql-Group}' ORDER BY id
(0) sql.sw-hp-mac-v3:    --> SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '' ORDER BY id
(0) sql.sw-hp-mac-v3: Executing select query: SELECT id, GroupName, Attribute, Value, op FROM radgroupcheck WHERE GroupName = '' ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 0 , fields = 5
(0) sql.sw-hp-mac-v3: Group "abc": Conditional check items matched
(0) sql.sw-hp-mac-v3: Group "abc": Merging assignment check items
(0) sql.sw-hp-mac-v3: EXPAND SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '%{Sql-Group}' ORDER BY id
(0) sql.sw-hp-mac-v3:    --> SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '' ORDER BY id
(0) sql.sw-hp-mac-v3: Executing select query: SELECT id, GroupName, Attribute, Value, op FROM radgroupreply WHERE GroupName = '' ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 0 , fields = 5
(0) sql.sw-hp-mac-v3: Group "abc": Merging reply items
rlm_sql (sql.sw-hp-mac-v3): Released connection (0)
(0)     [sql.sw-hp-mac-v3] = ok
(0)     if (reply:Egress-VLANID == "0x3200005C") {
(0)     if (reply:Egress-VLANID == "0x3200005C")  -> TRUE
(0)     if (reply:Egress-VLANID == "0x3200005C")  {
(0)       [reject] = reject
(0)     } # if (reply:Egress-VLANID == "0x3200005C")  = reject
(0)   } # authorize = reject
(0) Using Post-Auth-Type Reject
(0) # Executing group from file /etc/freeradius/sites-enabled/sw-hp-mac
(0)   Post-Auth-Type REJECT {
(0) sql.sw-hp-mac-v3: EXPAND .query
(0) sql.sw-hp-mac-v3:    --> .query
(0) sql.sw-hp-mac-v3: Using query template 'query'
rlm_sql (sql.sw-hp-mac-v3): Reserved connection (1)
(0) sql.sw-hp-mac-v3: EXPAND %{User-Name}
(0) sql.sw-hp-mac-v3:    --> 00:11:22:33:44:55
(0) sql.sw-hp-mac-v3: SQL-User-Name set to '00:11:22:33:44:55'
(0) sql.sw-hp-mac-v3: EXPAND INSERT INTO radpostauth (username, pass, reply, calledstationid, callingstationid, authdate) VALUES('%{User-Name}', '%{%{User-Password}:-Chap-Password}', '%{reply:Packet-Type}', '%{Called-Station-Id}', '%{Calling-Station-Id}', NOW())
(0) sql.sw-hp-mac-v3:    --> INSERT INTO radpostauth (username, pass, reply, calledstationid, callingstationid, authdate) VALUES('00:11:22:33:44:55', 'Chap-Password', 'Access-Reject', 'a1-a2-a3-a4-a5-a6', '00-11-22-33-44-55', NOW())
(0) sql.sw-hp-mac-v3: Executing query: INSERT INTO radpostauth (username, pass, reply, calledstationid, callingstationid, authdate) VALUES('00:11:22:33:44:55', 'Chap-Password', 'Access-Reject', 'a1-a2-a3-a4-a5-a6', '00-11-22-33-44-55', NOW())
rlm_sql_postgresql: Status: PGRES_COMMAND_OK
rlm_sql_postgresql: query affected rows = 1
(0) sql.sw-hp-mac-v3: SQL query returned: success
(0) sql.sw-hp-mac-v3: 1 record(s) updated
rlm_sql (sql.sw-hp-mac-v3): Released connection (1)
(0)     [sql.sw-hp-mac-v3] = ok
(0) attr_filter.access_reject: EXPAND %{User-Name}
(0) attr_filter.access_reject:    --> 00:11:22:33:44:55
(0) attr_filter.access_reject: Matched entry DEFAULT at line 18
(0)     [attr_filter.access_reject] = updated
(0)   } # Post-Auth-Type REJECT = updated
(0) Delaying response for 1.000000 seconds
Waking up in 0.3 seconds.
Waking up in 0.6 seconds.
(0) <delay>: Sending delayed response
(0) <delay>: Sent Access-Reject Id 138 from 172.16.xx.xx:1816 to 172.16.xx.xx:1812 length 20
Waking up in 3.9 seconds.
(0) <delay>: Cleaning up request packet ID 138 with timestamp +10

Does anyone have an idea?

Best regards.

Postgresql groupreply problem

georg＠wojti.de