DHCP. Several dhcp attributes from the sql table
Hi, On freeradius version 2.1.12 I could do the following dhcp DHCP-Request { ............ dhcpstatic .......... } where dhcpstatic is the sql module sql dhcpstaticnd { database = "mysql" driver = "rlm_sql _ $ {database}" ............................... authorize_check_query = .......... authorize_reply_query = "SELECT id, 'dhcp' as UserName, attribute, value, op FROM $ {authreply_table} dh WHERE RemoteID = BINARY '% {DHCP-Relay-Remote-Id}' and upper (CircuitID) = upper ('% {DHCP-Relay-Circuit-Id}') and MacAddr = BINARY '% {DHCP-Client-Hardware-Address}' ORDER BY LeaseDate desc " ........................................... } Several dhcp attributes were fetched from the authreply_table, not just DHCP-Your-IP-Address This configuration does not work on freeradius version 3.0.25 How can I select several dhcp attributes from the sql table and send them to DHCP-ACK? -- Sergey Kodentsev
On Dec 24, 2021, at 6:53 AM, Sergey Kodentsev <sergk@ic.vrn.ru> wrote:
On freeradius version 2.1.12 I could do the following ...
Several dhcp attributes were fetched from the authreply_table, not just DHCP-Your-IP-Address
This configuration does not work on freeradius version 3.0.25
Yes, it does. The SQL module still exists, and is fully documented.
How can I select several dhcp attributes from the sql table and send them to DHCP-ACK?
Read the rlm_sql documentation and follow it's examples. Oh, and post some sample *contents* of the SQL table you're looking at. And maybe even *debug output*. If your question contains as little detail as possible, then the answers will have as little detail as possible. Alan DeKok.
Hi,
/How can I select several dhcp attributes from the sql table and send />>/them to DHCP-ACK? / Read the rlm_sql documentation and follow it's examples.
Oh, and post some sample *contents* of the SQL table you're looking at. And maybe even *debug output*.
If your question contains as little detail as possible, then the answers will have as little detail as possible
More detailed information Sites DHCP -------------------------------------------------------------------------------------------------------------- dhcp DHCP-Discover { update control { Prefix := 'Request' } dhcplog { fail = 1 } update request { User-Name = 'dhcp' } dhcpstaticnd { fail = 1 } if (notfound || noop) { dhcpdynnd { fail = 1 } } if (ok) { dhcproute { fail = 1 } } if (ok) { update reply { DHCP-Message-Type = DHCP-Offer DHCP-Domain-Name-Server = 195.98.64.65 DHCP-Domain-Name-Server = 195.98.64.66 DHCP-NTP-Servers = 195.98.64.84 DHCP-Relay-Remote-Id = "%{request:DHCP-Relay-Remote-Id}" DHCP-Relay-Circuit-Id = "%{DHCP-Relay-Circuit-Id}" DHCP-DHCP-Server-Identifier = "%{Packet-Dst-IP-Address}" } #<-----> if (request:DHCP-Parameter-Request-List:DHCP-Classless-Static-Route && (request:DHCP-Parameter-Request-List:DHCP-Router-Address || request:DHCP-Parameter-Request-List:DHCP-Static-Routes)) { #<----->update reply { #<-----> DHCP-Router-Address !* 192.168.1.1 #<----->} #<-----> } update control { Prefix := 'Answer' } <--> dhcplog { fail = 1 } } else { if (DHCP-Hop-Count > 1 && notfound) { update reply { DHCP-Message-Type := DHCP-Nak } } else { update reply { DHCP-Message-Type !* DHCP-Nak } } reject } } --------------------------------------------------------------------------------------- Mods sql sql dhcpstaticnd { database = "mysql" driver = "rlm_sql_${database}" server = "localhost" #port = 3306 login = "raduser" password = "*********" read_groups = no read_profiles = yes read_clients = no do_clients = no query_user="dhcp" radius_db = "raddbdhcp" authreply_table = "dhcpip" # Remove stale session if checkrad does not see a double login delete_stale_sessions = no # Print all SQL statements when in debug mode (-x) sqltrace = no sqltracefile = ${logdir}/sqltrace.sql # number of sql connections to make to server num_sql_socks = 50 connect_failure_retry_delay = 60 # closed "lifetime" seconds after they were first opened. lifetime = 0 max_queries = 0 safe_characters = "@abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789.-_: /" sql_user_name = "dhcp" # Use these for case sensitive usernames. authorize_check_query = "SELECT id, 'dhcp' as UserName, 'Connect-Rate', IPint, ':=' \ FROM ${authreply_table} dh \ WHERE RemoteID = BINARY '%{DHCP-Relay-Remote-Id}' \ and upper(CircuitID) = upper('%{DHCP-Relay-Circuit-Id}') \ and MacAddr = BINARY '%{DHCP-Client-Hardware-Address}' \ ORDER BY LeaseDate desc" authorize_reply_query = "SELECT id,'dhcp' as UserName, attribute, value, op \ FROM ${authreply_table} dh \ WHERE RemoteID = BINARY '%{DHCP-Relay-Remote-Id}' \ and upper(CircuitID) = upper('%{DHCP-Relay-Circuit-Id}') \ and MacAddr = BINARY '%{DHCP-Client-Hardware-Address}' \ ORDER BY LeaseDate desc" } ---------------------------------------------------------------------------------- DEBUG Received DHCP-Discover of Id 8fd33637 from 10.255.116.231: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 = 2412983863 DHCP-Number-of-Seconds = 0 DHCP-Flags = 0 DHCP-Client-IP-Address = 0.0.0.0 DHCP-Your-IP-Address = 0.0.0.0 DHCP-Server-IP-Address = 0.0.0.0 DHCP-Gateway-IP-Address = 10.255.116.231 DHCP-Client-Hardware-Address = 04:bf:6d:97:dd:ed DHCP-Message-Type = DHCP-Discover DHCP-DHCP-Maximum-Msg-Size = 1500 DHCP-Client-Identifier = 0x0104bf6d97dded DHCP-Vendor-Class-Identifier = 0x6e6468637063 DHCP-Hostname = "Keenetic_Start" 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-Domain-Name DHCP-Parameter-Request-List = DHCP-Broadcast-Address DHCP-Parameter-Request-List = DHCP-Static-Routes DHCP-Parameter-Request-List = DHCP-NTP-Servers DHCP-Parameter-Request-List = DHCP-Vendor DHCP-Parameter-Request-List = DHCP-NETBIOS-Name-Servers DHCP-Parameter-Request-List = DHCP-Classless-Static-Route DHCP-Parameter-Request-List = 249 DHCP-Relay-Circuit-Id = 0x000400010002 DHCP-Relay-Remote-Id = 0x0113686e2d6368656c313031762d737730362d3137 (5) Received code 1025 Id 2412983863 from 10.255.116.231:68 to 10.255.249.194:67 length 327 (5) DHCP-Opcode = Client-Message (5) DHCP-Hardware-Type = Ethernet (5) DHCP-Hardware-Address-Length = 6 (5) DHCP-Hop-Count = 1 (5) DHCP-Transaction-Id = 2412983863 (5) DHCP-Number-of-Seconds = 0 (5) DHCP-Flags = 0 (5) DHCP-Client-IP-Address = 0.0.0.0 (5) DHCP-Your-IP-Address = 0.0.0.0 (5) DHCP-Server-IP-Address = 0.0.0.0 (5) DHCP-Gateway-IP-Address = 10.255.116.231 (5) DHCP-Client-Hardware-Address = 04:bf:6d:97:dd:ed (5) DHCP-Message-Type = DHCP-Discover (5) DHCP-DHCP-Maximum-Msg-Size = 1500 (5) DHCP-Client-Identifier = 0x0104bf6d97dded (5) DHCP-Vendor-Class-Identifier = 0x6e6468637063 (5) DHCP-Hostname = "Keenetic_Start" (5) DHCP-Parameter-Request-List = DHCP-Subnet-Mask (5) DHCP-Parameter-Request-List = DHCP-Router-Address (5) DHCP-Parameter-Request-List = DHCP-Domain-Name-Server (5) DHCP-Parameter-Request-List = DHCP-Domain-Name (5) DHCP-Parameter-Request-List = DHCP-Broadcast-Address (5) DHCP-Parameter-Request-List = DHCP-Static-Routes (5) DHCP-Parameter-Request-List = DHCP-NTP-Servers (5) DHCP-Parameter-Request-List = DHCP-Vendor (5) DHCP-Parameter-Request-List = DHCP-NETBIOS-Name-Servers (5) DHCP-Parameter-Request-List = DHCP-Classless-Static-Route (5) DHCP-Parameter-Request-List = 249 (5) DHCP-Relay-Circuit-Id = 0x000400010002 (5) DHCP-Relay-Remote-Id = 0x0113686e2d6368656c313031762d737730362d3137 Trying sub-section dhcp DHCP-Discover {...} (5) dhcp DHCP-Discover { (5) update control { (5) Prefix := 'Request' (5) } # update control = noop (5) dhcplog: EXPAND .query (5) dhcplog: --> .query (5) dhcplog: Using query template 'query' rlm_sql (dhcplog): Reserved connection (4) (5) dhcplog: EXPAND %{User-Name} (5) dhcplog: --> (5) dhcplog: SQL-User-Name set to '' (5) dhcplog: EXPAND INSERT INTO dhcplog (LogDate,Seq,DHCPTypeIn,DHCPTypeOut,DHCPGateway,RemoteID,CircuitID,MacAddr,IpAddr, GwAddr) VALUES (sysdate(), '%{control:Prefix}', '%{request:DHCP-Message-Type}', '%{reply:DHCP-Message-Type}', '%{request:DHCP-Gateway-IP-Address}', '%{request:DHCP-Relay-Remote-Id}','%{request:DHCP-Relay-Circuit-Id}', '%{request:DHCP-Client-Hardware-Address}','%{reply:DHCP-Your-IP-Address}', '%{reply:DHCP-Router-Address}') (5) dhcplog: --> INSERT INTO dhcplog (LogDate,Seq,DHCPTypeIn,DHCPTypeOut,DHCPGateway,RemoteID,CircuitID,MacAddr,IpAddr, GwAddr) VALUES (sysdate(), 'Request', 'DHCP-Discover', '', '10.255.116.231', '0x0113686e2d6368656c313031762d737730362d3137','0x000400010002', '04:bf:6d:97:dd:ed','', '') (5) dhcplog: Executing query: INSERT INTO dhcplog (LogDate,Seq,DHCPTypeIn,DHCPTypeOut,DHCPGateway,RemoteID,CircuitID,MacAddr,IpAddr, GwAddr) VALUES (sysdate(), 'Request', 'DHCP-Discover', '', '10.255.116.231', '0x0113686e2d6368656c313031762d737730362d3137','0x000400010002', '04:bf:6d:97:dd:ed','', '') (5) dhcplog: SQL query returned: success (5) dhcplog: 1 record(s) updated rlm_sql (dhcplog): Released connection (4) (5) [dhcplog] = ok (5) update request { (5) User-Name = 'dhcp' (5) } # update request = noop (5) dhcpstaticnd: EXPAND .query (5) dhcpstaticnd: --> .query (5) dhcpstaticnd: WARNING: No such configuration item .query (5) [dhcpstaticnd] = noop (5) if (notfound || noop) { (5) if (notfound || noop) -> TRUE (5) if (notfound || noop) { (5) dhcpdynnd: EXPAND .query (5) dhcpdynnd: --> .query (5) dhcpdynnd: WARNING: No such configuration item .query (5) [dhcpdynnd] = noop (5) } # if (notfound || noop) = noop (5) if (ok) { (5) if (ok) -> FALSE (5) if (ok) { (5) if (ok) -> FALSE (5) else { (5) if (DHCP-Hop-Count > 1 && notfound) { (5) if (DHCP-Hop-Count > 1 && notfound) -> FALSE (5) else { (5) update reply { (5) DHCP-Message-Type !* ANY (5) } # update reply = noop (5) } # else = noop (5) [reject] = reject (5) } # else = reject (5) } # dhcp DHCP-Discover = reject (5) Not sending reply to client. (5) Finished request (5) Cleaning up request packet ID 2412983863 with timestamp +0 Ready to process requests -- Sergey Kodentsev
On Dec 24, 2021, at 10:39 AM, Sergey Kodentsev <sergk@ic.vrn.ru> wrote:
More detailed information
Sites DHCP
All of the documentation says "don't post the configuration files, it doesn't help". Plus, I didn't ask for the configuration files. Because they don't help.
update reply { DHCP-Message-Type = DHCP-Offer DHCP-Domain-Name-Server = 195.98.64.65 DHCP-Domain-Name-Server = 195.98.64.66
That's not going to do what you want. Read "man unlang", to see what the individual operators do. Hint: see "+=".
Mods sql
Which you butchered completely. Why?
(5) dhcpstaticnd: EXPAND .query (5) dhcpstaticnd: --> .query (5) dhcpstaticnd: WARNING: No such configuration item .query
Perhaps this is a hint that something is going wrong? You edited the configuration files and broke them. Why?
... (5) dhcpdynnd: EXPAND .query (5) dhcpdynnd: --> .query (5) dhcpdynnd: WARNING: No such configuration item .query
Same thing here If you just read the debug output, you would see immediately why things don't work. It looks like you've just copied bits of your v2 configuration to v3. That won't work. 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. Alan DeKok.
participants (2)
-
Alan DeKok -
Sergey Kodentsev