Обсуждение: Can db user change own password?

Hi,

Is there any possibilities that db user can change own password?

There is no difference does the user uses ALTER command or \password meta-command, result is the same - response is
“permissiondenied”. 

dbname=> \password
Enter new password:
Enter it again:
ERROR:  permission denied

BR,
Toomas

On 10/20/21 07:38, Toomas wrote:
> Hi,
> 
> Is there any possibilities that db user can change own password?
> 
> There is no difference does the user uses ALTER command or \password meta-command, result is the same - response is
“permissiondenied”.
 
> 
> dbname=> \password
> Enter new password:
> Enter it again:
> ERROR:  permission denied

Works for me, you will need to supply more information:

1) Postgres version?

2) User you are connecting as?

3) Output of \du <user_name>

> 
> BR,
> Toomas
> 
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Hi Adrian,

Thank you for your help. The issue was that when user logged into database his session_user user was set as owner of
databaseautomatically. User had success to change password when session_user = current_user was set before. 

BR,
Toomas

> On 20. Oct 2021, at 17:45, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>
> On 10/20/21 07:38, Toomas wrote:
>> Hi,
>> Is there any possibilities that db user can change own password?
>> There is no difference does the user uses ALTER command or \password meta-command, result is the same - response is
“permissiondenied”. 
>> dbname=> \password
>> Enter new password:
>> Enter it again:
>> ERROR:  permission denied
>
> Works for me, you will need to supply more information:
>
> 1) Postgres version?
>
> 2) User you are connecting as?
>
> 3) Output of \du <user_name>
>
>> BR,
>> Toomas
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

On 10/20/21 08:07, Toomas wrote:
> Hi Adrian,
> 
> Thank you for your help. The issue was that when user logged into database his session_user user was set as owner of
databaseautomatically. User had success to change password when session_user = current_user was set before.
 

I'm not understanding. You will need to sketch this out:

1) Connection parameters for log in with <user_name>.

2) On log in the output from: select session_user, current_user;

3) Define '...set as owner of database automatically'.

> 
> BR,
> Toomas
> 
>> On 20. Oct 2021, at 17:45, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
>>
>> On 10/20/21 07:38, Toomas wrote:
>>> Hi,
>>> Is there any possibilities that db user can change own password?
>>> There is no difference does the user uses ALTER command or \password meta-command, result is the same - response is
“permissiondenied”.
 
>>> dbname=> \password
>>> Enter new password:
>>> Enter it again:
>>> ERROR:  permission denied
>>
>> Works for me, you will need to supply more information:
>>
>> 1) Postgres version?
>>
>> 2) User you are connecting as?
>>
>> 3) Output of \du <user_name>
>>
>>> BR,
>>> Toomas
>>
>>
>> -- 
>> Adrian Klaver
>> adrian.klaver@aklaver.com
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 10/21/21 08:39, Toomas wrote:
>> 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:

It's fairly obvious what's happening here: psql sends ALTER USER xxx
PASSWORD ..., where it gets xxx from PQuser(), so that is the role name
that was logged in with.  There are any number of reasons why that might
not be the currently active role.

The psql man page says

    \password [ username ]
        Changes the password of the specified user (by default, the
        current user).

So I'd say this is not doing what the documentation says.

With server versions >= 9.5 we could dodge the issue by sending
ALTER USER CURRENT_USER PASSWORD ....  For older servers,
I suppose we could do "SELECT CURRENT_USER" first.

I'm not sure if we want to change a security-relevant behavior
in released branches.  But if we don't, we probably need to
change the docs to something like "(by default, the logged-in
user)".

            regards, tom lane

On 10/21/21 09:53, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:

> It's fairly obvious what's happening here: psql sends ALTER USER xxx
> PASSWORD ..., where it gets xxx from PQuser(), so that is the role name
> that was logged in with.  There are any number of reasons why that might
> not be the currently active role.
> 
> The psql man page says
> 
>      \password [ username ]
>          Changes the password of the specified user (by default, the
>          current user).
> 
> So I'd say this is not doing what the documentation says.

Oops. That is where I got hung up.

