Audit record row access

Where should I start if I want to log access to records and store their ids and what user accessed them? Its not clear how I can use pgaudit, that seems more geared to logging record changes not record access. It looks like I cant attach a trigger for records based on SELECT either. Id like this to be done as close to the db as possible so I can also log access when I am in the dashboard otherwise Id build something at the application level.

garyaustin•1/10/25, 5:23 PM

You might need to use rpc calls for all access then you can log in the access with an insert to an access table.

garyaustin•1/10/25, 5:26 PM

Anything you do would have to be done in Postgres so certainly searching the web on logging select access in Postgres might turn up things people have done. https://www.depesz.com/2022/07/04/how-to-log-selects-from-specific-table/

select * from depesz;depesz

How to log selects from specific table?

Mr.BlackSniper•1/10/25, 5:27 PM

You could also add a trigger on the SELECT queries. this way you could write in another table that a user got the result.
Beside that this is a bad idea, what exactly are you looking for?
Do you want to really log which user got which entry in your db as a result whener he gets it?

garyaustin•1/10/25, 5:30 PM

https://jonmeyers.io/blog/create-a-select-trigger-in-postgresql/
Shows the use of rpc to do this.

@Mr.BlackSniper Can you clarify what you mean by a Select trigger? Postgres does not have that as far as I know.

typelevenOP•1/10/25, 5:32 PM

correct, as far as I can tell trigger on select is not supported. but I would love to be wrong about that

typelevenOP•1/10/25, 5:33 PM

for compliance I will need to be able to report who accessed sensitive data in the database. I want to include access from the dashboard as well

garyaustin•1/10/25, 5:34 PM

You might have to use PG_audit somehow then. The dashboard rules out rpc.

typelevenOP•1/10/25, 5:37 PM

when pgaudit logs a commend can that action then trigger something to log the record data ( just the id ) somewhere?

typelevenOP•1/10/25, 5:38 PM

in the end I ultimately just want a bunch of rows that have table,id,account,timestamp

typelevenOP•1/10/25, 5:39 PM

from there I can make reports

garyaustin•1/10/25, 5:39 PM

You should research that on the web. There are maybe 2 or 3 users here I've seen who might know the answer if it can even be done.

typelevenOP•1/10/25, 5:39 PM

thanks, I'll keep looking

Ttypeleven correct, as far as I can tell trigger on select is not supported. but I would lo...

Mr.BlackSniper•1/10/25, 5:53 PM

yeah sorry, i meant to misuse the RLS Select option.
I've only worked with it to do what it is meant for, but as far as i know it might execute any SQL statement. Hence the moment it is executed to validate something, you could fire an INSERT into another table.

but i think there are better ways to do that

garyaustin•1/10/25, 5:55 PM

If that works, on a large table it is likely to be a disaster as it will get run on every row. Also would not work for service_role or postgres user as RLS does not run at all.

Audit record row access

Similar Threads

Similar Threads

Similar Threads