0

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 Oct 15 at 7:55
  • The moment the user calls a specific function. – Chessbrain Oct 15 at 7:59
  • How is the user_id associated with your current session? – Laurenz Albe Oct 15 at 8:08
  • The function the user is calling takes his user_id parameter. So the ID value is in the function during its execution. – Chessbrain Oct 15 at 8:15
  • Then what keeps you from using it in an INSERT statement? – Laurenz Albe Oct 15 at 8:26
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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.