MIME-Version: 1.0
From: "Colin 't Hart" <colinthart@gmail.com>
Date: Tue, 18 Nov 2025 10:17:44 +0100
Message-ID: <CAMon-aRf-XS+wVEnVvP58GtojYENywx+s9pUjwz5omADdLP1NA@mail.gmail.com>
Subject: Using pgAudit to audit interesting tables for all users except for
 batch user?
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="0000000000006cfb540643daf04f"
Archived-At: <https://www.postgresql.org/message-id/CAMon-aRf-XS%2BwVEnVvP58GtojYENywx%2Bs9pUjwz5omADdLP1NA%40mail.gmail.com>
Precedence: bulk

--0000000000006cfb540643daf04f
Content-Type: text/plain; charset="UTF-8"

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

--0000000000006cfb540643daf04f
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi,</div><div><br></div><div>One of my clients has so=
me tables that contain sensitive data. These are
 modified regularly by batch jobs, and then the data is transformed and=20
summary information appended to other tables (fairly typical=20
datawarehouse).<br><br>For these sensitive tables they would like to add
 auditing of all activity -- but not for the batch user as that would=20
just blow up the logs, and we should be able to adequately prevent=20
access to the batch user.<br><br><br>Is there any way we can achieve this?<=
/div><div><br>I tried using a role, registering that with<br><br>alter syst=
em set pgaudit.role =3D &lt;auditrole&gt;;<br><br>and doing<br><br>grant se=
lect,insert,update,delete<br>on &lt;sensetivetable&gt;<br>to &lt;auditrole&=
gt;;<br><br><br>After that all operations on that table ended up audited in=
 the log, as expected.<br><br><br>Then I did<br><br>alter user &lt;batchuse=
r&gt; set pgaudit.log to &#39;none&#39;;<br><br>but after that operations w=
hen logged in as &lt;batchuser&gt; still ended up being audited.<br><br><br=
>What am I missing?<br><br><br>Is what I&#39;m trying even possible? Or is =
there another way to achieve our requirements?<br><br><br>This issue <a hre=
f=3D"https://github.com/issues/recent?issue=3Dpgaudit%7Cpgaudit%7C73" targe=
t=3D"_blank">https://github.com/issues/recent?issue=3Dpgaudit%7Cpgaudit%7C7=
3</a> seems to indicate that it&#39;s possible, but I&#39;m struggling to u=
nderstand how.</div><div><br></div><div>Thanks,</div><div><br></div><div>Co=
lin</div></div>

--0000000000006cfb540643daf04f--