One of the solutions available for auditing in PostgreSQL is Audit trigger 91plus.
There is a section in the wiki that is confusing to me:
Changes by the table owner and superusers are tracked, but can be trivially tampered with.
Ok… I am not sure if this means someone with access rights to the database via console or GUI or if this means something else…
If you want this audit log to be trustworthy, your app should run with a role that has at most USAGE to the audit schema and SELECT rights to audit.logged_actions. Most importantly, your app must not connect with a superuser role and must not own the tables it uses. Create your app's schema with a different user to the one your app runs as, and GRANT your app the minimum rights it needs.
Apologies for my lack of understanding: I don't understand the picture here. If I have a web app using a PostgreSQL db, what exactly are the dangers if I use Audit trigger 91plus without the different role the wiki talks about?
Best Answer
If I own the table (the owner is the user that created the table) or am a superuser, I can modify and drop triggers on a table.
This makes it trivial to tamper with trigger based auditing.
For good security, it is a good idea to use an application user different from the object owner, and of course not a superuser.