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@gmail.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.
