[Kea-users] PostgreSQL lease management

[James Sumners]

Thank you for clarifying some of how the database is used for lease storage. For me, and I’m sure for a lot of people, the biggest attraction to Kea is being able to back it with a proper database. I wish all information about leases was stored in the database. And I would really like to see the native types (e.g. inet and macaddr) used in the PostgreSQL database.

Below is what I have come up with so far. Maybe you can offer suggestions for improvement? In particular, I’d like to see the client_id stored as a human readable value. But I don’t know the details of that field to be able to come up with the necessary conversion.

Of course, it would be necessary to clean up the lease4_audit table on occasion.

-- the extension must be installed by a super user
-- if desired, remove `schema public` and execute while connected to target db
create extension if not exists "uuid-ossp" schema public;

create table if not exists lease4_audit (
  id uuid primary key default uuid_generate_v4(),
  action text not null,
  address inet not null,
  hwaddr macaddr not null,
  client_id bytea,
  valid_lifetime bigint,
  expire timestamptz,
  subnet_id bigint,
  hostname text,
  created_at timestamptz,
  updated_at timestamptz,
  deleted_at timestamptz
);

create index lease4_audit_addr on lease4_audit (address);
create index lease4_audit_hwad on lease4_audit (hwaddr);

create or replace function inet4_aton(ip_as_num bigint) returns text as $$
-- http://sqlblog.com/blogs/denis_gobo/archive/2008/10/05/9266.aspx
declare
  octet1 bigint;
  octet2 bigint;
  octet3 bigint;
  octet4 bigint;
  remainder bigint;
begin
  octet1 := ip_as_num / 16777216;
  remainder := ip_as_num - (octet1 * 16777216);

  octet2 := remainder / 65536;
  remainder := remainder - (octet2 * 65536);

  octet3 := remainder / 256;
  octet4 := remainder - (octet3 * 256);

  return octet1::text || '.' || octet2::text || '.' || octet3::text || '.' || octet4::text;
end;
$$ language plpgsql;

create or replace function lease4_auditor() returns trigger as $lease4_auditor$
begin
  if (TG_OP = 'INSERT') then
    insert into lease4_audit (action, address, hwaddr, client_id, valid_lifetime, expire, subnet_id, hostname, created_at) values (
      'reserve',
      inet4_aton(NEW.address)::inet,
      encode(NEW.hwaddr, 'hex')::macaddr,
      NEW.client_id,
      NEW.valid_lifetime,
      NEW.expire,
      NEW.subnet_id,
      NEW.hostname,
      now()
    );
    return NEW;
  elsif (TG_OP = 'UPDATE') then
    insert into lease4_audit (action, address, hwaddr, client_id, valid_lifetime, expire, subnet_id, hostname, updated_at) values (
      'renew',
      inet4_aton(NEW.address)::inet,
      encode(NEW.hwaddr, 'hex')::macaddr,
      NEW.client_id,
      NEW.valid_lifetime,
      NEW.expire,
      NEW.subnet_id,
      NEW.hostname,
      now()
    );
    return NEW;
  elsif (TG_OP = 'DELETE') then
    insert into lease4_audit (action, address, hwaddr, client_id, valid_lifetime, expire, subnet_id, hostname, deleted_at) values (
      'release',
      inet4_aton(OLD.address)::inet,
      encode(OLD.hwaddr, 'hex')::macaddr,
      OLD.client_id,
      OLD.valid_lifetime,
      OLD.expire,
      OLD.subnet_id,
      OLD.hostname,
      now()
    );
    return OLD;
  end if;

  return null;
end;
$lease4_auditor$ language plpgsql;

create trigger lease4_audit_trig
  after insert or update or delete
  on lease4_audit
  for each row execute procedure lease4_auditor();




From: Tomek Mrugalski <tomasz at isc.org><mailto:tomasz at isc.org>
Date: February 2, 2017 at 6:06:08 AM
To: James Sumners <jamessumners at clayton.edu><mailto:jamessumners at clayton.edu>, kea-users at lists.isc.org <kea-users at lists.isc.org><mailto:kea-users at lists.isc.org>
Subject:  Re: [Kea-users] PostgreSQL lease management

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.isc.org/pipermail/kea-users/attachments/20170202/169de9d7/attachment.htm>