PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15432
PG Version11rc1
OSUbuntu 18.04
Opened2018-10-15 14:44:24+00
Reported byRick Otten
StatusWon't Fix

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15432
Logged by:          Rick Otten
Email address:      (redacted)
PostgreSQL version: 11rc1
Operating system:   Ubuntu 18.04
Description:        

When I have logging set to 'ddl', and create a user, I can see their
password in plain text in the logs:

postgres=# create user example with password 'secretpassword';
CREATE ROLE

2018-10-15 10:09:03.513 EDT postgres 127.0.0.1 postgres psql LOG: 
statement: create user example with password 'secretpassword';

This may not technically be a 'bug'.   However, it should not be
out-of-the-box behavior.   The password itself should be obfuscated from the
logs by default.  Perhaps there are some use cases where seeing the actual
password in the logs is useful, but they should be an edge case and not
typical.

I'd like to forward all logs to a centralized logging system.  I do not want
to forward passwords in plain text to it.  I could make a "postgresql-aware
log forwarder" that obfuscates the passwords before forwarding the logs,
however I'm also not comfortable with all of the passwords sitting around on
disk and disk backups in the logs in plain text either.   

On the other hand, I definitely want to log all DDL and other changes that
are happening to the system, so I don't want to set log level lower.

I saw in a greenplum blog [ 
https://community.pivotal.io/s/article/How-to-Create-a-User-with-an-Encrypted-Password
] at I might be able "pre-hash" the password in some scenarios and then use
that when I create the user or role so the hash ends up in the logs instead
of the plain text.  That seems hacky, at best.  The examples on that page
use md5, I'm not sure it would work with scram-sha-256.  Based on the
documentation I think it will work with PG 11.  I'll experiment with that
potential work-around, meanwhile I wanted to log this issue (since I didn't
find it with the searches I ran) - many DBA's may not even notice those
lines going by in their log files and realize this is exposure they might
not want.

It would be much easier on us resource strapped pg admins if the logs simply
didn't log the password itself.

Messages

DateAuthorSubject
2018-10-15 14:44:24+00=?utf-8?q?PG_Bug_reporting_form?=BUG #15432: ddl logging includes password in plaintext
2018-10-15 15:02:27+00Tom LaneRe: BUG #15432: ddl logging includes password in plaintext