Postgresql – Logging user’s id with the query he executed

loggingpostgresqlpostgresql-11

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?

Best Answer

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.

Related Question