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.
Hello
2011/1/26 El Co <lc4od@yahoo.com>:
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?
no, it isn't
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.
you, can't do it this on PostgreSQL level. Probably you can revoke a
rights to DDL op. Then you are sure, so nobody will do some DDL op.
Next step can be a custom procedure in PL/pgSQL with security definer
right, that can do DDL via dynamic SQL. This procedure can trace a
info to log. A user can use DDL via this procedure.
Regards
Pavel Stehule
You just need to log DDL, correct?
Why not just edit postgres.conf and set log_statement to 'ddl'.
See http://www.postgresql.org/docs/9.0/static/runtime-config-logging.html
If you need to include username, database, etc, take a look at
log_line_prefix on the same page.
Ken
Show quoted text
On Wed, Jan 26, 2011 at 4:30 PM, El Co <lc4od@yahoo.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: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.