Re: get user info on log - Mailing list pgsql-general

From Adrian Klaver
Subject Re: get user info on log
Date
Msg-id 27304245-1e4e-a99a-ffe9-4d4ef80394c2@aklaver.com
Whole thread Raw
In response to Re: get user info on log  (Ganesh Korde <ganeshakorde@gmail.com>)
List pgsql-general
On 9/16/22 19:22, Ganesh Korde wrote:
> Hi,
> 
>    You can use log_line_prefix in postgresql.conf to log the user name 
> into the logs.

That uses the session user, the user that established the connection. 
What the OP is after is the role name that is set by SET ROLE or SET 
SESSION AUTHORIZATION.

To illustrate:

psql -d test -U postgres

test(5432)=# select session_user, current_user;
  session_user | current_user
--------------+--------------
  postgres     | postgres


test(5432)=# set role aklaver;
SET
test(5432)=> select session_user, current_user;
  session_user | current_user
--------------+--------------
  postgres     | aklaver
(1 row)


test(5432)=> select 1/0;
ERROR:  division by zero

 From log with log_line_prefix of log_line_prefix = '%a-%u-%m-%x'
:

psql-postgres-2022-09-17 14:36:06.635 PDT-0ERROR:  division by zero
psql-postgres-2022-09-17 14:36:06.635 PDT-0STATEMENT:  select 1/0;

Ganesh is looking for psql-<current_user> not psql-<session_user>, in 
this case psql-aklaver.

> 
> Regards,
> Ganesh Korde.
> 
> On Fri, 16 Sep 2022, 6:31 pm Marcos Pegoraro, <marcos@f10.com.br 
> <mailto:marcos@f10.com.br>> wrote:
> 
>     Em qui., 15 de set. de 2022 às 12:59, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> escreveu:
> 
>         test(5432)=# set role maura;
>         ERROR:  role "maura" does not exist
>         test(5432)=# SET SESSION AUTHORIZATION 'maura';
>         ERROR:  role "maura" does not exist
> 
>     No, I was asking about an error occurring later, not on set session
>     authorization command.
> 
>     set role maura;
>     --user Maura exists and set was done correctly
>     --now I´m working as Maura
>     select 1/0; -- I would like to see this exception on log being
>     logged as maura
> 
>     thanks
>     Marcos
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com



pgsql-general by date:

Previous
From: "sivapostgres@yahoo.com"
Date:
Subject: Re: Query Performance
Next
From: Adrian Klaver
Date:
Subject: Re: get user info on log