Re: New feature request for adding session information to PostgreSQL transaction log

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