How do I hide sensitive information like plaintext passwords from the logs?
It sounds like log_statement
is set to all
.
If you wish to prevent passwords from appearing in the logs while log_statement
is set to a value that captures ALTER USER
/ ALTER ROLE
then you'll want to override that when changing passwords. e.g.
BEGIN;
SET LOCAL log_statement = 'none';
ALTER USER ... SET PASSWORD ...;
COMMIT;
You must be a superuser to do this. Normal users cannot override logging rules.
It would be nice if PostgreSQL supported flagging some parameters to statements (or even functions) as security-sensitive and allowed users to request that they be masked in logs, pg_stat_statements
, pg_stat_activity
, etc. There is not currently any such feature - but hey, patches are welcome. If you're genuinely interested, post on pgsql-hackers before writing any actual code so you can get advice and comments, though. Alternately, speak to someone who does contract development.
In general PostgreSQL expects you to treat the logs as sensitive.
There are other areas where logging is a serious security concern. For example some of the pgcrypto
functions take crypto keys as parameters.
The currently accepted answer did not work for me on Postgres 9.4, for preventing the plaintext password from showing up in the logs.
What worked for me instead was generating the password hash locally :
$ username='some_user'; dbpass='some_pass'; echo -n "${dbpass}${username}" | md5sum | awk '{print "md5" $1}'
md5dcca63c0632e24746a19448231cac29c
Then inserting that :
ALTER USER some_user WITH UNENCRYPTED PASSWORD 'md5dcca63c0632e24746a19448231cac29c';
(Security note : Depending where you generate the above, you may want to prevent your shell from logging to its history, or on a shared system you may want to use a *nix utility that will prompt for the password versus actually including it in the command. I happened to not be on a shared system, so that was not a concern for me.)