pgaudit can log each access to each table. So if an SQL table touches three tables then it can generate three log records, one for each table. This is important because otherwise, you might have to try and parse the SQL to find out which tables it touched, which would be difficult without access to the schema and the search_path settings.
To make it easier to access the audit log per table, adjust these settings:
pgaudit.log_relation = on
pgaudit.log_statement_once = off
If you want even finer grained auditing, pgaudit allows you to control which tables are audited. The user cannot tell which tables are logged and which are not, so it is possible for investigators to quietly enhance the level of logging once they are alerted to a suspect or a potential attack.
First, set the role that will be used by the auditor:
pgaudit.role = ‘investigator’
Then you can define logging through the privilege system, like the following command:
GRANT INSERT, UPDATE, DELETE on <vulnerable_table> TO investigator;
And remove it again when no longer required.
Privileges may be set at individual column level to protect Personally Identifiable Information (PII).