Re: Can db user change own password?

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