I wanted to integrate SQL support direct into dhcpd for various reasons, so I wrote a patch. This integrates ISC dhcpd 4.2.0 with libdbi, and thus into MySQL, postgres, and any other SQL database supported by libdbi. This allows dhcpd to dynamically reference an SQL database based on hardware address.
Note that in general doing SQL lookups from dhcpd is a bad idea. The sort of environment where this is useful is the sort of environment where the LDAP patch is useful, where there is no LDAP around but there is an SQL database. An arbitrary SQL query can be specified, which can return anything that can be put in a ‘host {...}‘ block.
The patch, against dhcpd 4.2.0 release, can be found here (that link was updated on 2 Dec 2010 – the old version is here).
The patch was heavily inspired by the LDAP patch – thank you ISC, Ntelos Inc, Brian Masney & David Cantrell.
So far the patch has had very light testing indeed. Do not use it in a production environment. It should (unlike, I think, the LDAP patch) work fine with IPv6, though I’ve only tested it on IPv4.
To compile:
- Apply the patch here to the unpacked ISC dhcp source tree.
- Regenerate the configure script (requires GNU autoconf and automake):
12345aclocallibtoolize —copy —forceautoconfautoheaderautomake —foreign —add–missing —copy - Run ‘./configure‘ with the ‘--with-dbi‘ argument to enable DBI.
- Run ‘make‘ to build ISC dhcp.
Documentation is in the patch, but I have provided an outline below.
My main purpose in posting this here (and similarly to dhcp-users) is to see whether it is useful to anyone other than me. I don’t know much about the innards of dhcpd so I have probably mucked up the memory management (though this was in general taken from ldap.c).
This file provides a generalised interface to libdbi. The following configuration elements can be set (all are strings):
|
1 2 3 4 5 6 |
dbi–host: host on which database resides dbi–driver: name of driver (e.g. mysql) dbi–username: database username dbi–password: database password dbi–dbname: name of database dbi–query: database query |
The file allows dynamic configuration of dhcp parameters looked up by hardware address. The user can specify a statement (likely to be SELECT in an SQL environment) which returns data providing the dhcp paramaters
associated with that particular hardware address.
The query is the SELECT statement passed to the SQL backend, into which the following are substituted:
|
1 2 3 |
%h : the hardware address %t : the media type %% : a percent sign |
The query does not need a trailing semicolon. Be careful that quotes in the query do not interfere with quotes in the config file.
Columns returned are processed in order, with column names corresponding to entries in the file. The values in the first row are appended after a ” ” and terminated with a “;”.
For example, if the table ‘dhcp’ contained columns haddr, ipv4, droute corresponding to hardware address, IPv4 address and default routes for IPv4, the following query might be used:
|
1 2 |
SELECT ipv4 AS ‘fixed-address’, droute AS ‘option routers’ FROM dhcp WHERE haddr = ‘%h’ |
If ipv4 were returned as 192.200.0.15, and droute as 192.200.0.1, this
would generate a host block equivalent to:
|
1 2 3 4 |
host dummyhostname { fixed–address 192.200.0.15; option routers 192.200.0.1; }; |
The following column names have special significance:
|
1 2 3 |
name: the name of the host block (should be irrelevant) entry: an string to be copied verbatim into the configuration (including the relevant semi–colon) |
An example of the use of entry on MySQL is as follows, and has the same effect as the above example:
|
1 2 3 |
SELECT CONCAT(‘fixed-address ‘,ipv4,‘; ‘, ‘option routers ‘,droute,‘;’) AS ‘entry’ FROM dhcp WHERE haddr = ‘%h’ |
This allows for more flexible (if less legible) queries, and avoids problems with databases which are unhappy returning column names containing non-alphanumeric characters.
Leave a Reply to Richard PijnenburgCancel reply