One of my clients has some tables that contain sensitive data. These are modified regularly by batch jobs, and then the data is transformed and summary information appended to other tables (fairly typical datawarehouse).
For these sensitive tables they would like to add auditing of all activity -- but not for the batch user as that would just blow up the logs, and we should be able to adequately prevent access to the batch user.
Is there any way we can achieve this?
I tried using a role, registering that with
alter system set pgaudit.role = <auditrole>;
and doing
grant select,insert,update,delete on <sensetivetable> to <auditrole>;
After that all operations on that table ended up audited in the log, as expected.
Then I did
alter user <batchuser> set pgaudit.log to 'none';
but after that operations when logged in as <batchuser> still ended up being audited.
What am I missing?
Is what I'm trying even possible? Or is there another way to achieve our requirements?
