I do not have access to a Postgres installation, so I cannot check.

I am a security guy, and I'm seeing plaintext passwords in the logs:

create user user1 with password 'PLAINTEXT PASSWORD'

How can the DBAs change or create their passwords without the password in the clear in the logs?

I've seen this, which states you can use an md5 hash of the password, but then the hash is also in the clear. Is there a better way?

  • 1
    If you trust your DBAs (because you cannot enforce it), tell them to use the \password command of psql. Jun 22, 2018 at 16:13

2 Answers 2


Always prepare password digests client-side

If practical in your environment, use psql's \password command, or the underlying libpq function PQencryptPasswordConn(...) (src).

This pre-hashes the password client-side with scram-sha-256 (assuming a modern postgres) then sends it with ALTER USER ... PASSWORD ....

Other client drivers may have their own implementations, and if not, it's pretty simple to implement based using the client crypto libraries of your choice.

Hiding passwords (or digests) from logs

It sounds like log_statement is set to all.

Unfortunately (AFAIK) postgres doesn't special case ALTER USER ... PASSWORD so there's no strong guarantee made that the hashed password won't get logged.

Ideally the libpq protocol would support a "sensitive" flag on bind params, and would support parameter binding for ALTER USER/ALTER ROLE statements. But it doesn't, and the server doesn't proactively mask out the password in the querytext either, so we get to work around it.

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.

SET LOCAL log_statement = 'none';

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}'

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.)

  • 1
    Very good. One thing I'd add with some emphasis is that one simply should not set passwords in cleartext. Jun 22, 2018 at 13:07
  • 2
    Yes, I considered this and mentioned it in the question. It is still not sufficient for my requirements.
    – schroeder
    Jun 22, 2018 at 14:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.