We've been struggling for several days now to come up with a mechanism
that allows us to establish a mechanism to store data that remains
persistent for the life of the connection.
Essentially we have a web based application that utilizes a connection
pool (using one single 'super' postgresql database user). At time of
login by a user, we are capturing a variety of user demographics (user
ID, IP address the are coming from, etc). When any action is performed
by the user (a service makes a database call), we have audit triggers
that fire on update/delete/inserts. We'd like the triggers themselves
to have access to that demographic data so the audit records are
correctly stamped (at least with the correct user ID).
We attempted some techniques whereby we create a table and at the time
that each connection is requested from the pool, we extract the PID for
that connection and store it in a table (associating the logged in user
with it). Then our triggers could access this table (and the PID
associated with the current connection) to marry them up and audit
appropriately. As you can imagine, this was fraught with issues of
stale entries (if a PID was reused and a stale entry with the same PID
was never removed from the table) Last thing we wanted was audit
activity incorrectly denoting that user A did something that they didnt do.
We also thought about hijacking one of the session runtime variables
(via the SET command), but aside from not finding a suitable one that
wouldn't cause issues, it sounded dangerous.
Our latest scheme involves giving each web system user a postgresql user
account and when we grab a connection from the connection pool, we SET
SESSION AUTHORIZATION for that user. We can then access the user info
from the trigger.
But is there a better/different way to persist data at the connection level?