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

Hi,

On 2025-01-21 13:28:43 -0500, Chapman Flack wrote:
> 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?

I'd probably not create custom records, I'd just use pg_logical_emit_message()
with an appropriate prefix. That way you can emit both transactional and
non-transactional records etc

> 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.

Yes.

> 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.

The added space overhead should be small enough to not really matter in most
scenarios. Unless you do a lot of tiny tiny transaction it's not going to be a
lot compared of the size of WAL for actual DML.

The one issue I see is that it's not quite trivial to emit a WAL record with
extra information for a transaction iff the transaction actually performed
DML.

Greetings,

Andres Freund