Re: Can db user change own password?

On 10/21/21 10:44, Tom Lane wrote:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com