Обсуждение: BUG #15432: ddl logging includes password in plaintext
The following bug has been logged on the website: Bug reference: 15432 Logged by: Rick Otten Email address: rotten@windfish.net 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.
=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes: > 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'; Yup. There are ways to hash the password on the client side before sending, if you want to defend against that. > 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. This complaint has been made before, and rejected before, and I do not foresee the policy changing. Obfuscating the logs is impractical on both performance and system-structural grounds. And if we tried, it would be unreliable --- for instance, if you misspelled any of the keywords in this example command, would you still expect the hypothetical log obfuscator to figure out that it should get rid of 'secretpassword'? Moreover, in most database situations where you'd really care about password secrecy, there is a lot of *other* data going through the system that you'd not want exposed to prying eyes, either. Credit card numbers, personally-identifiable medical data, yadda yadda are all likely to be there in logs of INSERT/UPDATE/SELECT commands. The short answer here is that you need to limit access to the log files just as much as the rest of the database data. regards, tom lane