Dnsmasq Lease Database

DNSMASQ-database.md

This is now a part of amigus.dnsmasq.

Dnsmasq Lease Database

A dhcp-script for Dnsmasq that makes it manage DHCP lease information in an SQLite database.

What is it?

Dnsmasq has a dhcp-script configuration parameter. When set, Dnsmasq invokes the script whenever DHCP lease information changes. When combined with leasefile-ro it fully replaces the leases file.

The script tracks leases in an SQLite database. It also accumulates client information and requests.

Why do it?

The leases file that Dnsmasq uses by default is a point-in-time snapshot. Dnsmasq adds and removes from the file. No long-term record is kept. Using log-dhcp helps, but the log files do not make the data easy to use. However, by storing the requests in a database and associating them with client and lease information, we can keep a complete history of DHCP usage.

How?

On the host running Dnsmasq:

1. Install SQLite and use it to create a database using the schema below.
2. Save the script to a bin directory, e.g., /usr/local/sbin.
3. Configure Dnsmasq to use the script on the database.

Or download and run the install.sh (below) on your DHCP server and run it:

curl -sLo - https://gist.githubusercontent.com/amigus/6a9e4151d175d04bf05337b815f2213e/raw/install.sh
 | sh

Schema

The schema stores requests, leases, and client information. The script adds to and deletes from the leases table as required to maintain it, as it does the leases file by default. Nothing is deleted from requests so that it can grow significantly over time. Nothing is deleted from clients either; however, the primary key is the ethernet address, so the table never grows big enough to cause an issue.

sqlite3 /var/lib/misc/dnsmasq.leases.db /path/to/database.sql
chown dnsmasq:dnsmasq /var/lib/misc/dnsmasq.leases.db
chgrp dnsmasq /var/lib/misc
chmod g+w /var/lib/misc

Script

Dnsmasq passes information to the script via command-line arguments and environment variables. The script converts the information into SQL statements compatible with the schema. It uses the sqlite3 command to execute the statements as a single transaction on the SQLite database.

The script supports the init argument using the dnsmasq VIEW. Dnsmasq invokes the script with init when it starts instead of reading the leases file.

install -m 550 -o root -g dnsmasq /path/to/dnsmasq-dhcp.sh /usr/local/sbin

Configuration

Dnsmasq invokes the script in dhcp-script. The script-on-renewal parameter causes the script to store renewal requests in the database. The leasefile-ro option disables the built-in leases file. See the Dnsmasq man page for the details.

dhcp-script=/usr/local/sbin/dnsmasq-dhcp.sh
script-on-renewal
leasefile-ro

Security

Dnsmasq runs the script as root by default but the script does not need root privilege. Add the dhcp-scriptuser configuration parameter to run the script as a non-root user.

dhcp-scriptuser=dnsmasq

Note, however, that the user must have write access to the SQLite database and the parent directory.

dhcp:~# ls -ahl /var/lib/misc
total 29M
drwxrwxr-x    2 root     dnsmasq     4.0K Sep  3 10:07 .
drwxr-xr-x    6 root     root        4.0K Apr  2 14:58 ..
-rw-r--r--    1 dnsmasq  dnsmasq        0 Apr 30 20:31 dnsmasq.leases
-rw-rw----    1 dnsmasq  dnsmasq    28.6M Sep  3 10:07 dnsmasq.leases.db
dhcp:~#

dnsmasq-database.sql

CREATE TABLE IF NOT EXISTS requests (
    received TEXT NOT NULL,
    argument TEXT CHECK (argument IN ('add', 'del', 'old')),
    mac TEXT CHECK (mac REGEXP '^[a-zA-Z0-9]{2}(:[a-zA-Z0-9]{2}){5}$'),
    ipv4 TEXT CHECK (ipv4 REGEXP '^((25[0-5]|(2[0-4]|1\d|[1-9]|)\d)\.?\b){4}$'),
    client_id TEXT,
    requested_options TEXT
);

CREATE INDEX IF NOT EXISTS requests_addresses_index ON requests (mac, ipv4);
CREATE INDEX IF NOT EXISTS requests_received_index ON requests (received);

