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@coyhile.com