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(dot)klaver(at)aklaver(dot)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_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(dot)klaver(at)aklaver(dot)com