Add a warning message when using unencrypted passwords

Hello,

We've got a long tradition of telling people not to use unencrypted
passwords in CREATE ROLE and ALTER ROLE because the queries may be logged.
We try to encourage them to use \password in psql, and related techniques
on other tools. Users usually want us to stop logging passwords, but this
is not that easy to do (if it's at all possible and interesting). A few
days ago, I read Divya Sharma's interview on postgresql.life [1] and she
said:

> Whenever log_statement is set to all (which I understand should be done
for a short period of time for troubleshooting purposes only), if we change
the password for a user, or create a new user, the passwords would be
logged in plain text. From a security point of view, this should not be
allowed. Ideally, It should error out (or at least throw a warning) saying
“while log_statement is set to ‘all’, you shouldn’t change passwords/create
new user with passwords”.

While I dislike the idea of throwing an error, I found the idea of a
warning message really great. So kudos to her for the idea!

I thought about it, and tried to write a patch. I've mostly copied the
"Deprecate MD5 passwords" patch/commit from Nathan Bossart. My patch works
on current HEAD. Documentation and tests are dealt with.

Here is a quick demo:

postgres=# show plaintext_password_warnings;
plaintext_password_warnings
-----------------------------
on
(1 row)

postgres=# create user foo password 'bar';
WARNING: using a plaintext password in a query
DETAIL: plaintext password may be logged.
HINT: Refer to the PostgreSQL documentation for details about using
encrypted password in queries.
CREATE ROLE
postgres=# alter role foo password 'bar2';
WARNING: using a plaintext password in a query
DETAIL: plaintext password may be logged.
HINT: Refer to the PostgreSQL documentation for details about using
encrypted password in queries.
ALTER ROLE
postgres=# set plaintext_password_warnings to off;
SET
postgres=# alter role foo password 'bar3';
ALTER ROLE
postgres=# set plaintext_password_warnings to on;
SET
postgres=# \password foo
Enter new password for user "foo":
Enter it again:

As I'm writing this email, I'm thinking we could transform the boolean GUC
into an enum GUC, allowing the user to get an error or a log message, or no
message at all (old behaviour), whatever fits better for him/her.

I'm interested in any comments about this. I didn't create a commitfest
entry yet, I'm mostly waiting on your comments.

Thanks.

Regards.

[1] https://postgresql.life/post/divya_sharma/

--
Guillaume.