> 
> With server versions >= 9.5 we could dodge the issue by sending
> ALTER USER CURRENT_USER PASSWORD ....  For older servers,
> I suppose we could do "SELECT CURRENT_USER" first.
> 
> I'm not sure if we want to change a security-relevant behavior
> in released branches.  But if we don't, we probably need to
> change the docs to something like "(by default, the logged-in
> user)".

I would suggest session(_)user to make it match with the rest of 
documentation.

> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 10/21/21 09:53, Tom Lane wrote:
>> I'm not sure if we want to change a security-relevant behavior
>> in released branches.  But if we don't, we probably need to
>> change the docs to something like "(by default, the logged-in
>> user)".

> I would suggest session(_)user to make it match with the rest of
> documentation.

But that's not right either.

regression=# select session_user;
 session_user
--------------
 postgres
(1 row)

regression=# create user joe;
CREATE ROLE
regression=# set session authorization joe;
SET
regression=> select session_user;
 session_user
--------------
 joe
(1 row)

regression=> \password
Enter new password:
Enter it again:
ERROR:  must be superuser to alter superuser roles or change superuser attribute
regression=>

Another angle to this: even without SET SESSION AUTHORIZATION, the
existence of username mapping options in the pg_hba machinery means that
the role name that psql thought it logged in with might have nothing to do
with the role name that the server thinks is the authenticated user.
There might be no SQL role by that name at all.  So what psql is doing
here is flat-out wrong.  I'm still hesitant about changing the behavior in
the back branches, though, especially given the lack of prior complaints.

            regards, tom lane

Toomas <toomas.kristin@gmail.com> writes:
> 2) db=> select current_user, session_user;
>  current_user | session_user
> --------------+--------------
>  db_owner      | db_user
> (1 row)

Given that setup, I wonder which role you expected \password to change.

If we target the current_user, we can expect the command to succeed.
I'm just wondering if people will find that surprising.
Targeting the session_user might be less surprising (or not?)
but as this example shows, it can fail.

One thing that would help, regardless of which definition we think
is most appropriate, is to have \password explicitly say which role
it's intending to set the password for:

db=> \password
Enter new password for role "dbowner":
Enter it again:

            regards, tom lane

On 10/21/21 10:51, Tom Lane wrote:
> Toomas <toomas.kristin@gmail.com> writes:
>> 2) db=> select current_user, session_user;
>>   current_user | session_user
>> --------------+--------------
>>   db_owner      | db_user
>> (1 row)
> 
> Given that setup, I wonder which role you expected \password to change.
> 
> If we target the current_user, we can expect the command to succeed.
> I'm just wondering if people will find that surprising.
> Targeting the session_user might be less surprising (or not?)
> but as this example shows, it can fail.

Well from here:

https://www.postgresql.org/docs/current/sql-set-session-authorization.html

'The current user identifier is relevant for permission checking.'

To me current_user would be the less surprising choice.

> 
> One thing that would help, regardless of which definition we think
> is most appropriate, is to have \password explicitly say which role
> it's intending to set the password for:
> 
> db=> \password
> Enter new password for role "dbowner":
> Enter it again:

Yes, that would be helpful in untangling who you are actually pointing at.

> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 10/21/21 10:44, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 10/21/21 09:53, Tom Lane wrote:

>> I would suggest session(_)user to make it match with the rest of
>> documentation.
> 
> But that's not right either.
> 
> regression=# select session_user;
>   session_user
> --------------
>   postgres
> (1 row)
> 
> regression=# create user joe;
> CREATE ROLE
> regression=# set session authorization joe;
> SET
> regression=> select session_user;
>   session_user
> --------------
>   joe
> (1 row)
> 
> regression=> \password
> Enter new password:
> Enter it again:
> ERROR:  must be superuser to alter superuser roles or change superuser attribute
> regression=>

Hmm, I'm striking out on this one. Just now grasped that PQuser() is 
grabbing a user/role from the connection itself and that the effective 
role could be something entirely different.

> 
> Another angle to this: even without SET SESSION AUTHORIZATION, the
> existence of username mapping options in the pg_hba machinery means that
> the role name that psql thought it logged in with might have nothing to do
> with the role name that the server thinks is the authenticated user.
> There might be no SQL role by that name at all.  So what psql is doing
> here is flat-out wrong.  I'm still hesitant about changing the behavior in
> the back branches, though, especially given the lack of prior complaints.
> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com