Adding ddl audit trigger
Trying to get some DDL auditing in a development environment by adding triggers to pg_proc, pg_class,pg_type,pg_trigger and getting the following error:
ERROR: permission denied: "pg_proc" is a system catalog
SQL state: 42501
Is there any way to achieve DDL auditing in Postgres and trace any new/dropped/changed object into a table?
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.
________________________________
Attention:
The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies.
Le 26/01/2011 22:29, Lawrence Cohan a �crit :
Trying to get some DDL auditing in a development environment by adding triggers to pg_proc, pg_class,pg_type,pg_trigger and getting the following error:
ERROR: permission denied: "pg_proc" is a system catalog
SQL state: 42501Is there any way to achieve DDL auditing in Postgres and trace any new/dropped/changed object into a table?
No. If there was, it would open new great possibilities for tools like
Slony or Londiste.
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.
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
Guillaume Lelarge <guillaume@lelarge.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".
regards, tom lane
Le 26/01/2011 23:13, Tom Lane a �crit :
Guillaume Lelarge <guillaume@lelarge.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 :)
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
If Postgres users are not allowed to add triggers to system catalogs (which is fair nothing to say) maybe they could be added into a future release of Postgres. Maybe some basic DDL auditing like the name/type of the object, action, userid(current_user), and timestamp be included in these audit DDL triggers that would be added DISABLED by default and users can turn them on/off at their will.
Regards,
Lawrence Cohan.
-----Original Message-----
From: Guillaume Lelarge [mailto:guillaume@lelarge.info]
Sent: January-26-11 5:41 PM
To: Tom Lane
Cc: Lawrence Cohan; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Adding ddl audit trigger
Le 26/01/2011 23:13, Tom Lane a écrit :
Guillaume Lelarge <guillaume@lelarge.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 :)
--
Guillaume
http://www.postgresql.fr
http://dalibo.com
Attention:
The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies.
guillaume@lelarge.info (Guillaume Lelarge) writes:
Le 26/01/2011 23:13, Tom Lane a écrit :
Guillaume Lelarge <guillaume@lelarge.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/>
On Wed, Jan 26, 2011 at 2:29 PM, Lawrence Cohan <LCohan@web.com> wrote:
Trying to get some DDL auditing in a development environment by adding
triggers to pg_proc, pg_class,pg_type,pg_trigger and getting the following
error:
Yep, can't do it just yet. For now you've got
log_statement='ddl';
which can be set by user, db, or for the whole cluster in postgresql.conf.
You can use the log_line_prefix to see what user did what to which
database. Then you can just grep for alter / create / drop etc. and
see what's happened. not as slick or elegant as ddl triggers, but at
least it's there.
That should do it for our dev environment however on production systems it would be a little bit harder (quite a few hops/approval/restore) to get to and grep the log files.
Many thanks for the suggestion,
Lawrence Cohan.
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: January-27-11 3:31 PM
To: Lawrence Cohan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Adding ddl audit trigger
On Wed, Jan 26, 2011 at 2:29 PM, Lawrence Cohan <LCohan@web.com> wrote:
Trying to get some DDL auditing in a development environment by adding
triggers to pg_proc, pg_class,pg_type,pg_trigger and getting the following
error:
Yep, can't do it just yet. For now you've got
log_statement='ddl';
which can be set by user, db, or for the whole cluster in postgresql.conf.
You can use the log_line_prefix to see what user did what to which
database. Then you can just grep for alter / create / drop etc. and
see what's happened. not as slick or elegant as ddl triggers, but at
least it's there.
Attention:
The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies.
On Thu, Jan 27, 2011 at 1:47 PM, Lawrence Cohan <LCohan@web.com> wrote:
That should do it for our dev environment however on production systems it would be a little bit harder (quite a few hops/approval/restore) to get to and grep the log files.
Sounds like you need a process to scrub logs and ship them out to
where you can get to them.
We'll do that - thanks again.
-----Original Message-----
From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
Sent: January-27-11 3:58 PM
To: Lawrence Cohan
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Adding ddl audit trigger
On Thu, Jan 27, 2011 at 1:47 PM, Lawrence Cohan <LCohan@web.com> wrote:
That should do it for our dev environment however on production systems it would be a little bit harder (quite a few hops/approval/restore) to get to and grep the log files.
Sounds like you need a process to scrub logs and ship them out to
where you can get to them.
Attention:
The information contained in this message and or attachments is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any system and destroy any copies.