Re: Transaction user id through logical decoding - Mailing list pgsql-hackers

<p dir="ltr"><p dir="ltr">On 28 Sep. 2016 17:50, "valeriof" <<a
href="mailto:valerio_farruggio@hotmail.com">valerio_farruggio@hotmail.com</a>>wrote:<br /> ><br /> > Hi
all,<br/> > I'm developing a custom plugin to stream Postgres CDC changes to my client<br /> > application. One
ofthe info the application needs is the user id of the<br /> > user who executed a certain transaction. I can see we
haveaccess to other<br /> > transaction info (xid, lsn, changed data) but apparently the user id is not<br /> >
available.<br/> > Does anyone know if it is possible to extract this info in any way?<p dir="ltr">It is not recorded
inWAL so it isn't possible as-is.<p dir="ltr">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).<p dir="ltr">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
chanceof getting into core. You'd need to work out how to tell when the new info was there too.<p dir="ltr">You could
adda new rmgr that logs use is at tx start and whenever it changes. Doing this robustly could be interesting but I
thinkit'd have more chance. 10.0 at the earliest though.<p dir="ltr">You could use a FOR EACH ROW trigger added to each
tableto xlog a logical wal message (9.6 only) with the user id changing the row. Maybe optimise by keeping a cache with
thelast id logged and only log again if it changes. Care here is needed for cleanup at xact end, rolled back subxact
handlingetc.<p dir="ltr">(If you don't care about handling the corner cases you could use a FOR EACH STATEMENT trigger
instead.)<pdir="ltr">You could use a special table in an extension schema that you insert rows into to record the user
whoperformed an action. Using a before trigger. Delete the row as soon as you insert it since you only care about the
walrecord. Then when decoding inserts examine the affected table oid. If it's your special table, save the stored user
idin output plugin state instead of sending it to the peer as a normal insert. BDR has some things similar to this for
itshandling of ddl replication, TRUNCATE, and global sequence voting that you could take a look at; see bdr_output.c
andbdr_apply.c .<br /><br /><p dir="ltr">> Thanks,<br /> > Valerio<br /> ><br /> ><br /> ><br /> >
--<br/> > View this message in context: <a
href="http://postgresql.nabble.com/Transaction-user-id-through-logical-decoding-tp5923261.html">http://postgresql.nabble.com/Transaction-user-id-through-logical-decoding-tp5923261.html</a><br
/>> Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.<br /> ><br /> ><br /> > --<br />
>Sent via pgsql-hackers mailing list (<a
href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> > To make changes to your
subscription:<br/> > <a
href="http://www.postgresql.org/mailpref/pgsql-hackers">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/>