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
