Re: Can db user change own password?

Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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