Logging user's id with the query he executed

Question

So, I am trying to log not just the postgres user but his ID from my table (for example) Person (person_id) when he calls functions.

So far, from what I can tell I can get almost every information I need from these system tables:

• pg_stat_activity
• pg_stat_statements if I activate it (but probably won't be able to due to some restrictions)

But the part that I can't seem to figure out is if I want to log the user's ID from (for example) the User table (user_id for example).

Basically I'd like to create a small table that will only house the last month's log, and I will insert into it.

• user_id (not system id, but from my own table)
• IP address
• timestamptz of when he called the function/query
• query he executed

My main issue is the user_id part, since the other 3 values already exist in the pg_stat_activity (client_addr, query_start, query)

If there's no easy way to make this happen, lets say I want to create a function that will somehow select the user_id based on parameters returned by pg_stat_activity, but I can't seem to figure out how I would do that. Hidden table parameters like tableoid, cmin, cmax, xmin, xmax, ctid don't help me here, at least I don't see how, maybe I missed something?

• Laurenz Albe: When exactly do you want to insert a row into the log table? Which action triggers that insert?
• Chessbrain: The moment the user calls a specific function.
• Laurenz Albe: How is the user_id associated with your current session?
• Chessbrain: The function the user is calling takes his user_id parameter. So the ID value is in the function during its execution.
• Laurenz Albe: Then what keeps you from using it in an INSERT statement?

Answer 1

Based on your comments, the INSERT could look something like this:

INSERT INTO log (userid, clientaddr, calltime, query)
SELECT user_id, -- function argument
 a.client_addr,
 a.query_start,
 a.query
FROM pg_stat_activity AS a
WHERE a.pid = pg_backend_pid();

The key to identifying the current session is the process ID of the backend process.

Answer 2

pg_stat_activity has application_name. You can use that to append person_id. Before every query you can run below query

set application_name to my_application-<person_id>;

We use this way to log user_id and module from where the query was triggered. This application_name also gets logged in actual logs if you specify that in log format.