Re: Add a warning message when using unencrypted passwords

On Sat, 7 Dec 2024 at 15:40, Guillaume Lelarge <guillaume(at)lelarge(dot)info> wrote:
> > 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!

+1 for more clearly letting people know that what they're doing is not
recommended from a security standpoint.

Regarding warning vs error, I agree that a WARNING is probably the
right choice generally. But I think that Divya is correct: When
log_statement = 'all', an error should be thrown instead. Because when
that is the case, we know for sure that the password will be leaked to
the logs. And that error should contain something like: You should
consider this password compromised.

Throwing an error always actually has an interesting downside: We then
automatically log the statement, and thus the password to the log.
When I change the level to ERROR in your code, I get the following
(but with WARNING the STATEMENT line is not there):

2024-12-08 22:59:50.967 CET [104900] ERROR: using a plaintext
password in a query
2024-12-08 22:59:50.967 CET [104900] DETAIL: plaintext password may be logged.
2024-12-08 22:59:50.967 CET [104900] HINT: Refer to the PostgreSQL
documentation for details about using encrypted password in queries.
2024-12-08 22:59:50.967 CET [104900] STATEMENT: ALTER ROLE jelte
PASSWORD 'abc';

PS. I created a commit fest entry here:
https://commitfest.postgresql.org/51/5426/