Re: Transaction user id through logical decoding

On 28 Sep. 2016 17:50, "valeriof" <valerio_farruggio(at)hotmail(dot)com> wrote:
>
> Hi all,
> I'm developing a custom plugin to stream Postgres CDC changes to my client
> application. One of the info the application needs is the user id of the
> user who executed a certain transaction. I can see we have access to other
> transaction info (xid, lsn, changed data) but apparently the user id is
not
> available.
> Does anyone know if it is possible to extract this info in any way?

It is not recorded in WAL so it isn't possible as-is.

Also you can't assume a tx is all done by one user id. SET ROLE, SECURITY
DEFINER, etc. Even the session user can change during a tx (which IMO a
defect).

You have a couple of options. You could patch pg to add an option to xlog
user id with heap and heap2 rmgr writes, but I doubt it'd have much chance
of getting into core. You'd need to work out how to tell when the new info
was there too.

You could add a new rmgr that logs use is at tx start and whenever it
changes. Doing this robustly could be interesting but I think it'd have
more chance. 10.0 at the earliest though.

You could use a FOR EACH ROW trigger added to each table to xlog a logical
wal message (9.6 only) with the user id changing the row. Maybe optimise by
keeping a cache with the last id logged and only log again if it changes.
Care here is needed for cleanup at xact end, rolled back subxact handling
etc.

(If you don't care about handling the corner cases you could use a FOR EACH
STATEMENT trigger instead.)

You could use a special table in an extension schema that you insert rows
into to record the user who performed an action. Using a before trigger.
Delete the row as soon as you insert it since you only care about the wal
record. Then when decoding inserts examine the affected table oid. If it's
your special table, save the stored user id in output plugin state instead
of sending it to the peer as a normal insert. BDR has some things similar
to this for its handling of ddl replication, TRUNCATE, and global sequence
voting that you could take a look at; see bdr_output.c and bdr_apply.c .

> Thanks,
> Valerio
>
>
>
> --
> View this message in context:
http://postgresql.nabble.com/Transaction-user-id-through-logical-decoding-tp5923261.html
> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers