DHCP. Several dhcp attributes from the sql table
Sergey Kodentsev
sergk at ic.vrn.ru
Sat Dec 25 10:45:02 CET 2021
Hi,
> You have to use the new v3 queries, which are in a new location.
> Just read the file you edited to see where the queries are. Then, read the query file to see how they work. This is all documented in great detail.
Simplifying the configuration
Apart from adding the file dhcpic in sites-enabled and changing the file
sql, nothing has been changed in the release freeradius 3.0.25
The rlm_sql module in this configuration does not change the Request
control and Request update , unlike freeradius 2.
Only the post-auth section is executed, authorize_check_query and
authorize_reply_query are not executed. although they are in the sql module.
------------------------------------------------------------------------------------------
# cat dhcpic
# -*- text -*-
server dhcpic {
listen {
ipaddr = raddhcpic
port = 67
type = dhcp
broadcast = no
}
dhcp DHCP-Discover {
sql
}
dhcp DHCP-Request {
sql
}
dhcp DHCP-Inform {
}
dhcp DHCP-Decline {
}
dhcp DHCP-Release {
}
dhcp {
}
}
--------------------------------------------------------------------
Slightly modified file sql from freeradius 3.0.25
# diff -u sql /usr/local/radius3/etc/raddb/mods-enabled/sql
--- sql 2021-10-07 22:46:04.000000000 +0300
+++ /usr/local/radius3/etc/raddb/mods-enabled/sql 2021-12-25
10:23:41.302444436 +0300
@@ -37,7 +37,7 @@
# sqlite
# mongo
#
- dialect = "sqlite"
+ dialect = "mysql"
#
# The driver module used to execute the queries. Since we
@@ -57,8 +57,8 @@
# rlm_sql_iodbc
# rlm_sql_unixodbc
#
- driver = "rlm_sql_null"
-# driver = "rlm_sql_${dialect}"
+# driver = "rlm_sql_null"
+ driver = "rlm_sql_${dialect}"
#
# Driver-specific subsections. They will only be loaded and
@@ -84,13 +84,13 @@
mysql {
# If any of the files below are set, TLS encryption is
enabled
tls {
- ca_file = "/etc/ssl/certs/my_ca.crt"
- ca_path = "/etc/ssl/certs/"
- certificate_file =
"/etc/ssl/certs/private/client.crt"
- private_key_file =
"/etc/ssl/certs/private/client.key"
- cipher = "DHE-RSA-AES256-SHA:AES128-SHA"
+# ca_file = "/etc/ssl/certs/my_ca.crt"
+# ca_path = "/etc/ssl/certs/"
+# certificate_file =
"/etc/ssl/certs/private/client.crt"
+# private_key_file =
"/etc/ssl/certs/private/client.key"
+# cipher = "DHE-RSA-AES256-SHA:AES128-SHA"
- tls_required = yes
+ tls_required = no
tls_check_cert = no
tls_check_cert_cn = no
}
@@ -158,10 +158,10 @@
# Connection info:
#
-# server = "localhost"
+ server = "localhost"
# port = 3306
-# login = "radius"
-# password = "radpass"
+ login = "raduser"
+ password = "radpass"
# Connection info for Mongo
# Authentication Without SSL
@@ -176,7 +176,7 @@
# server = mongodb://<DERIVED
USERNAME>@192.168.0.2:PORT/DATABASE?authSource=$external&ssl=true&authMechanism=MONGODB-X509
# Database table configuration for everything except Oracle
- radius_db = "radius"
+ radius_db = "raddbdhcp"
# If you are using Oracle then use this instead
# radius_db =
"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SID=your_sid)))"
@@ -211,11 +211,11 @@
# If set to 'yes' (default) we read the group tables unless
Fall-Through = no in the reply table.
# If set to 'no' we do not read the group tables unless
Fall-Through = yes in the reply table.
-# read_groups = yes
+ read_groups = no
# If set to 'yes' (default) we read profiles unless
Fall-Through = no in the groupreply table.
# If set to 'no' we do not read profiles unless Fall-Through =
yes in the groupreply table.
-# read_profiles = yes
+ read_profiles = no
# Remove stale session if checkrad does not see a double login
delete_stale_sessions = yes
----------------------------------------------------------------------------------------------------------------------------------------------------
Debug
...............
# Loaded module rlm_sql
# Loading module "sql" from file
/usr/local/radius3/etc/raddb/mods-enabled/sql
sql {
driver = "rlm_sql_mysql"
server = "localhost"
port = 0
login = "raduser"
password = <<< secret >>>
radius_db = "raddbdhcp"
read_groups = no
read_profiles = no
read_clients = no
delete_stale_sessions = yes
sql_user_name = "%{User-Name}"
default_user_profile = ""
client_query = "SELECT id, nasname, shortname, type, secret,
server FROM nas"
authorize_check_query = "SELECT id, username, attribute, value,
op FROM radcheck WHERE username = '%{SQL-User-Name}' ORDER BY id"
authorize_reply_query = "SELECT id, username, attribute, value,
op FROM radreply WHERE username = '%{SQL-User-Name}' ORDER BY id"
authorize_group_check_query = "SELECT id, groupname, attribute,
Value, op FROM radgroupcheck WHERE groupname = '%{SQL-Group}' ORDER BY id"
authorize_group_reply_query = "SELECT id, groupname, attribute,
value, op FROM radgroupreply WHERE groupname = '%{SQL-Group}' ORDER BY id"
group_membership_query = "SELECT groupname FROM radusergroup
WHERE username = '%{SQL-User-Name}' ORDER BY priority"
simul_count_query = "SELECT COUNT(*) FROM radacct WHERE
username = '%{SQL-User-Name}' AND acctstoptime IS NULL"
simul_verify_query = "SELECT radacctid, acctsessionid,
username, nasipaddress, nasportid, framedipaddress, callingstationid,
framedprotocol FROM radacct WHERE username = '%{SQL
-User-Name}' AND acctstoptime IS NULL"
safe_characters =
"@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /"
auto_escape = no
accounting {
reference =
"%{tolower:type.%{%{Acct-Status-Type}:-%{Request-Processing-Stage}}.query}"
type {
accounting-on {
query = "UPDATE radacct SET acctstoptime =
FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), acctsessiontime =
'%{%{integer:Event-Timestamp}:-%l}' - UNIX_TIMESTAMP(acctstart
time), acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}'
WHERE acctstoptime IS NULL AND nasipaddress = '%{NAS-IP-Address}' AND
acctstarttime <= FROM_UNIXTIME(%{%{integer:
Event-Timestamp}:-%l})"
}
accounting-off {
query = "UPDATE radacct SET acctstoptime =
FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), acctsessiontime =
'%{%{integer:Event-Timestamp}:-%l}' - UNIX_TIMESTAMP(acctstart
time), acctterminatecause = '%{%{Acct-Terminate-Cause}:-NAS-Reboot}'
WHERE acctstoptime IS NULL AND nasipaddress = '%{NAS-IP-Address}' AND
acctstarttime <= FROM_UNIXTIME(%{%{integer:
Event-Timestamp}:-%l})"
}
start {
query = "INSERT INTO radacct (acctsessionid,
acctuniqueid, username, realm, nasipaddress,
nasportid, nasporttype,
acctstarttime, acctupdatetime, acctstoptime,
acctsessiontime, acctauthentic, connectinfo_start,
connectinfo_stop, acctinputoctets, acctoutputoctet
s, calledstationid, callingstationid, acctterminatecause,
servicetype, framedprotocol, framedipaddress,
framedipv6address, framedipv6prefix, framed
interfaceid, delegatedipv6prefix ) VALUES ('%{Acct-Session-Id}',
'%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}',
'%{NAS-IP-Address}', '%{%{NAS-Port-ID}:-%{NAS-Port}
}', '%{NAS-Port-Type}',
FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}),
FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), NULL, '0',
'%{Acct-Authentic}', '%{Connect-Info}', '', '0',
'0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}',
'%{Framed-IPv6-Address}', '%{Framed-IPv6-Prefix}', '%{Framed-
Interface-Id}', '%{Delegated-IPv6-Prefix}' )"
}
interim-update {
query = "UPDATE radacct SET acctupdatetime =
(@acctupdatetime_old:=acctupdatetime), acctupdatetime =
FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), acctinterval = %{%{i
nteger:Event-Timestamp}:-%l} - UNIX_TIMESTAMP(@acctupdatetime_old),
framedipaddress = '%{Framed-IP-Address}', framedipv6address =
'%{Framed-IPv6-Address}', framedipv6prefix = '%{Framed
-IPv6-Prefix}', framedinterfaceid = '%{Framed-Interface-Id}',
delegatedipv6prefix = '%{Delegated-IPv6-Prefix}', acctsessiontime =
%{%{Acct-Session-Time}:-NULL}, acctinputoctets = '%{%{
Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',
acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}' WHERE AcctUniqueId = '%{Acct-
Unique-Session-Id}'"
}
stop {
query = "UPDATE radacct SET acctstoptime =
FROM_UNIXTIME(%{%{integer:Event-Timestamp}:-%l}), acctsessiontime =
%{%{Acct-Session-Time}:-NULL}, acctinputoctets
= '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}',
acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 |
'%{%{Acct-Output-Octets}:-0}', acctterminatecause =
'%{Acct-Terminate-Cause}', connectinfo_stop = '%{Connect-Info}' WHERE
AcctUniqueId = '%{Acct-Unique-Session-Id}'"
}
}
}
post-auth {
reference = ".query"
query = "INSERT INTO radpostauth (username, pass, reply,
authdate ) VALUES ( '%{SQL-User-Name}',
'%{%{User-Password}:-%{Chap-Password}}', '%{reply:Packet-Type}', '%S.%M' )"
}
}
rlm_sql (sql): Driver rlm_sql_mysql (module rlm_sql_mysql) loaded and linked
.............................................................................
Ready to process requests
Received DHCP-Request of Id 155a2327 from 10.255.177.9:68 to
10.255.249.194:67
DHCP-Opcode = Client-Message
DHCP-Hardware-Type = Ethernet
DHCP-Hardware-Address-Length = 6
DHCP-Hop-Count = 1
DHCP-Transaction-Id = 358228775
DHCP-Number-of-Seconds = 0
DHCP-Flags = 0
DHCP-Client-IP-Address = 10.5.12.205
DHCP-Your-IP-Address = 0.0.0.0
DHCP-Server-IP-Address = 0.0.0.0
DHCP-Gateway-IP-Address = 10.255.177.9
DHCP-Client-Hardware-Address = 00:1a:79:44:ed:0f
DHCP-Message-Type = DHCP-Request
DHCP-Client-Identifier = 0x01001a7944ed0f
DHCP-DHCP-Maximum-Msg-Size = 576
DHCP-Parameter-Request-List = DHCP-Subnet-Mask
DHCP-Parameter-Request-List = DHCP-Router-Address
DHCP-Parameter-Request-List = DHCP-Domain-Name-Server
DHCP-Parameter-Request-List = DHCP-Hostname
DHCP-Parameter-Request-List = DHCP-Domain-Name
DHCP-Parameter-Request-List = DHCP-Broadcast-Address
DHCP-Parameter-Request-List = DHCP-NTP-Servers
DHCP-Parameter-Request-List = DHCP-Vendor
DHCP-Vendor-Class-Identifier = 0x496e666f6d69724d4147333232
DHCP-Relay-Circuit-Id = 0x000400010015
DHCP-Relay-Remote-Id = 0x010e686e2d6b727a7633362d73773036
DHCP-Network-Subnet = 10.255.177.9/32
(0) Received code 1027 Id 358228775 from 10.255.177.9:68 to
10.255.249.194:67 length 310
(0) DHCP-Opcode = Client-Message
(0) DHCP-Hardware-Type = Ethernet
(0) DHCP-Hardware-Address-Length = 6
(0) DHCP-Hop-Count = 1
(0) DHCP-Transaction-Id = 358228775
(0) DHCP-Number-of-Seconds = 0
(0) DHCP-Flags = 0
(0) DHCP-Client-IP-Address = 10.5.12.205
(0) DHCP-Your-IP-Address = 0.0.0.0
(0) DHCP-Server-IP-Address = 0.0.0.0
(0) DHCP-Gateway-IP-Address = 10.255.177.9
(0) DHCP-Client-Hardware-Address = 00:1a:79:44:ed:0f
(0) DHCP-Message-Type = DHCP-Request
(0) DHCP-Client-Identifier = 0x01001a7944ed0f
(0) DHCP-DHCP-Maximum-Msg-Size = 576
(0) DHCP-Parameter-Request-List = DHCP-Subnet-Mask
(0) DHCP-Parameter-Request-List = DHCP-Router-Address
(0) DHCP-Parameter-Request-List = DHCP-Domain-Name-Server
(0) DHCP-Parameter-Request-List = DHCP-Hostname
(0) DHCP-Parameter-Request-List = DHCP-Domain-Name
(0) DHCP-Parameter-Request-List = DHCP-Broadcast-Address
(0) DHCP-Parameter-Request-List = DHCP-NTP-Servers
(0) DHCP-Parameter-Request-List = DHCP-Vendor
(0) DHCP-Vendor-Class-Identifier = 0x496e666f6d69724d4147333232
(0) DHCP-Relay-Circuit-Id = 0x000400010015
(0) DHCP-Relay-Remote-Id = 0x010e686e2d6b727a7633362d73773036
(0) DHCP-Network-Subnet = 10.255.177.9/32
Trying sub-section dhcp DHCP-Request {...}
(0) dhcp DHCP-Request {
(0) sql: EXPAND .query
(0) sql: --> .query
(0) sql: Using query template 'query'
rlm_sql (sql): Reserved connection (0)
(0) sql: EXPAND %{User-Name}
(0) sql: -->
(0) sql: SQL-User-Name set to ''
(0) sql: EXPAND INSERT INTO radpostauth (username, pass, reply, authdate
) VALUES ( '%{SQL-User-Name}', '%{%{User-Password}:-%{Chap-Password}}',
'%{reply:Packet-Type}', '%S.%M' )
(0) sql: --> INSERT INTO radpostauth (username, pass, reply, authdate
) VALUES ( '', '', '0', '2021-12-25 10:40:13.292201' )
(0) sql: Executing query: INSERT INTO radpostauth (username, pass,
reply, authdate ) VALUES ( '', '', '0', '2021-12-25 10:40:13.292201' )
(0) sql: SQL query returned: success
(0) sql: 1 record(s) updated
rlm_sql (sql): Released connection (0)
-----------------------------------------------------------------------------------------------------------------------------------------
More information about the Freeradius-Users
mailing list