[Kea-users] PostgreSQL lease management

[James Sumners]

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.


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.


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.

________________________________
From: Kea-users <kea-users-bounces at lists.isc.org> on behalf of Tomek Mrugalski <tomasz at isc.org>
Sent: Wednesday, February 1, 2017 5:39:04 PM
To: kea-users at lists.isc.org
Subject: Re: [Kea-users] PostgreSQL lease management

W dniu 01.02.2017 o 22:09, James Sumners pisze:
> Is there a document that describes how the leases database is managed
> when it is stored in PostgreSQL? In particular, I want to look at the
> queries that are involved, so something like [1] would be great.
>
> I want to devise a trigger to archive leases to another table when they
> are being reaped.
>
> [1] —
> http://kea.isc.org/wiki/HostReservationsHowTo#QueriesUsedbytheKeaServer
Not in a such easy format to read, but the information is there.

The schema itself is available in
src/share/database/scripts/pgsql/dhcpdb_create.pgsql.

The actual queries Kea code uses are in 2 files:

src/lib/dhcpsrv/pgsql_lease_mgr.cc (for leases)
src/lib/dhcpsrv/pgsql_host_data_source.cc (for host reservations)

It's a C++ code, but SQL queries are there in plain text, just search
for "tagged_statements". One way to browse those files would be our
github repo: https://github.com/isc-projects/kea/

On one hand writing such a document is useful, but on the other hand
there's the danger of it being outdated without anyone noticing.

Hope that helps,
Tomek

_______________________________________________
Kea-users mailing list
Kea-users at lists.isc.org
https://lists.isc.org/mailman/listinfo/kea-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.isc.org/pipermail/kea-users/attachments/20170202/d3219552/attachment.htm>