Always prepare password digests client-side
If practical in your environment, use
\password command, or the underlying
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
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';
ALTER USER ... SET PASSWORD ...;
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_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.