Re: Can db user change own password?

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@aklaver.com> 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_useris 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@aklaver.com