sql authorization but using LDAP for authentication?
Coy Hile
coy.hile at coyhile.com
Fri Jan 7 22:02:45 CET 2022
Hi all,
I’m trying to centralize a few things in my POC setup on my network so that I don’t have separate RADIUS password for access to terminal servers or network devices). I see the following:
=====
(0) Received Access-Request Id 204 from 192.168.42.18:33452 to 172.17.64.47:1812 length 90
(0) User-Name = "hile"
(0) User-Password = “REDACTED"
(0) NAS-IP-Address = 192.168.42.18
(0) NAS-Identifier = "basement-anc1"
(0) NAS-Port = 32427
(0) NAS-Port-Type = Virtual
(0) Calling-Station-Id = "10.100.9.64"
(0) # Executing section authorize from file /etc/freeradius/sites-enabled/default
(0) authorize {
(0) policy filter_username {
(0) if (&User-Name) {
(0) if (&User-Name) -> TRUE
(0) if (&User-Name) {
(0) if (&User-Name =~ / /) {
(0) if (&User-Name =~ / /) -> FALSE
(0) if (&User-Name =~ /@[^@]*@/ ) {
(0) if (&User-Name =~ /@[^@]*@/ ) -> FALSE
(0) if (&User-Name =~ /\.\./ ) {
(0) if (&User-Name =~ /\.\./ ) -> FALSE
(0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) {
(0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE
(0) if (&User-Name =~ /\.$/) {
(0) if (&User-Name =~ /\.$/) -> FALSE
(0) if (&User-Name =~ /@\./) {
(0) if (&User-Name =~ /@\./) -> FALSE
(0) } # if (&User-Name) = notfound
(0) } # policy filter_username = notfound
(0) policy filter_password {
(0) if (&User-Password && (&User-Password != "%{string:User-Password}")) {
(0) EXPAND %{string:User-Password}
(0) --> REDACTED
(0) if (&User-Password && (&User-Password != "%{string:User-Password}")) -> TRUE
(0) if (&User-Password && (&User-Password != "%{string:User-Password}")) {
(0) update request {
(0) EXPAND %{string:User-Password}
(0) —> REDACTED
&Tmp-String-0 := dkG0,t7Ly
(0) EXPAND %{string:Tmp-String-0}
(0) --> REDACTED
(0) &User-Password := dkG0,t7Ly
(0) &Tmp-String-0 !* ANY
(0) } # update request = noop
(0) } # if (&User-Password && (&User-Password != "%{string:User-Password}")) = noop
(0) } # policy filter_password = noop
(0) [preprocess] = ok
(0) auth_log: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d
(0) auth_log: --> /var/log/freeradius/radacct/192.168.42.18/auth-detail-20220107
(0) auth_log: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d expands to /var/log/freeradius/radacct/192.168.42.18/auth-detail-20220107
(0) auth_log: EXPAND %t
(0) auth_log: --> Fri Jan 7 20:45:37 2022
(0) [auth_log] = ok
(0) [chap] = noop
(0) [mschap] = noop
(0) [digest] = noop
(0) suffix: Checking for suffix after "@"
(0) suffix: No '@' in User-Name = "hile", looking up realm NULL
(0) suffix: No such realm "NULL"
(0) [suffix] = noop
(0) eap: No EAP-Message, not doing EAP
(0) [eap] = noop
(0) [files] = noop
rlm_ldap (ldap): Reserved connection (0)
(0) ldap: EXPAND (uid=%{%{Stripped-User-Name}:-%{User-Name}})
(0) ldap: --> (uid=hile)
(0) ldap: Performing search in "dc=coyhile,dc=com" with filter "(uid=hile)", scope "sub"
(0) ldap: Waiting for search result...
(0) ldap: User object found at DN "uid=hile,ou=people,dc=coyhile,dc=com"
(0) ldap: Processing user attributes
(0) ldap: control:Password-With-Header += '{crypt}$6$ut$GUTveD8XX1rMcJSpadioxYp7YUtSdTMauWDcNuzQu3mqVn20k8x3Wo2Kb6K9V8BeliKYCC9THwEEbnH5yc2vH.'
rlm_ldap (ldap): Released connection (0)
Need more connections to reach 10 spares
rlm_ldap (ldap): Opening additional connection (5), 1 of 27 pending slots used
rlm_ldap (ldap): Connecting to ldap://ldap.coyhile.com:389
rlm_ldap (ldap): Waiting for bind result...
rlm_ldap (ldap): Bind successful
rlm_ldap (ldap): Closing expired connection (4) - Hit idle_timeout limit
rlm_ldap (ldap): Closing expired connection (3) - Hit idle_timeout limit
rlm_ldap (ldap): Closing expired connection (2) - Hit idle_timeout limit
rlm_ldap (ldap): You probably need to lower "min"
rlm_ldap (ldap): Closing expired connection (1) - Hit idle_timeout limit
(0) [ldap] = updated
(0) sql: EXPAND %{User-Name}
(0) sql: --> hile
(0) sql: SQL-User-Name set to 'hile'
rlm_sql (sql): Reserved connection (1)
(0) sql: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
(0) sql: --> SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = 'hile' ORDER BY id
(0) sql: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = 'hile' ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 0 , fields = 5
(0) sql: WARNING: User not found in radcheck table.
(0) sql: EXPAND SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority
(0) sql: --> SELECT GroupName FROM radusergroup WHERE UserName='hile' ORDER BY priority
(0) sql: Executing select query: SELECT GroupName FROM radusergroup WHERE UserName='hile' ORDER BY priority
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 0 , fields = 1
(0) sql: User not found in any groups
rlm_sql (sql): Released connection (1)
Need more connections to reach 10 spares
rlm_sql (sql): Opening additional connection (6), 1 of 26 pending slots used
rlm_sql_postgresql: Connecting using parameters: dbname='radius' host='postgres.svc.a4b01392-e48d-4cdf-cacf-f850d1800d45.basement.cns.coyhile.com' port=5432 user='radius' password='radpass' application_name='FreeRADIUS 3.0.25 - radiusd (sql)'
Connected to database 'radius' on 'postgres.svc.a4b01392-e48d-4cdf-cacf-f850d1800d45.basement.cns.coyhile.com' server version 120001, protocol version 3, backend PID 113082
rlm_sql (sql): Closing expired connection (5) - Hit idle_timeout limit
rlm_sql_postgresql: Socket destructor called, closing socket
rlm_sql (sql): Closing expired connection (4) - Hit idle_timeout limit
rlm_sql_postgresql: Socket destructor called, closing socket
rlm_sql (sql): Closing expired connection (3) - Hit idle_timeout limit
rlm_sql_postgresql: Socket destructor called, closing socket
rlm_sql (sql): Closing expired connection (2) - Hit idle_timeout limit
rlm_sql_postgresql: Socket destructor called, closing socket
rlm_sql (sql): You probably need to lower "min"
rlm_sql (sql): Closing expired connection (0) - Hit idle_timeout limit
rlm_sql_postgresql: Socket destructor called, closing socket
(0) [sql] = notfound
(0) [expiration] = noop
(0) [logintime] = noop
(0) pap: Converted: &control:Password-With-Header -> &control:Crypt-Password
(0) pap: Removing &control:Password-With-Header
(0) [pap] = updated
(0) } # authorize = updated
(0) Found Auth-Type = PAP
(0) # Executing group from file /etc/freeradius/sites-enabled/default
(0) Auth-Type PAP {
(0) pap: Login attempt with password
(0) pap: Comparing with "known-good" Crypt-password
(0) pap: User authenticated successfully
(0) [pap] = ok
(0) } # Auth-Type PAP = ok
(0) # Executing section post-auth from file /etc/freeradius/sites-enabled/default
(0) post-auth {
(0) if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) {
(0) if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) -> FALSE
(0) update {
(0) No attributes updated for RHS &session-state:
(0) } # update = noop
(0) reply_log: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/reply-detail-%Y%m%d
(0) reply_log: --> /var/log/freeradius/radacct/192.168.42.18/reply-detail-20220107
(0) reply_log: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/reply-detail-%Y%m%d expands to /var/log/freeradius/radacct/192.168.42.18/reply-detail-20220107
(0) reply_log: EXPAND %t
(0) reply_log: --> Fri Jan 7 20:45:37 2022
(0) [reply_log] = ok
(0) sql: EXPAND .query
(0) sql: --> .query
(0) sql: Using query template 'query'
rlm_sql (sql): Reserved connection (1)
(0) sql: EXPAND %{User-Name}
(0) sql: --> hile
(0) sql: SQL-User-Name set to 'hile'
(0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate ) VALUES('%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S.%M' )
(0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate ) VALUES('hile', 'dkG0=3D2Ct7Ly', 'Access-Accept', '2022-01-07 20:45:37.860687' )
(0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate ) VALUES('hile', 'dkG0=3D2Ct7Ly', 'Access-Accept', '2022-01-07 20:45:37.860687' )
rlm_sql_postgresql: Status: PGRES_COMMAND_OK
rlm_sql_postgresql: query affected rows = 1
(0) sql: SQL query returned: success
(0) sql: 1 record(s) updated
rlm_sql (sql): Released connection (1)
(0) [sql] = ok
(0) [exec] = noop
(0) policy remove_reply_message_if_eap {
(0) if (&reply:EAP-Message && &reply:Reply-Message) {
(0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE
(0) else {
(0) [noop] = noop
(0) } # else = noop
(0) } # policy remove_reply_message_if_eap = noop
(0) if (EAP-Key-Name && &reply:EAP-Session-Id) {
(0) if (EAP-Key-Name && &reply:EAP-Session-Id) -> FALSE
(0) } # post-auth = ok
(0) Sent Access-Accept Id 204 from 172.17.64.47:1812 to 192.168.42.18:33452 length 20
(0) Finished request
Waking up in 4.9 seconds.
(0) Cleaning up request packet ID 204 with timestamp +69
Ready to process requests
=======
My question is that since there’s a username set from the LDAP module, but NOT one found in the SQL module, the sql module never checks the `radreply` table.
Contrast that with a setup where I have a password set in the SQL module itself, one sees:
======
Ready to process requests
(0) Received Access-Request Id 91 from 192.168.42.18:1530 to 172.17.64.47:1812 length 90
(0) User-Name = "hile"
(0) User-Password = "dkG0,t7Ly\000\000\000\000\000\001a"
(0) NAS-IP-Address = 192.168.42.18
(0) NAS-Identifier = "basement-anc1"
(0) NAS-Port = 505
(0) NAS-Port-Type = Virtual
(0) Calling-Station-Id = "10.100.9.64"
(0) # Executing section authorize from file /etc/freeradius/sites-enabled/default
(0) authorize {
(0) policy filter_username {
(0) if (&User-Name) {
(0) if (&User-Name) -> TRUE
(0) if (&User-Name) {
(0) if (&User-Name =~ / /) {
(0) if (&User-Name =~ / /) -> FALSE
(0) if (&User-Name =~ /@[^@]*@/ ) {
(0) if (&User-Name =~ /@[^@]*@/ ) -> FALSE
(0) if (&User-Name =~ /\.\./ ) {
(0) if (&User-Name =~ /\.\./ ) -> FALSE
(0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) {
(0) if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/)) -> FALSE
(0) if (&User-Name =~ /\.$/) {
(0) if (&User-Name =~ /\.$/) -> FALSE
(0) if (&User-Name =~ /@\./) {
(0) if (&User-Name =~ /@\./) -> FALSE
(0) } # if (&User-Name) = notfound
(0) } # policy filter_username = notfound
(0) policy filter_password {
(0) if (&User-Password && (&User-Password != "%{string:User-Password}")) {
(0) EXPAND %{string:User-Password}
(0) --> dkG0,t7Ly
(0) if (&User-Password && (&User-Password != "%{string:User-Password}")) -> TRUE
(0) if (&User-Password && (&User-Password != "%{string:User-Password}")) {
(0) update request {
(0) EXPAND %{string:User-Password}
(0) --> dkG0,t7Ly
(0) &Tmp-String-0 := dkG0,t7Ly
(0) EXPAND %{string:Tmp-String-0}
(0) --> dkG0,t7Ly
(0) &User-Password := dkG0,t7Ly
(0) &Tmp-String-0 !* ANY
(0) } # update request = noop
(0) } # if (&User-Password && (&User-Password != "%{string:User-Password}")) = noop
(0) } # policy filter_password = noop
(0) [preprocess] = ok
(0) auth_log: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d
(0) auth_log: --> /var/log/freeradius/radacct/192.168.42.18/auth-detail-20220107
(0) auth_log: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d expands to /var/log/freeradius/radacct/192.168.42.18/auth-detail-20220107
(0) auth_log: EXPAND %t
(0) auth_log: --> Fri Jan 7 20:59:11 2022
(0) [auth_log] = ok
(0) [chap] = noop
(0) [mschap] = noop
(0) [digest] = noop
(0) suffix: Checking for suffix after "@"
(0) suffix: No '@' in User-Name = "hile", looking up realm NULL
(0) suffix: No such realm "NULL"
(0) [suffix] = noop
(0) eap: No EAP-Message, not doing EAP
(0) [eap] = noop
(0) [files] = noop
(0) sql: EXPAND %{User-Name}
(0) sql: --> hile
(0) sql: SQL-User-Name set to 'hile'
rlm_sql (sql): Reserved connection (1)
(0) sql: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = '%{SQL-User-Name}' ORDER BY id
(0) sql: --> SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = 'hile' ORDER BY id
(0) sql: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radcheck WHERE Username = 'hile' ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 1 , fields = 5
(0) sql: User found in radcheck table
(0) sql: Conditional check items matched, merging assignment check items
(0) sql: Cleartext-Password := "dkG0,t7Ly"
(0) sql: EXPAND SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = '%{SQL-User-Name}' ORDER BY id
(0) sql: --> SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = 'hile' ORDER BY id
(0) sql: Executing select query: SELECT id, UserName, Attribute, Value, Op FROM radreply WHERE Username = 'hile' ORDER BY id
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 3 , fields = 5
(0) sql: User found in radreply table, merging reply items
(0) sql: Arista-AVPair := "shell:priv-lvl=15"
(0) sql: Arista-AVPair := "shell:roles=network-admin"
(0) sql: Filter-Id := ":group_name=admin;"
(0) sql: EXPAND SELECT GroupName FROM radusergroup WHERE UserName='%{SQL-User-Name}' ORDER BY priority
(0) sql: --> SELECT GroupName FROM radusergroup WHERE UserName='hile' ORDER BY priority
(0) sql: Executing select query: SELECT GroupName FROM radusergroup WHERE UserName='hile' ORDER BY priority
rlm_sql_postgresql: Status: PGRES_TUPLES_OK
rlm_sql_postgresql: query affected rows = 0 , fields = 1
(0) sql: User not found in any groups
rlm_sql (sql): Released connection (1)
Need more connections to reach 10 spares
rlm_sql (sql): Opening additional connection (6), 1 of 26 pending slots used
rlm_sql_postgresql: Connecting using parameters: dbname='radius' host='postgres.svc.a4b01392-e48d-4cdf-cacf-f850d1800d45.basement.cns.coyhile.com' port=5432 user='radius' password='radpass' application_name='FreeRADIUS 3.0.25 - radiusd (sql)'
Connected to database 'radius' on 'postgres.svc.a4b01392-e48d-4cdf-cacf-f850d1800d45.basement.cns.coyhile.com' server version 120001, protocol version 3, backend PID 113633
(0) [sql] = ok
rlm_ldap (ldap): Reserved connection (0)
(0) ldap: EXPAND (uid=%{%{Stripped-User-Name}:-%{User-Name}})
(0) ldap: --> (uid=hile)
(0) ldap: Performing search in "dc=coyhile,dc=com" with filter "(uid=hile)", scope "sub"
(0) ldap: Waiting for search result...
(0) ldap: User object found at DN "uid=hile,ou=people,dc=coyhile,dc=com"
(0) ldap: Processing user attributes
(0) ldap: control:Password-With-Header += '{crypt}$6$ut$GUTveD8XX1rMcJSpadioxYp7YUtSdTMauWDcNuzQu3mqVn20k8x3Wo2Kb6K9V8BeliKYCC9THwEEbnH5yc2vH.'
rlm_ldap (ldap): Released connection (0)
Need more connections to reach 10 spares
rlm_ldap (ldap): Opening additional connection (5), 1 of 27 pending slots used
rlm_ldap (ldap): Connecting to ldap://ldap.coyhile.com:389
rlm_ldap (ldap): Waiting for bind result...
rlm_ldap (ldap): Bind successful
(0) [ldap] = updated
(0) [expiration] = noop
(0) [logintime] = noop
(0) pap: WARNING: Config already contains a "known good" password (&control:Cleartext-Password). Ignoring &control:Password-With-Header
(0) [pap] = updated
(0) } # authorize = updated
(0) Found Auth-Type = PAP
(0) # Executing group from file /etc/freeradius/sites-enabled/default
(0) Auth-Type PAP {
(0) pap: Login attempt with password
(0) pap: Comparing with "known good" Cleartext-Password
(0) pap: User authenticated successfully
(0) [pap] = ok
(0) } # Auth-Type PAP = ok
(0) # Executing section post-auth from file /etc/freeradius/sites-enabled/default
(0) post-auth {
(0) if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) {
(0) if (session-state:User-Name && reply:User-Name && request:User-Name && (reply:User-Name == request:User-Name)) -> FALSE
(0) update {
(0) No attributes updated for RHS &session-state:
(0) } # update = noop
(0) reply_log: EXPAND /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/reply-detail-%Y%m%d
(0) reply_log: --> /var/log/freeradius/radacct/192.168.42.18/reply-detail-20220107
(0) reply_log: /var/log/freeradius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/reply-detail-%Y%m%d expands to /var/log/freeradius/radacct/192.168.42.18/reply-detail-20220107
(0) reply_log: EXPAND %t
(0) reply_log: --> Fri Jan 7 20:59:11 2022
(0) [reply_log] = ok
(0) sql: EXPAND .query
(0) sql: --> .query
(0) sql: Using query template 'query'
rlm_sql (sql): Reserved connection (2)
(0) sql: EXPAND %{User-Name}
(0) sql: --> hile
(0) sql: SQL-User-Name set to 'hile'
(0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate ) VALUES('%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S.%M' )
(0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate ) VALUES('hile', 'dkG0=3D2Ct7Ly', 'Access-Accept', '2022-01-07 20:59:11.751612' )
(0) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate ) VALUES('hile', 'dkG0=3D2Ct7Ly', 'Access-Accept', '2022-01-07 20:59:11.751612' )
rlm_sql_postgresql: Status: PGRES_COMMAND_OK
rlm_sql_postgresql: query affected rows = 1
(0) sql: SQL query returned: success
(0) sql: 1 record(s) updated
rlm_sql (sql): Released connection (2)
Need more connections to reach 10 spares
rlm_sql (sql): Opening additional connection (7), 1 of 25 pending slots used
rlm_sql_postgresql: Connecting using parameters: dbname='radius' host='postgres.svc.a4b01392-e48d-4cdf-cacf-f850d1800d45.basement.cns.coyhile.com' port=5432 user='radius' password='radpass' application_name='FreeRADIUS 3.0.25 - radiusd (sql)'
Connected to database 'radius' on 'postgres.svc.a4b01392-e48d-4cdf-cacf-f850d1800d45.basement.cns.coyhile.com' server version 120001, protocol version 3, backend PID 113634
(0) [sql] = ok
(0) [exec] = noop
(0) policy remove_reply_message_if_eap {
(0) if (&reply:EAP-Message && &reply:Reply-Message) {
(0) if (&reply:EAP-Message && &reply:Reply-Message) -> FALSE
(0) else {
(0) [noop] = noop
(0) } # else = noop
(0) } # policy remove_reply_message_if_eap = noop
(0) if (EAP-Key-Name && &reply:EAP-Session-Id) {
(0) if (EAP-Key-Name && &reply:EAP-Session-Id) -> FALSE
(0) } # post-auth = ok
(0) Sent Access-Accept Id 91 from 172.17.64.47:1812 to 192.168.42.18:1530 length 98
(0) Arista-AVPair = "shell:priv-lvl=15"
(0) Arista-AVPair = "shell:roles=network-admin"
(0) Filter-Id = ":group_name=admin;"
(0) Finished request
Waking up in 4.9 seconds.
(0) Cleaning up request packet ID 91 with timestamp +51
=======
Is there some configuration that I need to tweak to make the server check `radreply` table even there’s no user returned from the rad check table?
--
Coy Hile
coy.hile at coyhile.com
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 488 bytes
Desc: Message signed with OpenPGP
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20220107/aa5f8c1e/attachment-0001.sig>
More information about the Freeradius-Users
mailing list