<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style>
body {
font-family: "Helvetica Neue", Helvetica, Arial, sans-serif;
padding:1em;
margin:auto;
background:#fefefe;
}
h1, h2, h3, h4, h5, h6 {
font-weight: bold;
}
h1 {
color: #000000;
font-size: 28pt;
}
h2 {
border-bottom: 1px solid #CCCCCC;
color: #000000;
font-size: 24px;
}
h3 {
font-size: 18px;
}
h4 {
font-size: 16px;
}
h5 {
font-size: 14px;
}
h6 {
color: #777777;
background-color: inherit;
font-size: 14px;
}
hr {
height: 0.2em;
border: 0;
color: #CCCCCC;
background-color: #CCCCCC;
display: inherit;
}
p, blockquote, ul, ol, dl, li, table, pre {
margin: 15px 0;
}
a, a:visited {
color: #4183C4;
background-color: inherit;
text-decoration: none;
}
#message {
border-radius: 6px;
border: 1px solid #ccc;
display:block;
width:100%;
height:60px;
margin:6px 0px;
}
button, #ws {
font-size: 12 pt;
padding: 4px 6px;
border-radius: 5px;
border: 1px solid #bbb;
background-color: #eee;
}
code, pre, #ws, #message {
font-family: Monaco;
font-size: 10pt;
border-radius: 3px;
background-color: #F8F8F8;
color: inherit;
}
code {
border: 1px solid #EAEAEA;
margin: 0 2px;
padding: 0 5px;
}
pre {
border: 1px solid #CCCCCC;
overflow: auto;
padding: 4px 8px;
}
pre > code {
border: 0;
margin: 0;
padding: 0;
}
#ws { background-color: #f8f8f8; }
.bloop_markdown table {
border-collapse: collapse;
font-family: Helvetica, arial, freesans, clean, sans-serif;
color: rgb(51, 51, 51);
font-size: 15px; line-height: 25px;
padding: 0; }
.bloop_markdown table tr {
border-top: 1px solid #cccccc;
background-color: white;
margin: 0;
padding: 0; }
.bloop_markdown table tr:nth-child(2n) {
background-color: #f8f8f8; }
.bloop_markdown table tr th {
font-weight: bold;
border: 1px solid #cccccc;
margin: 0;
padding: 6px 13px; }
.bloop_markdown table tr td {
border: 1px solid #cccccc;
margin: 0;
padding: 6px 13px; }
.bloop_markdown table tr th :first-child, table tr td :first-child {
margin-top: 0; }
.bloop_markdown table tr th :last-child, table tr td :last-child {
margin-bottom: 0; }
.bloop_markdown blockquote{
border-left: 4px solid #dddddd;
padding: 0 15px;
color: #777777; }
blockquote > :first-child {
margin-top: 0; }
blockquote > :last-child {
margin-bottom: 0; }
code, pre, #ws, #message {
word-break: normal;
word-wrap: normal;
}
hr {
display: inherit;
}
.bloop_markdown :first-child {
-webkit-margin-before: 0;
}
code, pre, #ws, #message {
font-family: Menlo, Consolas, Liberation Mono, Courier, monospace;
}
.send { color:#77bb77; }
.server { color:#7799bb; }
.error { color:#AA0000; }</style>
</head>
<body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div class="bloop_markdown">
<p>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
<em>all</em> information about leases was stored in the database. And I would really like to see the native types (e.g.
<code>inet</code> and <code>macaddr</code>) used in the PostgreSQL database.</p>
<p>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
<code>client_id</code> 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.</p>
<p>Of course, it would be necessary to clean up the <code>lease4_audit</code> table on occasion.</p>
<pre><code>-- 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();
</code></pre>
<p></p>
</div>
<div class="bloop_original_html"><style>body{font-family:Helvetica,Arial;font-size:13px}</style>
<div id="bloop_customfont" style="font-family:Helvetica,Arial;font-size:13px; color: rgba(0,0,0,1.0); margin: 0px; line-height: auto;">
<br>
</div>
<br>
<div id="bloop_sign_1486048419333036032" class="bloop_sign"></div>
<div class="airmail_ext_on" style="color:black"><br>
From: <span style="color:black">Tomek Mrugalski</span> <a href="mailto:tomasz@isc.org">
<tomasz@isc.org></a><br>
Date: <span style="color:black">February 2, 2017 at 6:06:08 AM</span><br>
To: <span style="color:black">James Sumners</span> <a href="mailto:jamessumners@clayton.edu">
<jamessumners@clayton.edu></a>, <span style="color:black">kea-users@lists.isc.org</span>
<a href="mailto:kea-users@lists.isc.org"><kea-users@lists.isc.org></a><br>
Subject: <span style="color:black"> Re: [Kea-users] PostgreSQL lease management <br>
</span></div>
<br>
<blockquote type="cite" class="clean_bq"><span>
<div>
<div></div>
<div>W dniu 02.02.2017 o 03:21, James Sumners pisze: <br>
> Okay, so it's just simple `delete` statements. Looking at the schema, <br>
> I'm going to have to alter the `lease4` table to add a column named <br>
> "created" of type `timestamptz` with the default value for inserts set <br>
> to `now()`. Please consider adding this column in 1.2. Without it, <br>
> keeping an audit trail will be basically impossible. <br>
There are couple points your proposal does not cover as explained below. <br>
Since the solution you proposed overlaps functionality with what we <br>
already have in forensic logging lib, it's unlikely we will merge it, <br>
but I will discuss this with the team. One additional reason against it <br>
(at least in the form you described) is that it would keep growing <br>
without bounds. That may be a problem for deployments that do not need <br>
this kind of information (or even are unaware of its existence). <br>
<br>
> Scenario: we have to process DMCA requests. When that happens, we have <br>
> to look back at our DHCP logs to see which devices would have had the <br>
> targeted IP within the timeframe given in the request. <br>
Understood. In this case you may be interested in forensic logging <br>
library. http://kea.isc.org/docs/kea-guide.html#idp54623200 <br>
Depending on what your requirements are, it may be a better solution. <br>
Couple points to consider: leases are being updated, so it's not only <br>
the initial creation time that may be of interest, but also how long it <br>
was used, when it was renewed and released. <br>
<br>
Also, forensic logging lib logs additional information that's not <br>
available in the leases database - all the information related to <br>
client's point of attachment (relay address, remote-id etc.). The <br>
separate log file produced is a proof of activity of a device over time, <br>
from Kea perspective it's write only. <br>
<br>
Finally, keep in mind that your trigger will not cover all cases that <br>
the forensic library will catch. Kea usually assigns new leases, but <br>
sometimes it reuses existing leases that have just expired but were not <br>
removed yet. From the DB perspective, the lease will be updated, but the <br>
update will change the client information completely. <br>
<br>
> So what I'm going to do is add an `after delete on lease4 for each row <br>
> execute procedure archive_lease()` trigger that will merely copy the <br>
> deleted data over to an archive table. The archive table may have an <br>
> addition "archived" `timestamptz` column. <br>
Thanks for sharing. <br>
<br>
Tomek <br>
<br>
</div>
</div>
</span></blockquote>
</div>
<div class="bloop_markdown">
<p></p>
</div>
</body>
</html>