DHCP. Several dhcp attributes from the sql table
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) -----------------------------------------------------------------------------------------------------------------------------------------
On Dec 25, 2021, at 4:45 AM, Sergey Kodentsev <sergk@ic.vrn.ru> wrote:
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
i.e. you didn't read what I wrote, and you're not going to look at the file which contains the queries.
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.
Yes. Exactly. You added random things to the SQL module configuration, and are surprised that they don't magically do what you want. Perhaps you should read the SQL module configuration, or the debug output to see where the queries are now located. The file which contains the queries also has complete documentation on how they work. You can edit those queries. If you're going to ignore my suggestions and all of the documentation, then why are you asking questions here? Alan DeKok.
participants (2)
-
Alan DeKok -
Sergey Kodentsev