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

Chapman Flack <jcflack(at)acm(dot)org> writes:
> 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.

An extension would be a great solution if we can make it work.

> 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 believe this is possible.

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

I'm less sure about whether all the hooks exist today to do this, but
it seems like something we could allow if it's not there already.
However, I wonder whether this is sufficient. As you've sketched it,
it would permit tracing actions to sessions' login roles, but
individual actions might be taken under other roles. Does that need
to be captured? If so at what granularity? The maximalist approach
would probably be that every WAL record has to be labeled with
current_role, which would be a lot of overhead in the best case,
and still more if it has to be done via a separate WAL record.

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

I think this'd be an acceptable tradeoff if it only has to happen once
per commit. Not sure if that answers the request though.

regards, tom lane