SQL query as fallback to auth script?
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.
*(2) ERROR: Program returned code (1) and output 'Reject'* On Fri, May 3, 2019 at 9:16 AM Wladyslaw Jankowski <wladekj@interia.pl> wrote:
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.
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
> *(2) ERROR: Program returned code (1) and output 'Reject'*This is the idea: script should always reject - doing its thing behind the scenes - and allow for SQL fallback. I can't "Accept" RADIUS auth with this script as it can't calculate MSCHAP challanges and no cleartext password will be provided to it (can't use PAP). I have changed the exit code script is returning but "sql" under "if(fail)" (desired fallback) still doesn't seem to be used: (0) pap: WARNING: No "known good" password found for the user. Not setting Auth-Type (0) pap: WARNING: Authentication will fail unless a "known good" password is available (0) [pap] = noop (0) update control { (0) Executing: /bin/python /scripts/radiusauth.py '%{User-Name}' 'rejectme': (0) EXPAND %{User-Name} (0) --> provided-username (0) Program returned code (0) and output 'Reject' (0) Auth-Type := Reject (0) } # update control = noop (0) if (fail) { (0) if (fail) -> FALSE (0) } # authorize = ok (0) Found Auth-Type = Reject (0) Auth-Type = Reject, rejecting user (0) Failed to authenticate the user ThanksWJ
On May 3, 2019, at 10:40 AM, Wladyslaw Jankowski <wladekj@interia.pl> wrote:
Learn how to format your messages. When you make it difficult for us to help you, we're inclined to avoid helping you.
*(2) ERROR: Program returned code (1) and output 'Reject'*
This is the idea: script should always reject - doing its thing behind the scenes - and allow for SQL fallback.
That makes no sense whatsoever. If the script always rejects, then you always need a work-around for that reject. Why not just have the script do it's thing, and have it return an "ok" code? What you're doing now is nailing your feet to the floor, and then asking for help walking across the room. Just don't do that.
I can't "Accept" RADIUS auth with this script as it can't calculate MSCHAP challanges and no cleartext password will be provided to it (can't use PAP).
Yes, FreeRADIUS does authentication. Your script doesn't. That's the way it should work./
I have changed the exit code script is returning but "sql" under "if(fail)" (desired fallback) still doesn't seem to be used: (0) pap: WARNING: No "known good" password found for the user. Not setting Auth-Type
Perhaps you could set a password? Or, set "Auth-Type := Accept" if you want the user to always be accepted. The problem here is that you're asking how to fix the "solution" you came up with. That's bad practice. Instead, you should be describing your requirements. We can then offer you advice as to how to meet those requirements. So what are you trying to do? What *problem* are you trying to solve? And don't answer "run the script and SQL". That isn't relevant. What are the users trying to do? What answers do you want the RADIUS server to return? And why? Alan DeKok.
Hi, On 04.05.19 16:52, Alan DeKok wrote:
My requirements were basically that I would like to be able to authorize users with EAP-MSCHAPv2 / EAP-TTLS without having direct access to the db with NT passwords. Well, it has to access the DB *somehow* in order to read the passwords.
From a practical point of view, there's no difference between FreeRADIUS accessing the DB directly, or using a script to do that. The script is just security theatre. It doesn't add any security. It doesn't prevent any attacks.
Devices that would have access to the RADIUS server are located in various locations and for security reasons it was safer to allow access via client-side script - so server-side script can be monitored for anomalies like brute-force attempts. But it can be done other way, too.
With PAP it is possible to pass %{User-Password} to the script but no such field is present in MSCHAPv2 packets. The fallback-idea was to blindly grab the (one given) hash from the proprietary solution and allow FreeRADIUS to validate credentials based on it. Short after it was supposed to be deleted from local store. Why cache it if you're quickly deleting it? That doesn't make any sense. It wasn't supposed to be caching system... at least not caching these password for longer than like 10 seconds to allow for fallback. Having FreeRADIUS access the DB is the easiest approach.
I understand but was hoping to avoid it as it would require additional layer of security (IPsec tunnel or something similar, from each client to the RADIUS server) for RADIUS packets to travel over the internet.
What you want do do is this:
authorize { ... sql if (notfound) { update control { NT-Password := `/path/to/script` } } ...
The script can then:
a) access the external DB to get the password b) insert the password into SQL c) return the hex password (0xabcdef...) as a string to stdout.
OK. From my understanding - 1st login attempt would always fail but following ones would use the "cached" password (so I can't delete it after 10 seconds). Probably better to go with "FreeRADIUS with direct SQL access" approach then, than to slowly create a copy of the database. But what is the reason for c)? I can see that printing the hash (lower or upper case) doesn't seem to have an effect. Would that work (if FreeRADIUS could use the hash printed to stdout) - this might be a solution not requiring local databases and no second login attempts! What I'm getting now, using the password 'dupa.8' seems like Access-Challenge is being treated as Access-Reject by the VPN software. (0) [eap] = ok (0) update control { (0) Executing: /bin/python /scripts/radiusauth.py '%{User-Name}' 'failme': (0) EXPAND %{User-Name} (0) --> provided-username (0) Program returned code (0) and output '0x9853107D57DAC0881DE7676395A03F5F' (0) NT-Password := 9853107d57dac0881de7676395a03f5f (0) } # update control = noop (0) } # authorize = ok (0) Found Auth-Type = eap (0) # Executing group from file /etc/raddb/sites-enabled/default (0) authenticate { (0) eap: Peer sent packet with method EAP Identity (1) (0) eap: Calling submodule eap_tls to process data (0) eap_tls: Initiating new EAP-TLS session (0) eap_tls: Setting verify mode to require certificate from client (0) eap_tls: [eaptls start] = request (0) eap: Sending EAP Request (code 1) ID 1 length 6 (0) eap: EAP session adding &reply:State = 0x2cec41732ced4cc5 (0) [eap] = handled (0) } # authenticate = handled (0) Using Post-Auth-Type Challenge (0) # Executing group from file /etc/raddb/sites-enabled/default (0) C hallenge { ... } # empty sub-section is ignored (0) Sent Access-Challenge Id 221 from 127.0.0.1:1812 to 127.0.0.1:28318 length 0 (0) EAP-Message = 0x010100060d20 (0) Message-Authenticator = 0x00000000000000000000000000000000 (0) State = 0x2cec41732ced4cc52ba998708fe9bf68 (0) Finished request Authorize "ok", authenticate "handled" - what might be the reason for another Access-Challenge, than?
But realize that this *also* means that the script (and thus FreeRADIUS) has write permission to the SQL authentication table. That's a security problem. The default config has the authentication tables read-only for very good reason.
Understood. Thank you, WJ
On May 6, 2019, at 11:16 AM, Wladyslaw Jankowski <wladekj@interia.pl> wrote:
Devices that would have access to the RADIUS server are located in various locations and for security reasons it was safer to allow access via client-side script - so server-side script can be monitored for anomalies like brute-force attempts. But it can be done other way, too.
I'm saying that it's not safer.
It wasn't supposed to be caching system... at least not caching these password for longer than like 10 seconds to allow for fallback.
If you're not caching it, then there's no need for SQL. And "allowing for fallback" isn't necessary. Just have the script return the correct password
I understand but was hoping to avoid it as it would require additional layer of security (IPsec tunnel or something similar, from each client to the RADIUS server) for RADIUS packets to travel over the internet.
That's what people often do. But... how does a shell script on the RADIUS server side change the security of packets going over the internet? Answer: it doesn't.
OK. From my understanding - 1st login attempt would always fail but following ones would use the "cached" password (so I can't delete it after 10 seconds).
No. That's not what it does. It look up the cached password in SQL *first*, and only if it's not found, does it run the script.
Probably better to go with "FreeRADIUS with direct SQL access" approach then, than to slowly create a copy of the database.
But what is the reason for c)? I can see that printing the hash (lower or upper case) doesn't seem to have an effect.
If you follow my advice, it *will* have affect. If there's no NT-Password in SQL, then the server can get one from the script.
Would that work (if FreeRADIUS could use the hash printed to stdout) - this might be a solution not requiring local databases and no second login attempts!
It doesn't need multiple login attempts. And yes, you can just run a script and have the output assigned to an attribute. See "man unlang". This is documented. You can just get rid of SQL, and do: update control { NT-Password := `/path/to/script ... arguments ...` } But again, the shell script doesn't add any security. It's a waste of your time.
What I'm getting now, using the password 'dupa.8' seems like Access-Challenge is being treated as Access-Reject by the VPN software.
Ask the vendor of the VPN software how their product works.
Authorize "ok", authenticate "handled" - what might be the reason for another Access-Challenge, than?
EAP uses multiple challenges. That's how it works. Alan DeKok.
Hi, On 06.05.19 17:33, Alan DeKok wrote:
If you're not caching it, then there's no need for SQL. And "allowing for fallback" isn't necessary. Just have the script return the correct password
This was the case in my last log and the problem was in my failure to understand which protocol the client is using and how it communicates. Thank you for your help.
participants (3)
-
Alan DeKok -
Jorge Pereira -
Wladyslaw Jankowski