Improving psql's \password command

The attached patch responds to the discussion at [1]. That thread
pointed out that if you issue "\password" with no argument, psql tries
to set the password for the role identified by PQuser(conn), that is,
the originally requested login name. That's problematic because
if you've done SET SESSION AUTHORIZATION or SET ROLE, you may no
longer have permissions to set the password for the original role.
Even if you do, that might not be what you were expecting, especially
since the psql documentation specifically says it sets the password
for "the current user".

The solution adopted here is to do "SELECT CURRENT_USER" to acquire
the correct role name, and to include the role name in the password
prompt so as to make it very clear which password is to be set.

While testing that, I noticed another bit of user-unfriendliness:
there's no obvious way to get out of it if you realize you are
setting the wrong user's password. simple_prompt() ignores
control-C, and when you give up and press return, you'll just
get the prompt to enter the password again. If at this point
you have the presence of mind to enter a deliberately different
string, you'll be out of the woods. If you don't, and just hit
return again, you will get this response from the backend:

NOTICE: empty string is not a valid password, clearing password

which is just about the worst default behavior I can think of.
If you're superuser, and you meant to set the password for user1
but typed user2 instead, you just clobbered user2's password,
and you have no easy way to undo that.

What I propose here is that we just refuse to let you set an
empty password this way:

postgres=# \password joe
Enter new password for user "joe":
Empty string is not a valid password.
postgres=#

If you actually want to clear the password, I don't see anything
wrong with entering "alter user joe password null" explicitly.

It's not clear to me whether we ought to back-patch some or all
of this. In the other thread I expressed doubt about back-patching
security-relevant behavior changes, and that still seems like a
serious concern; but on the other hand, the behaviors enumerated
above are pretty bad. Since \password is probably only used
interactively, it seems like we could get away with making these
changes: the addition of the user name to the prompt should be
enough to cue anyone about what's really going to happen.

A compromise position could be to keep PQuser() as the default
target role name in the back branches, but back-patch the other
aspects (the prompt addition and the exit on empty password).

Thoughts?

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/B340250F-A0E3-43BF-A1FB-2AE36003F68D%40gmail.com