Recently we changed our production database log_statement from 'all' to 'mod', because the resulting log file was too large for our available storage. Unfortunately, we still need to log every SELECT made by users to a specific table N for audit purposes.
Is there any solution for that? I have tried using pgaudit and pg_stat_statements without any success.
We have set pgaudit.log to read, but it logs every SELECT query instead of just selects to table N.
You need pgAudit's object audit logging:
Object-level audit logging is implemented via the roles system. The pgaudit.role setting defines the role that will be used for audit logging. A relation (
TABLE,VIEW, etc.) will be audit logged when the audit role has permissions for the command executed or inherits the permissions from another role.
So run
CREATE ROLE auditor;
GRANT INSERT, UPDATE, SELECT, DELETE ON special_table TO auditor;
Then set pgaudit.role = auditor in the configuration, and all access to the table will be audited.
