SQL query as fallback to auth script?

3 May 2019

      Hi list! 

This issue was probably already answered but I cannot find it. I have a setup where FreeRADIUS can't have access to the database where NT hashes are stored. I would like FreeRADIUS to fire up a script and than fallback to SQL. This way I could at least temporarily grab the hash to local database with the script, script would "Reject", and FreeRADIUS would fall back to local SQL where the hash temporarily exists. After all the EAP magic - FreeRADIUS would try authorize the user via local database.

This is a VPN (not NAS, WiFi..) setup that for best compatibility with most operating systems would use EAP-MSCHAPv2 or EAP-TTLS but in any case - server is not receiving plaintext password from the user (like with PAP) so I can't pass it to the script.
I have tried the following configuration, but the only SQL queries fired after script "Rejects" the user are INSERTS logging this failure:
authorize {
        filter_username
        preprocess
        auth_log
        mschap
        digest
        expiration
        logintime
        eap
        pap
        update control {
                Auth-Type := `/bin/python /scripts/radiusauth.py '%{User-Name}' 'rejectme'`
        }
        if (fail) {
                sql
        }
}

Please find the log below. 

(2) Received Access-Request Id 197 from 127.0.0.1:28318 to 127.0.0.1:1812 length 144
(2)   User-Name = "provided-username"
(2)   NAS-Port-Type = Virtual
(2)   Service-Type = Framed-User
(2)   NAS-Port = 35
(2)   NAS-Port-Id = "IKEv2"
(2)   NAS-IP-Address = server-public-ip
(2)   Called-Station-Id = "server-public-ip[4500]"
(2)   Calling-Station-Id = "client-public-ip[60403]"
(2)   EAP-Message = 0x0200000a0121349a
(2)   NAS-Identifier = "vpn-software"
(2)   Message-Authenticator = 0xa123abc123abc123abc123abc123abc1
(2) # Executing section authorize from file /etc/raddb/sites-enabled/default
(2)   authorize {
(2)     policy filter_username {
(2)       if (&User-Name) {
(2)       if (&User-Name)  -> TRUE
(2)       if (&User-Name)  {
(2)         if (&User-Name =~ / /) {
(2)         if (&User-Name =~ / /)  -> FALSE
(2)         if (&User-Name =~ /@[^@]*@/ ) {
(2)         if (&User-Name =~ /@[^@]*@/ )  -> FALSE
(2)         if (&User-Name =~ /\.\./ ) {
(2)         if (&User-Name =~ /\.\./ )  -> FALSE
(2)         if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))  {
(2)         if ((&User-Name =~ /@/) && (&User-Name !~ /@(.+)\.(.+)$/))   -> FALSE
(2)         if (&User-Name =~ /\.$/)  {
(2)         if (&User-Name =~ /\.$/)   -> FALSE
(2)         if (&User-Name =~ /@\./)  {
(2)         if (&User-Name =~ /@\./)   -> FALSE
(2)       } # if (&User-Name)  = notfound
(2)     } # policy filter_username = notfound
(2)     [preprocess] = ok
(2) auth_log: EXPAND /var/log/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d
(2) auth_log:    --> /var/log/radacct/127.0.0.1/auth-detail-20190503
(2) auth_log: /var/log/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/auth-detail-%Y%m%d expands to /var/log/radacct/127.0.0.1/auth-detail-20190503
(2) auth_log: EXPAND %t
(2) auth_log:    --> Fri May  3 07:11:31 2019
(2)     [auth_log] = ok
(2)     [chap] = noop
(2)     [mschap] = noop
(2)     [digest] = noop
(2)     [expiration] = noop
(2)     [logintime] = noop
(2) eap: Peer sent EAP Response (code 2) ID 0 length 10
(2) eap: EAP-Identity reply, returning 'ok' so we can short-circuit the rest of authorize
(2)     [eap] = ok
(2) pap: WARNING: No "known good" password found for the user.  Not setting Auth-Type
(2) pap: WARNING: Authentication will fail unless a "known good" password is available
(2)     [pap] = noop
(2)     update control {
(2)      Executing: /bin/python /scripts/radiusauth.py '%{User-Name}' 'rejectme':
(2)       EXPAND %{User-Name}
(2)          --> provided-username
(2)       ERROR: Program returned code (1) and output 'Reject'
(2)     } # update control = fail
(2)   } # authorize = fail
(2) Invalid user (Program returned code (1) and output 'Reject'): [provided-username/<via Auth-Type = eap>] (from client localhost port 35 cli client-public-ip[60403])
(2) Using Post-Auth-Type Reject
(2) # Executing group from file /etc/raddb/sites-enabled/default
(2)   Post-Auth-Type REJECT {
(2) sql: EXPAND .query
(2) sql:    --> .query
(2) sql: Using query template 'query'
rlm_sql (sql): Closing connection (6): Hit idle_timeout, was idle for 5679 seconds
rlm_sql (sql): You probably need to lower "min"
rlm_sql (sql): Closing connection (7): Hit idle_timeout, was idle for 5679 seconds
rlm_sql (sql): You probably need to lower "min"
rlm_sql (sql): 0 of 0 connections in use.  You  may need to increase "spare"
rlm_sql (sql): Opening additional connection (8), 1 of 32 pending slots used
rlm_sql (sql): Reserved connection (8)
(2) sql: EXPAND %{User-Name}
(2) sql:    --> provided-username
(2) sql: SQL-User-Name set to 'provided-username'
(2) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S')
(2) sql:    --> INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'provided-username', '', 'Access-Reject', '2019-05-03 07:11:31')
(2) sql: Executing query: INSERT INTO radpostauth (username, pass, reply, authdate) VALUES ( 'provided-username', '', 'Access-Reject', '2019-05-03 07:11:31')
(2) sql: SQL query returned: success
(2) sql: 1 record(s) updated
rlm_sql (sql): Released connection (8)
Need 2 more connections to reach min connections (3)
rlm_sql (sql): Opening additional connection (9), 1 of 31 pending slots used
(2)     [sql] = ok
(2) attr_filter.access_reject: EXPAND %{User-Name}
(2) attr_filter.access_reject:    --> provided-username
(2) attr_filter.access_reject: Matched entry DEFAULT at line 11
(2)     [attr_filter.access_reject] = updated
(2) eap: Request was previously rejected, inserting EAP-Failure
(2) eap: Sending EAP Failure (code 4) ID 0 length 4
(2)     [eap] = updated
(2)   } # Post-Auth-Type REJECT = updated
(2) Login incorrect (Program returned code (1) and output 'Reject'): [provided-username/<via Auth-Type = eap>] (from client localhost port 35 cli client-public-ip[60403])
(2) Delaying response for 1.000000 seconds
Waking up in 0.9 seconds.
(2) Sending delayed response
(2) Sent Access-Reject Id 197 from 127.0.0.1:1812 to 127.0.0.1:28318 length 44
(2)   EAP-Message = 0x04000004
(2)   Message-Authenticator = 0x00000000000000000000000000000000
Waking up in 3.9 seconds.
(2) Cleaning up request packet ID 197 with timestamp +6729
Ready to process requests

TIA and apologies again if the question was already answered.

SQL query as fallback to auth script?

Wladyslaw Jankowski