CREATE TABLE IF NOT EXISTS leases (
    mac TEXT CHECK (mac REGEXP '^[a-zA-Z0-9]{2}(:[a-zA-Z0-9]{2}){5}$'),
    ipv4 TEXT CHECK (ipv4 REGEXP '^((25[0-5]|(2[0-4]|1\d|[1-9]|)\d)\.?\b){4}$'),
    added TEXT,
    renewed TEXT,
    PRIMARY KEY (mac, ipv4)
);

CREATE TABLE IF NOT EXISTS clients (
    mac TEXT PRIMARY KEY CHECK (mac REGEXP '^[a-zA-Z0-9]{2}(:[a-zA-Z0-9]{2}){5}$'),
    hostname TEXT,
    client_id TEXT,
    vendor_class TEXT,
    updated TEXT
);

DROP VIEW IF EXISTS dnsmasq;
CREATE VIEW dnsmasq AS SELECT unixepoch(ifnull(l.renewed, l.added)), l.mac, 
    l.ipv4, c.hostname, ifnull(c.client_id, '*')
        FROM leases AS l join clients AS c ON l.mac = c.mac;

dnsmasq-dhcp.sh

#!/bin/sh

if test -z "$1"; then
    echo "error: missing argument; aborting"
    exit 1
elif test "$1" = 'init'; then
    sqlite3 -separator ' ' '/var/lib/misc/dnsmasq.leases.db' \
        'SELECT * from dnsmasq;'
    exit 0
fi

mac="'$2'"
ipv4="'$3'"
hostname="'$4'"
client_id=null
test -n "$DNSMASQ_CLIENT_ID" && client_id="'$DNSMASQ_CLIENT_ID'"
requested_options=null
test -n "$DNSMASQ_REQUESTED_OPTIONS" && \
    requested_options="'$DNSMASQ_REQUESTED_OPTIONS'"
vendor_class=null
test -n "$DNSMASQ_VENDOR_CLASS" && vendor_class="'$DNSMASQ_VENDOR_CLASS'"
now="'$(date +"%Y-%m-%d %H:%M:%S")'"
sql="BEGIN TRANSACTION;
INSERT INTO requests VALUES (
    $now, '$1', $mac, $ipv4, $client_id, $requested_options)"

if test $1 = "add"; then
    sql="$sql;
        INSERT INTO leases VALUES ($mac, $ipv4, $now, null);
        INSERT OR REPLACE INTO clients VALUES (
            $mac, $hostname, $client_id, $vendor_class, $now)"
elif test $1 = "old"; then
    sql="$sql;
        INSERT INTO leases VALUES ($mac, $ipv4, null, $now)
            ON CONFLICT(mac, ipv4) DO UPDATE SET renewed = $now"
elif test $1 = "del"; then
    sql="$sql;
        DELETE FROM leases WHERE mac = $mac and ipv4 = $ipv4"
fi

sqlite3 '/var/lib/misc/dnsmasq.leases.db' \
    "$sql; COMMIT TRANSACTION;"

install.sh

#!/bin/sh
url='https://gist.github.com/amigus/6a9e4151d175d04bf05337b815f2213e/raw'
apk add sqlite dnsmasq
curl -sL $url/dnsmasq-database.sql -o - | sqlite3 /var/lib/misc/dnsmasq.leases.db
chown dnsmasq:dnsmasq /var/lib/misc/dnsmasq.leases.db
chgrp dnsmasq /var/lib/misc && chmod g+w /var/lib/misc
curl -sL $url/dnsmasq-dhcp.sh -o /usr/local/bin/dnsmasq-dhcp.sh
chmod +x /usr/local/bin/dnsmasq-dhcp.sh
cat >|/etc/dnsmasq.d/dnsmasq-web.conf<<EOF
dhcp-script=/usr/local/bin/dnsmasq-dhcp.sh
dhcp-scriptuser=dnsmasq
leasefile-ro
script-on-renewal
EOF
rc-service dnsmasq restart