[Pdns-users] monitoring changes to zones, frontend recommendations

[Steven Crandell]

If there actually isn't an existing solution out there to capture the info
you're looking for, you could roll your own pretty easily.

You could check your database dump/backup files into svn and configure hooks
to email diffs.
This will definitely work but has some drawbacks:
1. Dump files are often difficult to clearly read (dumping full insert
statements will help with this but the problem still exists in general)
2. Your reporting is only as granular as your backups (e.g. daily backups
might be enough but daily reports on changes might not)
3. There's a decent amount of scripting that still needs to be done to turn
this into a usable report

You could also run ordered selects against records on a domain-by-domain
basis, writing these to disk and checking these files directly into svn.
Then make with the email diff hooks as mentioned above.

I would instead recommend that you create two audit tables, something along
these lines (this is scratch SQL and is in postgresql compliant format,
tune/mysql-ify to taste):

CREATE TABLE domains_changes(
    timestamp  TIMESTAMP NOT NULL DEFAULT now(),
    operation    VARCHAR(10) NOT NULL, --- this would be one of insert,
update, delete
    old_name         VARCHAR(255),
    new_name         VARCHAR(255),
    old_type           VARCHAR(6),
    new_type           VARCHAR(6)
);

CREATE TABLE records_changes(
    timestamp  TIMESTAMP NOT NULL DEFAULT now(),
    operation    VARCHAR(10) NOT NULL,
    old_name   VARCHAR(255),
    new_name  VARCHAR(255),
    old_type     VARCHAR(6),
    new_type    VARCHAR(6),
    old_content VARCHAR(255),
    new_content VARCHAR(255)
    --- etc....
);

Then attach a trigger to the domains table and another to the records table
which updates the appropriate table (domains_changes, records_changes) after
every operation performed on these two tables.  You can then select out of
these *_changes table based on day/hour/etc to see any and all changes
(you're basically done with your reporting at this point also).  You can
also use these tables to step back in time in certain situations.

Most of the time, questions about auditing tend to focus more on making sure
someone doesn't accidentally fat-finger one or more of your core domain
names out of existence than they tend focus on keeping your finger on the
pulse of name services in general.  Knowing that your intern admin brought
down your production web presence is nice but you still went down.  For this
situation I again suggest triggers as the option way to go.  A "pre" trigger
on your records and domains table which does a regex or an id check against
any records being changed and then discards changes made to sensitive
records (perhaps along with an alert email) is pretty simple to construct
and could save you some serious headaches.  In situations where the
sensitive records actually do need to be updated, the trigger can be
temporarily dropped

-s



On Sun, Oct 18, 2009 at 12:55 PM, Xavier Valentino <
xavier.valentino at gmail.com> wrote:

> Hi Nils,
>
> I suspected as much, to be honest. I was just throwing it out there and
> hoping somebody would have a better suggestion; the interface still seemed a
> little more polished than PowerDNSAdmin's which is, at best, minimalistic.
> I'm fine with that, though.
>
> The more pressing matter is that I need to have some accounting/revision
> reporting going on. If somebody adds something to a zone that shouldn't be
> there, or deletes a whole bunch of records on accident, or anything of that
> nature, I'd like to be able to tell what it was so I can reverse it. Any
> suggestions?
>
>
> On Sun, Oct 18, 2009 at 8:38 AM, Nils Breunese (Lemonbit) <
> nils at lemonbit.com> wrote:
>
>> Xavier Valentino wrote:
>>
>>  I also see a PowerDNSAdmin alternative called TUPA; does anyone have any
>>> thoughts on the comparison between the two, or suggestions of another
>>> web-based frontend (more) suitable for users?
>>>
>>
>> The latest news item on http://www.tupa-dns.org/ is dated April 3 2006.
>> Looks like a dead project to me.
>>
>> Nils.
>> _______________________________________________
>> Pdns-users mailing list
>> Pdns-users at mailman.powerdns.com
>> http://mailman.powerdns.com/mailman/listinfo/pdns-users
>>
>
>
> _______________________________________________
> Pdns-users mailing list
> Pdns-users at mailman.powerdns.com
> http://mailman.powerdns.com/mailman/listinfo/pdns-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20091018/ab82e94f/attachment-0001.html>