session_user different from current_user after normal login

From: Murillo corvino rocha <murillo_corvinorocha(at)hotmail(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: session_user different from current_user after normal login
Date: 2022-11-17 15:11:10
Message-ID: CO6PR17MB50288F5AC97DF80B8EAB6B67EB069@CO6PR17MB5028.namprd17.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

We have a situation where two users are members of a group, like below:

group_read_only
|- user1
|- user2

We tried to change the password for those users, using a query like below:

ALTER ROLE user1 PASSWORD 'sup3r$4fe';

but got a "ERROR: permission denied" (using pgadmin4 and psql docker clients). Using the
"\password" command, we got:

Enter new password for user "group_read_only"

and using the command "\password user1" we got the same "ERROR: permission denied".

After further investigation, and reaching the discussion https://www.postgresql.org/message-id/flat/B340250F-A0E3-43BF-A1FB-2AE36003F68D(at)gmail(dot)com,
we first checked that using the query below:

SELECT session_user, current_user;

We got the result:
session_user: user1
current_user: group_read_only

Following the solution in the discussion above, we solved our problem using the command:
SET SESSION AUTHORIZATION <username>;

For our example:
SET SESSION AUTHORIZATION user1;

After that, it was possible to change the user password.

My question is: is that a normal behavior? should I, after a normal loggin, be logged as
group_read_only as my current_user?

Thanks in advance.

Murillo.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2022-11-17 15:23:02 Re: Configure StopWords in full text search without a configuration file?
Previous Message Pavel Stehule 2022-11-17 14:52:44 Re: unrecognized node type: 350