public inbox for [email protected]  

help / color / mirror / Atom feed

Using pgAudit to audit interesting tables for all users except for batch user?
2+ messages / 2 participants
[nested] [flat]

* Using pgAudit to audit interesting tables for all users except for batch user?
@ 2025-11-18 09:17  Colin 't Hart <[email protected]>
  0 siblings, 1 reply; 2+ messages in thread

From: Colin 't Hart @ 2025-11-18 09:17 UTC (permalink / raw)
  To: PostgreSQL General <[email protected]>

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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Using pgAudit to audit interesting tables for all users except for batch user?
@ 2025-11-18 15:10  Greg Sabino Mullane <[email protected]>
  parent: Colin 't Hart <[email protected]>
  0 siblings, 0 replies; 2+ messages in thread

From: Greg Sabino Mullane @ 2025-11-18 15:10 UTC (permalink / raw)
  To: Colin 't Hart <[email protected]>; +Cc: PostgreSQL General <[email protected]>

On Tue, Nov 18, 2025 at 4:18 AM Colin 't Hart <[email protected]> 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


^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2025-11-18 15:10 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-11-18 09:17 Using pgAudit to audit interesting tables for all users except for batch user? Colin 't Hart <[email protected]>
2025-11-18 15:10 ` Greg Sabino Mullane <[email protected]>

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox