Revoking SELECT from the audit role does not disable read object audit logging for that relation

[v3rm0n]

Revoking SELECT from the audit role does not disable read object audit logging for that relation

[v3rm0n]

So this example is actually incorrect and I removed the relevant part when trying to anonymise the query. What actually happens is that when you do SELECT ... FOR UPDATE then the read gets logged even though there is no SELECT granted on the auditor.
For example like this

-- Create test5 and add permissions
CREATE TABLE test5
(
    id int
);

GRANT select,insert, update, delete
    ON TABLE public.test5
    TO auditor;
--
-- Not object logged because SELECT not granted
SELECT id
FROM public.test5;

INSERT INTO public.test5 (id)
VALUES (0);

Relevant output that should not be there:

WARNING:  AUDIT: OBJECT,5,1,READ,SELECT,TABLE,public.test5,"SELECT id
FROM public.test5 FOR UPDATE;",<not logged>

Would it be possible to handle this or is this "working as advertised" ?

[v3rm0n]

Found the relevant part of the Postgres code

https://doxygen.postgresql.org/parsenodes_8h_source.html#l00100

/* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */
 #define ACL_SELECT_FOR_UPDATE   ACL_UPDATE

[v3rm0n]

Here's a proof of concept PR to check if it is possible #219

[danpalmer]

@v3rm0n given that SELECT ... FOR UPDATE is a write operation, requiring UPDATE privileges seems correct, although I'll admit it's a little strange on first reading. It also implies a later update, and I can't think of a use-case (at the Postgres level, not thinking of pgAudit) for SELECT ... FOR UPDATE for a user that doesn't have privileges to then perform the update it has locked for?

Based on this, I'd suggest this is probably working as intended. I'm not familiar with pgAudit, but perhaps audit logs could be filtered post-hoc before ingestion into other systems in order to reduce verbosity?

[v3rm0n]

@danpalmer makes sense in general, but in my specific case we use AWS RDS where you can't control the logging much, you either use CloudWatch or nothing and when it arrives in CloudWatch, the cost is already occurred. For usability reasons we can filter it afterwards, but we can't reduce the cost anymore.

Another thought is that since there is a pgaudit.log parameter for session logs which controls the granularity of session logs, there can be a similar parameter for object logs where one could enable/disable logging in case of different types of row locks which translate to SELECT ... FOR UPDATE, SELECT ... FOR SHARE etc.