On 01/19/25 12:02, Tom Lane wrote: > You can build that yourself, typically by adding a trigger that stores > the value of "current_user" into inserted/updated rows. (If you want > to also track deletions, a separate audit log table would work > better.) The event-trigger feature might also be useful.
I wonder how close one could get to the customer request (better forensics without having to build extra columns and triggers at the SQL level) with an extension and existing hooks. I haven't used it, but isn't there now a facility for inserting additional custom records into the WAL? With ClientAuthentication_hook, could an extension add a record there for the creation of a new session, with timestamp and authenticated role oid? Could an XactCallback be used to add a custom record at commit time identifying the responsible session? There would then be enough breadcrumbs to follow forensically from the commit to the session to the credentials. An added custom record at commit time likely costs more in space than extending the existing commit record with a session id, but seems like something an extension could do without changes in core. Forensic information would then be present with no need for a customer to build extra table columns and triggers. It would be recorded transparently, and would need some custom WAL-reading forensic tool to piece it back together if ever needed. But just such a facility would have made me quite happy back when I was experimenting[1] with using pg_commit_ts forensically. Regards, -Chap [1] https://www.postgresql.org/message-id/8527e4bf-a3c0-f056-978b-ff4096951e3d%40anastigmatix.net
