Run sql Module after exec in radpostauth
Hello I am using external php script for authentication which I configured in users file , the PHP script will return the reason of the reject and I want to save this reason in radpost auth table, the problem is if I set SQL module before exec and the user was accepted as PAP but rejected from my script then the radpost auth will have access-accept even if he is rejected by my PHP script , this is the debug (1) Received Access-Request Id 245 from 127.0.0.1:53576 to 127.0.0.1:1812 length 96 (1) User-Name = "abhibose" (1) User-Password = "1234" (1) Calling-Station-Id = "4e:f9:5e:77:0c:9a" (1) NAS-Port = 102 (1) NAS-IP-Address = 103.200.57.138 (1) Framed-Protocol = PPP (1) Framed-IP-Address = 192.168.0.1 (1) NAS-Identifier = "nas" (1) # Executing section authorize from file /usr/local/etc/raddb/sites-enabled/default (1) authorize { (1) [preprocess] = ok (1) [chap] = noop (1) [mschap] = noop (1) [digest] = noop (1) suffix: Checking for suffix after "@" (1) suffix: No '@' in User-Name = "abhibose", looking up realm NULL (1) suffix: No such realm "NULL" (1) [suffix] = noop (1) eap: No EAP-Message, not doing EAP (1) [eap] = noop (1) files: users: Matched entry DEFAULT at line 48 (1) files: EXPAND /usr/bin/php /var/www/html/cloudradius/captiveportal/auth_new.php "%{User-Name}" "%{User-Password}" "%{Calling-Station-Id}" "%{NAS-Port}" "%{NAS-IP-Address}" "%{Framed-Protocol}" "%{Framed-IP-Address}" "%{Filter-Id}" "%{NAS-Identifier}" (1) files: --> /usr/bin/php /var/www/html/cloudradius/captiveportal/auth_new.php "abhibose" "1234" "4e:f9:5e:77:0c:9a" "102" "103.200.57.138" "PPP" "192.168.0.1" "" "nas" (1) [files] = ok (1) sql: EXPAND %{User-Name} (1) sql: --> abhibose (1) sql: SQL-User-Name set to 'abhibose' rlm_sql (sql): Closing connection (4): Hit idle_timeout, was idle for 68 seconds rlm_sql_mysql: Socket destructor called, closing socket rlm_sql (sql): Reserved connection (0) (1) sql: EXPAND SELECT id, username, attribute, value, op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'abhibose' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radcheck WHERE username = 'abhibose' ORDER BY id (1) sql: User found in radcheck table (1) sql: Conditional check items matched, merging assignment check items (1) sql: Simultaneous-Use := 1 (1) sql: Cleartext-Password := "1234" (1) sql: EXPAND SELECT id, username, attribute, value, op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id (1) sql: --> SELECT id, username, attribute, value, op FROM radreply WHERE username = 'abhibose' ORDER BY id (1) sql: Executing select query: SELECT id, username, attribute, value, op FROM radreply WHERE username = 'abhibose' ORDER BY id rlm_sql (sql): Reserved connection (1) rlm_sql (sql): Released connection (1) Need 6 more connections to reach 10 spares rlm_sql (sql): Opening additional connection (6), 1 of 27 pending slots used rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'cloudradius' on Localhost via UNIX socket, server version 5.5.65-MariaDB, protocol version 10 (1) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (1) sql: --> SELECT groupname FROM radusergroup WHERE username = 'abhibose' ORDER BY priority (1) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'abhibose' ORDER BY priority (1) sql: User not found in any groups rlm_sql (sql): Released connection (0) (1) [sql] = ok (1) if (User-Name =~ /([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})/i) { (1) if (User-Name =~ /([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})/i) -> FALSE (1) [pap] = updated (1) } # authorize = updated (1) Found Auth-Type = PAP (1) # Executing group from file /usr/local/etc/raddb/sites-enabled/default (1) Auth-Type PAP { (1) pap: Login attempt with password (1) pap: Comparing with "known good" Cleartext-Password (1) pap: User authenticated successfully (1) [pap] = ok (1) } # Auth-Type PAP = ok (1) # Executing section session from file /usr/local/etc/raddb/sites-enabled/default (1) session { (1) sql: EXPAND %{User-Name} (1) sql: --> abhibose (1) sql: SQL-User-Name set to 'abhibose' rlm_sql (sql): Reserved connection (5) (1) sql: EXPAND SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL (1) sql: --> SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL (1) sql: Executing select query: SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL rlm_sql (sql): Released connection (5) (1) [sql] = ok (1) } # session = ok (1) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default (1) post-auth { (1) sql: EXPAND .query (1) sql: --> .query (1) sql: Using query template 'query' rlm_sql (sql): Reserved connection (2) (1) sql: EXPAND %{User-Name} (1) sql: --> abhibose (1) sql: SQL-User-Name set to 'abhibose' (1) sql: EXPAND INSERT into radpostauth (username, pass, mac,framedipaddress, nasportid, calledstationid, nasipaddress, reply, authdate, reason) values ('%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{Calling-Station-Id}','%{Framed-IP-Address}', '%{NAS-Port-Id}','%{Called-Station-Id}','%{NAS-IP-Address}', '%{reply:Packet-Type}', NOW(), '%{reply:Reply-Message}') (1) sql: --> INSERT into radpostauth (username, pass, mac,framedipaddress, nasportid, calledstationid, nasipaddress, reply, authdate, reason) values ('abhibose', '1234', '4e:f9:5e:77:0c:9a','192.168.0.1', '','','103.200.57.138', 'Access-Accept', NOW(), '') (1) sql: Executing query: INSERT into radpostauth (username, pass, mac,framedipaddress, nasportid, calledstationid, nasipaddress, reply, authdate, reason) values ('abhibose', '1234', '4e:f9:5e:77:0c:9a','192.168.0.1', '','','103.200.57.138', 'Access-Accept', NOW(), '') (1) sql: SQL query returned: success (1) sql: 1 record(s) updated rlm_sql (sql): Released connection (2) (1) [sql] = ok (1) exec: Executing: /usr/bin/php /var/www/html/cloudradius/captiveportal/auth_new.php "abhibose" "1234" "4e:f9:5e:77:0c:9a" "102" "103.200.57.138" "PPP" "192.168.0.1" "" "nas" : (1) exec: ERROR: Program returned code (1) and output 'Reply-Message := "Your Account has been expired."' (1) [exec] = reject (1) } # post-auth = reject (1) Delaying response for 1.000000 seconds Waking up in 0.1 seconds. Waking up in 0.8 seconds. (1) Sending delayed response (1) Sent Access-Reject Id 245 from 127.0.0.1:1812 to 127.0.0.1:53576 length 52 (1) Reply-Message := "Your Account has been expired." Waking up in 3.9 seconds. (1) Cleaning up request packet ID 245 with timestamp +68 Ready to process requests ^CYou have new mail in /var/spool/mail/root so if I put SQL module after exec , then the sqk will not run at all . so how can I run SQL module after exec thank you in advance
On Oct 3, 2020, at 11:01 AM, Muhammed Buvaydani via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
I am using external php script for authentication which I configured in users file , the PHP script will return the reason of the reject and I want to save this reason in radpost auth table, the problem is if I set SQL module before exec and the user was accepted as PAP but rejected from my script then the radpost auth will have access-accept even if he is rejected by my PHP script , this is the debug
Move "sql" to after "exec" then.
so if I put SQL module after exec , then the sqk will not run at all .
Yes, you can also list "sql" in the "Post-Auth-Type Reject" section. Which will log the reject. Alan DeKok.
many thanks for your reply , actually this is my post auth config post-auth { exec sql Post-Auth-Type REJECT { sql attr_filter.access_reject } } and this the log when I do this configuration it is not run the SQL module after exec in reject type rlm_sql (sql): Released connection (0) (0) [sql] = ok (0) if (User-Name =~ /([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})/i) { (0) if (User-Name =~ /([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})/i) -> FALSE (0) [pap] = updated (0) } # authorize = updated (0) Found Auth-Type = PAP (0) # Executing group from file /usr/local/etc/raddb/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 session from file /usr/local/etc/raddb/sites-enabled/default (0) session { (0) sql: EXPAND %{User-Name} (0) sql: --> abhibose (0) sql: SQL-User-Name set to 'abhibose' rlm_sql (sql): Reserved connection (2) (0) sql: EXPAND SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL (0) sql: --> SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL (0) sql: Executing select query: SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL rlm_sql (sql): Released connection (2) (0) [sql] = ok (0) } # session = ok (0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default (0) post-auth { (0) exec: Executing: /usr/bin/php /var/www/html/cloudradius/captiveportal/auth_new.php "abhibose" "1234" "4e:f9:5e:77:0c:9a" "102" "103.200.57.138" "PPP" "192.168.0.1" "" "nas" : (0) exec: ERROR: Program returned code (1) and output 'Reply-Message :="Your Account has been expired."' (0) [exec] = reject (0) } # post-auth = reject (0) Delaying response for 1.000000 seconds Waking up in 0.2 seconds. Waking up in 0.7 seconds. (0) Sending delayed response (0) Sent Access-Reject Id 146 from 127.0.0.1:1812 to 127.0.0.1:54425 length 52 (0) Reply-Message := "Your Account has been expired." Waking up in 3.9 seconds. [1562314050593] ________________________________ From: Alan DeKok <aland@deployingradius.com> Sent: Saturday, October 3, 2020 7:22:29 PM To: FreeRadius users mailing list Cc: Muhammed Buvaydani Subject: Re: Run sql Module after exec in radpostauth On Oct 3, 2020, at 11:01 AM, Muhammed Buvaydani via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
I am using external php script for authentication which I configured in users file , the PHP script will return the reason of the reject and I want to save this reason in radpost auth table, the problem is if I set SQL module before exec and the user was accepted as PAP but rejected from my script then the radpost auth will have access-accept even if he is rejected by my PHP script , this is the debug
Move "sql" to after "exec" then.
so if I put SQL module after exec , then the sqk will not run at all .
Yes, you can also list "sql" in the "Post-Auth-Type Reject" section. Which will log the reject. Alan DeKok.
On Oct 3, 2020, at 4:07 PM, Muhammed Buvaydani via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
many thanks for your reply , actually this is my post auth config
That's good...
and this the log when I do this configuration it is not run the SQL module after exec in reject type
OK...
(0) post-auth { (0) exec: Executing: /usr/bin/php /var/www/html/cloudradius/captiveportal/auth_new.php "abhibose" "1234" "4e:f9:5e:77:0c:9a" "102" "103.200.57.138" "PPP" "192.168.0.1" "" "nas" : (0) exec: ERROR: Program returned code (1) and output 'Reply-Message :="Your Account has been expired."' (0) [exec] = reject (0) } # post-auth = reject (0) Delaying response for 1.000000 seconds
You're running a _very_ old version of v3. As in more than 6 years old. Upgrade to the latest release, and it will work. Alan DeKok.
hello Alan Thank you for your help , actually I upgraded mu radius server to 3.0.21 and I tested the post auth. if my authentication script just send reply-message without reject it fires the post auth and insert in database. like this (0) # Executing section session from file /usr/local/etc/raddb/sites-enabled/default (0) session { (0) sql: EXPAND %{User-Name} (0) sql: --> abhibose (0) sql: SQL-User-Name set to 'abhibose' rlm_sql (sql): Reserved connection (5) (0) sql: EXPAND SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL (0) sql: --> SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL (0) sql: Executing select query: SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL rlm_sql (sql): Released connection (5) (0) [sql] = ok (0) } # session = ok (0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default (0) post-auth { (0) exec: Executing: /usr/bin/php /var/www/html/test.php: (0) exec: Program returned code (0) and output 'Reply-Message := "Your Account is expired"' (0) exec: Program executed successfully (0) [exec] = ok (0) sql: EXPAND .query (0) sql: --> .query (0) sql: Using query template 'query' rlm_sql (sql): Reserved connection (6) (0) sql: EXPAND %{User-Name} (0) sql: --> abhibose (0) sql: SQL-User-Name set to 'abhibose' (0) sql: EXPAND INSERT into radpostauth (username, pass, mac,framedipaddress, nasportid, calledstationid, nasipaddress, reply, authdate, reason) values ('%{User-Name}', '%{%{User-Password}:-%{Chap-Password}}', '%{Calling-Station-Id}','%{Framed-IP-Address}', '%{NAS-Port-Id}','%{Called-Station-Id}','%{NAS-IP-Address}', '%{reply:Packet-Type}', NOW(), '%{reply:Reply-Message}') (0) sql: --> INSERT into radpostauth (username, pass, mac,framedipaddress, nasportid, calledstationid, nasipaddress, reply, authdate, reason) values ('abhibose', '1234', '4e:f9:5e:77:0c:9a','192.168.0.1', '','','103.200.57.138', 'Access-Accept', NOW(), 'Your Account is expired') (0) sql: Executing query: INSERT into radpostauth (username, pass, mac,framedipaddress, nasportid, calledstationid, nasipaddress, reply, authdate, reason) values ('abhibose', '1234', '4e:f9:5e:77:0c:9a','192.168.0.1', '','','103.200.57.138', 'Access-Accept', NOW(), 'Your Account is expired') but when I send reject I get this in debug rlm_sql_mysql: Starting connect to MySQL server rlm_sql_mysql: Connected to database 'cloudradius' on Localhost via UNIX socket, server version 5.5.65-MariaDB, protocol version 10 (4) sql: EXPAND SELECT groupname FROM radusergroup WHERE username = '%{SQL-User-Name}' ORDER BY priority (4) sql: --> SELECT groupname FROM radusergroup WHERE username = 'abhibose' ORDER BY priority (4) sql: Executing select query: SELECT groupname FROM radusergroup WHERE username = 'abhibose' ORDER BY priority (4) sql: User not found in any groups rlm_sql (sql): Released connection (9) (4) [sql] = ok (4) if (User-Name =~ /([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})/i) { (4) if (User-Name =~ /([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})/i) -> FALSE (4) [pap] = updated (4) } # authorize = updated (4) Found Auth-Type = PAP (4) # Executing group from file /usr/local/etc/raddb/sites-enabled/default (4) Auth-Type PAP { (4) pap: Login attempt with password (4) pap: Comparing with "known good" Cleartext-Password (4) pap: User authenticated successfully (4) [pap] = ok (4) } # Auth-Type PAP = ok (4) # Executing section session from file /usr/local/etc/raddb/sites-enabled/default (4) session { (4) sql: EXPAND %{User-Name} (4) sql: --> abhibose (4) sql: SQL-User-Name set to 'abhibose' rlm_sql (sql): Reserved connection (10) (4) sql: EXPAND SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL (4) sql: --> SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL (4) sql: Executing select query: SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL rlm_sql (sql): Released connection (10) (4) [sql] = ok (4) } # session = ok (4) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default (4) post-auth { (4) exec: Executing: /usr/bin/php /var/www/html/cloudradius/captiveportal/auth_new.php "abhibose" "1234" "4e:f9:5e:77:0c:9a" "102" "103.200.57.138" "PPP" "192.168.0.1" "" "nas" : (4) exec: ERROR: Program returned code (1) and output 'Reply-Message := "Your Account has been expired."' (4) [exec] = reject (4) } # post-auth = reject (4) Delaying response for 1.000000 seconds Waking up in 0.1 seconds. Waking up in 0.8 seconds. (4) Sending delayed response (4) Sent Access-Reject Id 39 from 127.0.0.1:1812 to 127.0.0.1:40840 length 52 (4) Reply-Message := "Your Account has been expired." Waking up in 3.9 seconds. (4) Cleaning up request packet ID 39 with timestamp +587 Ready to process requests it is not fire SQL module at all , and this is my config in post auth reject Post-Auth-Type REJECT { # log failed authentications in SQL, too. sql attr_filter.access_reject # Insert EAP-Failure message if the request was # rejected by policy instead of because of an # authentication failure eap # Remove reply message if the response contains an EAP-Message remove_reply_message_if_eap } thank you in advance ________________________________ From: Muhammed Buvaydani Sent: Saturday, October 3, 2020 11:07:27 PM To: Alan DeKok; FreeRadius users mailing list Subject: Re: Run sql Module after exec in radpostauth many thanks for your reply , actually this is my post auth config post-auth { exec sql Post-Auth-Type REJECT { sql attr_filter.access_reject } } and this the log when I do this configuration it is not run the SQL module after exec in reject type rlm_sql (sql): Released connection (0) (0) [sql] = ok (0) if (User-Name =~ /([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})/i) { (0) if (User-Name =~ /([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})[-:.]?([0-9a-f]{2})[-:]?([0-9a-f]{2})/i) -> FALSE (0) [pap] = updated (0) } # authorize = updated (0) Found Auth-Type = PAP (0) # Executing group from file /usr/local/etc/raddb/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 session from file /usr/local/etc/raddb/sites-enabled/default (0) session { (0) sql: EXPAND %{User-Name} (0) sql: --> abhibose (0) sql: SQL-User-Name set to 'abhibose' rlm_sql (sql): Reserved connection (2) (0) sql: EXPAND SELECT COUNT(*) FROM radacct WHERE username = '%{SQL-User-Name}' AND acctstoptime IS NULL (0) sql: --> SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL (0) sql: Executing select query: SELECT COUNT(*) FROM radacct WHERE username = 'abhibose' AND acctstoptime IS NULL rlm_sql (sql): Released connection (2) (0) [sql] = ok (0) } # session = ok (0) # Executing section post-auth from file /usr/local/etc/raddb/sites-enabled/default (0) post-auth { (0) exec: Executing: /usr/bin/php /var/www/html/cloudradius/captiveportal/auth_new.php "abhibose" "1234" "4e:f9:5e:77:0c:9a" "102" "103.200.57.138" "PPP" "192.168.0.1" "" "nas" : (0) exec: ERROR: Program returned code (1) and output 'Reply-Message :="Your Account has been expired."' (0) [exec] = reject (0) } # post-auth = reject (0) Delaying response for 1.000000 seconds Waking up in 0.2 seconds. Waking up in 0.7 seconds. (0) Sending delayed response (0) Sent Access-Reject Id 146 from 127.0.0.1:1812 to 127.0.0.1:54425 length 52 (0) Reply-Message := "Your Account has been expired." Waking up in 3.9 seconds. [1562314050593] ________________________________ From: Alan DeKok <aland@deployingradius.com> Sent: Saturday, October 3, 2020 7:22:29 PM To: FreeRadius users mailing list Cc: Muhammed Buvaydani Subject: Re: Run sql Module after exec in radpostauth On Oct 3, 2020, at 11:01 AM, Muhammed Buvaydani via Freeradius-Users <freeradius-users@lists.freeradius.org> wrote:
I am using external php script for authentication which I configured in users file , the PHP script will return the reason of the reject and I want to save this reason in radpost auth table, the problem is if I set SQL module before exec and the user was accepted as PAP but rejected from my script then the radpost auth will have access-accept even if he is rejected by my PHP script , this is the debug
Move "sql" to after "exec" then.
so if I put SQL module after exec , then the sqk will not run at all .
Yes, you can also list "sql" in the "Post-Auth-Type Reject" section. Which will log the reject. Alan DeKok.
participants (2)
-
Alan DeKok -
Muhammed Buvaydani