Postgres Connection Identifier Identification

Question

I have a Postgres (9) database for which I am writing a trigger. I want the trigger to set the modification time and user ID for the record. In Firebird, you have a CONNECTIONID that you can use in a trigger, so you can add a value to the table when connecting to the database (this is a desktop application, therefore connections are stable for the application to live), something like this:

  UserId |  ConnectionId
 ---------------------
 544 |  3775

and then find the trigger that connectionid 3775 belongs to user ID 544 and uses 544 as the user who changed the entry.

Is there anything similar in Postgres?

Answer 1

you can use the process id. It can be found with:

pg_backend_pid() 

With this pid, you can also use the pg_stat_activity table to get more information about the current backend, although you should already know everything since you are using this backend.

Or better. Just create a series and extract one value from it for each connection:

 CREATE SEQUENCE 'connectionids'; 

And then:

 SELECT next_val('connectionids'); 

in each connection to get a unique connection identifier.

Answer 2

One way is to use the custom_variable_classes configuration custom_variable_classes . It seems to be intended to configure additional modules, but can also be used to store arbitrary values ​​in the current database session.

In postgresql.conf you need to add something along the lines of the following contents:

 custom_variable_classes = 'local' 

The first time you connect to the database, you can store any required information in a user class, for example:

 SET local.userid = 'foobar'; 

And later, you can get this value using the current_setting() function:

 SELECT current_setting('local.userid'); 

Adding an entry to the log table might look something like this:

 INSERT INTO audit_log VALUES (now(), current_setting('local.userid'), ...) 

Answer 3

While it may work on your desktop, note that process ID numbers are rolling over (32768 is a common upper limit), so using them as a unique key to identify a user can lead to problems. If you ever end up with data left from a previous session in a table that tracks user mapping -> a process that may encounter newer connections that are assigned the same process ID after it is reversed. It may be enough for your application to simply make sure that you aggressively clear old matching records, possibly during startup, as described in its operation.

To avoid this problem as a whole, you need to create a connection key that includes an additional bit of information, for example, when the session started:

 SELECT procpid,backend_start FROM pg_stat_activity WHERE procpid=pg_backend_pid(); 

This is necessary to repeat all the connections active at that time for calculation, so it adds a bit of overhead. It is possible to do this more efficiently starting with PostgreSQL 8.4:

 SELECT procpid,backend_start FROM pg_stat_get_activity(pg_backend_pid()); 

But it really matters if a large number of connections are activated at the same time.

Answer 4

Use current_user if you need a database user (I'm not sure what you want by reading your question).