[Kea-users] PostgreSQL lease management

[Tomek Mrugalski]

W dniu 02.02.2017 o 03:21, James Sumners pisze:
> Okay, so it's just simple `delete` statements. Looking at the schema,
> I'm going to have to alter the `lease4` table to add a column named
> "created" of type `timestamptz` with the default value for inserts set
> to `now()`. Please consider adding this column in 1.2. Without it,
> keeping an audit trail will be basically impossible.
There are couple points your proposal does not cover as explained below.
Since the solution you proposed overlaps functionality with what we
already have in forensic logging lib, it's unlikely we will merge it,
but I will discuss this with the team.  One additional reason against it
(at least in the form you described) is that it would keep growing
without bounds. That may be a problem for deployments that do not need
this kind of information (or even are unaware of its existence).

> Scenario: we have to process DMCA requests. When that happens, we have
> to look back at our DHCP logs to see which devices would have had the
> targeted IP within the timeframe given in the request.
Understood. In this case you may be interested in forensic logging
library. http://kea.isc.org/docs/kea-guide.html#idp54623200
Depending on what your requirements are, it may be a better solution.
Couple points to consider: leases are being updated, so it's not only
the initial creation time that may be of interest, but also how long it
was used, when it was renewed and released.

Also, forensic logging lib logs additional information that's not
available in the leases database - all the information related to
client's point of attachment (relay address, remote-id etc.). The
separate log file produced is a proof of activity of a device over time,
from Kea perspective it's write only.

Finally, keep in mind that your trigger will not cover all cases that
the forensic library will catch. Kea usually assigns new leases, but
sometimes it reuses existing leases that have just expired but were not
removed yet. From the DB perspective, the lease will be updated, but the
update will change the client information completely.

> So what I'm going to do is add an `after delete on lease4 for each row
> execute procedure archive_lease()` trigger that will merely copy the
> deleted data over to an archive table. The archive table may have an
> addition "archived" `timestamptz` column.
Thanks for sharing.

Tomek