2

We have implemented a browser-based system that is run from a PostgreSQL 9.3 database. We have hit a snag in addressing auditing. Is there a way to record if an audit trigger has been turned off. This question is directly related to having checks and balance for admins in the back-end database. While I am aware that the superuser of a database has unrestricted access, we still need to find a way to restrict 'turning off' an audit trigger or at minimum record in the audit database (controlled separately) that the trigger has been turned off and/or on.

Alternatively, can an account other than the postgres account be used solely to administer triggers, without these rights being overridden by the superuser account?

Thanks, DS

Donald Singh
  • 21
  • 2
  • You can capture the `ALTER TABLE` commands with an [event trigger](http://www.postgresql.org/docs/current/static/event-trigger-definition.html). There is a nice example [here](http://stackoverflow.com/questions/23488228/how-to-get-sql-text-from-postgres-event-trigger/35616026). – Nick Barnes May 16 '16 at 23:33
  • PostgresSQL doesn't yet have fine grained admin rights so you can't grant some "ADMIN TRIGGERS" right to a user. There's work on the beginnings of that for PostgreSQL 9.7. For now all you can really do is enable DDL logging (`log_statement = 'ddl'`). But the superuser can trivially cirvumvent that. They can also directly modify `pg_catalog` with `UPDATE` queries, which won't show up in the logs as DDL. Mandatory audit functionality for the superuser can't really be achieved with triggers. – Craig Ringer May 17 '16 at 01:44
  • There's the [`pg_audit`](https://github.com/2ndQuadrant/pgaudit) extension though. See https://www.depesz.com/2015/05/22/waiting-for-9-5-add-pg_audit-an-auditing-extension/ . – Craig Ringer May 17 '16 at 01:46

0 Answers0