Thread

1. Using pgAudit to audit interesting tables for all users except for batch user?

   Colin 't Hart <colinthart@gmail.com> — 2025-11-18T09:17:44Z

   Hi,
   
   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?
   
   
   This issue https://github.com/issues/recent?issue=pgaudit%7Cpgaudit%7C73
   seems to indicate that it's possible, but I'm struggling to understand how.
   
   Thanks,
   
   Colin
   

2. Re: Using pgAudit to audit interesting tables for all users except for batch user?

   Greg Sabino Mullane <htamfids@gmail.com> — 2025-11-18T15:10:46Z

   On Tue, Nov 18, 2025 at 4:18 AM Colin 't Hart <colinthart@gmail.com> wrote:
   
   > alter user <batchuser> set pgaudit.log to 'none';
   >
   
   That's close! pgaudit.log deals with session level things, but you want to
   exclude object-level things. Try:
   
   create role skip_pguadit;
   alter user <batchuser> set pgaudit.role = 'skip_pgaudit';
   
   
   Cheers,
   Greg
   
   --
   Crunchy Data - https://www.crunchydata.com
   Enterprise Postgres Software Products & Tech Support
   

3. Re: Using pgAudit to audit interesting tables for all users except for batch user?

   Colin 't Hart <colinthart@gmail.com> — 2025-11-18T15:55:06Z

   Duh, I feel silly now :-)
   
   Works perfectly.
   
   Many thanks,
   
   Colin
   
   On Tue, 18 Nov 2025 at 16:11, Greg Sabino Mullane <htamfids@gmail.com>
   wrote:
   
   > On Tue, Nov 18, 2025 at 4:18 AM Colin 't Hart <colinthart@gmail.com>
   > wrote:
   >
   >> alter user <batchuser> set pgaudit.log to 'none';
   >>
   >
   > That's close! pgaudit.log deals with session level things, but you want to
   > exclude object-level things. Try:
   >
   > create role skip_pguadit;
   > alter user <batchuser> set pgaudit.role = 'skip_pgaudit';
   >
   >
   > Cheers,
   > Greg
   >
   > --
   > Crunchy Data - https://www.crunchydata.com
   > Enterprise Postgres Software Products & Tech Support
   >
   >