DHCP. Several dhcp attributes from the sql table
Sergey Kodentsev
sergk at ic.vrn.ru
Fri Dec 24 16:39:58 CET 2021
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
More information about the Freeradius-Users
mailing list