Hi,
DB user has definition
GRANT db_owner TO db_user;
ALTER ROLE db_user IN DATABASE db SET role TO ‘db_owner’;
and user logs in with command
1) $ psql -U db_user -d db
2) db=> select current_user, session_user;
current_user | session_user
--------------+--------------
db_owner | db_user
(1 row)
3) db=> \du db_user
List of roles
Role name | Attributes | Member of
-----------+------------+-----------
db_user | | {db_owner}
4) db=> \password
Enter new password:
Enter it again:
ERROR: permission denied
5) db=> SET SESSION AUTHORIZATION db_user;
SET
6) db=> select current_user, session_user;
current_user | session_user
--------------+--------------
db_user | db_user
(1 row)
7) db=> \password
Enter new password:
Enter it again:
db=>
I hope this explains.
BR,
Toomas
> On 21. Oct 2021, at 19:13, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
>
> On 10/21/21 08:39, Toomas wrote:
>> Hi,
>> Basically the case was, when *session_user != current_user* then command \password failed with error message “ERROR: permission denied”. All was good when session_user == current_user.
>> In terms of statement “session_user user was set as the owner of the database automatically” - I have a setup where session_user is changed automatically as database owner when user logs into database.
>
> What I am looking for is a step by step outline that approximates:
>
> 1) psql -U <some_user>
>
> 2) The command(s) that set session_user as database owner.
>
> 3) The command(s) that make session_user != current_user.
>
> 4) Output of:
> select session_user, current_user;
>
> 5) The output of \du for the users involved
>
>> BR,
>> Toomas
>
>
>
>
> --
> Adrian Klaver
> adrian.klaver@xxxxxxxxxxx
