PostgreSql Event trigger for Auditing

Question

I am about to implement an audit system in my database and am looking for the most effective way to do this.

I've read the wiki and did my research.

There is this example: https://wiki.postgresql.org/wiki/Audit_trigger And also this: https://wiki.postgresql.org/wiki/Audit_trigger_91plus

In the listed examples you have to create a trigger for each table (kinda redudant).

However with Postgres 9.3+ we have the ability of event triggers where we can react on special events like create, alter or drop table.

An optimal solution would be:

create event trigger UpdateTables
   on ddl_command_end
   when tag in ( 'insert ', 'update ')
   execute procedure DoAudit();

However that returns:

ERROR: filter value "insert " not recognized for filter variable "tag" SQL Status:42601

I am wondering if we also can react on insert, update events with event triggers and use this single trigger for all auditing?

You indeed need to create a trigger for each table, but all triggers can (re)use the same trigger function. — user330315
– user330315, Commented Aug 26, 2016 at 12:05

Boris Schegolev · Accepted Answer · 2016-08-26 09:22:40Z

1

This won't work.

The "on ddl_command_end" means the event is intended to be invoked on structural changes in the database. The complete reference to events is here: https://www.postgresql.org/docs/current/static/event-trigger-matrix.html

answered Aug 26, 2016 at 9:22

Boris Schegolev

3,7115 gold badges23 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Over a year ago

This list is helpful indeed. Hopefully in the future there will be additional functionality be added to event triggers so we can have a nice audit system.

andehhh · Accepted Answer · 2016-08-26 12:02:22Z

-1

Have a look at this:

https://eager.io/blog/audit-postgres/

This is a quite simple, yet effective way to implement an auditing system, making use of the hstore data type. I recently tried it and it worked flawlessly "out of the box".

answered Aug 26, 2016 at 12:02

andehhh

1308 bronze badges

2 Comments

Thomas Over a year ago

Thanks, i'll have a look at it.

Denis Rouzaud Over a year ago

link is not available anymore

Collectives™ on Stack Overflow

PostgreSql Event trigger for Auditing

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related