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?

When exactly do you want to insert a row into the log table? Which action triggers that insert? — Laurenz Albe
– Laurenz Albe, Commented Oct 15, 2019 at 7:55
The function the user is calling takes his user_id parameter. So the ID value is in the function during its execution. — Chessbrain
– Chessbrain, Commented Oct 15, 2019 at 8:15

Laurenz Albe · Accepted Answer · 2019-10-15 08:35:05Z

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.

Shaunak Sontakke · Accepted Answer · 2019-10-15 08:28:02Z

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.

Stack Exchange Network

Logging user's id with the query he executed

2 Answers 2

Hot Network Questions

Logging user's id with the query he executed

2 Answers 2

Related

Hot Network Questions