On 28 Sep. 2016 17:50, "valeriof" <valerio_farrug...@hotmail.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?
Advertising
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@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers
