Run sql Module after exec in radpostauth
Muhammed Buvaydani
muhammed.buvaydani at netcom.com.tr
Mon Oct 5 14:36:53 CEST 2020
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 at 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 at 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.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OutlookEmoji-1562314050593d9f82ea2-a3af-4ad3-ae48-089a45f4e572.png
Type: image/png
Size: 39094 bytes
Desc: OutlookEmoji-1562314050593d9f82ea2-a3af-4ad3-ae48-089a45f4e572.png
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20201005/d64b14fb/attachment-0008.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OutlookEmoji-1562314050593bf0b1080-00d9-4aa2-8bf9-d3f997f15d99.png
Type: image/png
Size: 39094 bytes
Desc: OutlookEmoji-1562314050593bf0b1080-00d9-4aa2-8bf9-d3f997f15d99.png
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20201005/d64b14fb/attachment-0009.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OutlookEmoji-15623140505935f16b94d-cefa-4db9-b3de-2c93a68c2d8c.png
Type: image/png
Size: 39094 bytes
Desc: OutlookEmoji-15623140505935f16b94d-cefa-4db9-b3de-2c93a68c2d8c.png
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20201005/d64b14fb/attachment-0010.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OutlookEmoji-156231405059391d4b85d-2452-474a-9a26-3f70300608f0.png
Type: image/png
Size: 39094 bytes
Desc: OutlookEmoji-156231405059391d4b85d-2452-474a-9a26-3f70300608f0.png
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20201005/d64b14fb/attachment-0011.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OutlookEmoji-1562314050593a66c9f89-69ea-4be5-99ab-139b22e98d7f.png
Type: image/png
Size: 39094 bytes
Desc: OutlookEmoji-1562314050593a66c9f89-69ea-4be5-99ab-139b22e98d7f.png
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20201005/d64b14fb/attachment-0012.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OutlookEmoji-156231405059375e9146c-6106-41bf-889d-5b9fa74f20fe.png
Type: image/png
Size: 39094 bytes
Desc: OutlookEmoji-156231405059375e9146c-6106-41bf-889d-5b9fa74f20fe.png
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20201005/d64b14fb/attachment-0013.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OutlookEmoji-15623140505936d595557-0c40-4777-9ba6-9c52bff3e220.png
Type: image/png
Size: 39094 bytes
Desc: OutlookEmoji-15623140505936d595557-0c40-4777-9ba6-9c52bff3e220.png
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20201005/d64b14fb/attachment-0014.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: OutlookEmoji-156231405059339267fcb-397e-4a17-8ce1-8b4f48aead7b.png
Type: image/png
Size: 39094 bytes
Desc: OutlookEmoji-156231405059339267fcb-397e-4a17-8ce1-8b4f48aead7b.png
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20201005/d64b14fb/attachment-0015.png>
More information about the Freeradius-Users
mailing list