Re: Can db user change own password?

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