Re: Adding ddl audit trigger

guillaume(at)lelarge(dot)info (Guillaume Lelarge) writes:
> Le 26/01/2011 23:13, Tom Lane a écrit :
>> Guillaume Lelarge <guillaume(at)lelarge(dot)info> writes:
>>> Le 26/01/2011 22:29, Lawrence Cohan a écrit :
>>>> All I need is to at least be able and save a userid(current_user),
>>> timestamp, action, and the name of the object and this could be
>>> done easily by adding triggers to these pg catalogs.
>>
>>> Nope, sorry. You can't add triggers on system catalogs.
>>
>> This has been discussed, and I think it's on the TODO list. It's not
>> "easily done".
>
> Yes, AFAIR, it was discussed during pgcon dev meeting last year. Jan
> Wieck was interested to work on this (I suppose to help Slony work
> better with DDL). Still hope to see some progress on this :)

The notion there is that it would be Nice To Have DDL triggers where
you'd be able to get at stuff like (as I "wave hands frantically"):

NEW.ddl_statement

which would capture the change that was requested.

This is further harder than it seems, because what you'd want isn't
simply the DDL that was submitted, but rather a somewhat post-processed
"canonical" form so that you've got:
- consistent encoding of strings
- consistent encoding of datestamps
- fully qualified names

So that's not the query that was submitted, but rather some re-expansion
of the parse tree.

It would also be nice if one of the return results was some sort of...
NEW.parsed_statement
so that you could have a function that walks the parse tree, grabbing
whatever bits it wants to grab. It is surely unattractive to require
that drawing data out of the DDL requires re-parsing it.

See also: <http://wiki.postgresql.org/wiki/DDL_Triggers>

The latest "conclusion" seems to be that if someone really wants to
contribute that effort, they're free to do so.
<http://wiki.postgresql.org/wiki/SlonyBrainstorming#DDL_Triggers>
--
Rules of the Evil Overlord #149. "Ropes supporting various fixtures
will not be tied next to open windows or staircases, and chandeliers
will be hung way at the top of the ceiling."
<http://www.eviloverlord.com/>