Re: pg_stat_statements showing passwords while create or alter users.

On Fri, Apr 17, 2020 at 8:50 AM PG Doc comments form <noreply(at)postgresql(dot)org>
wrote:

> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/10/bug-reporting.html
> Description:
>
> pg_stat_statements is recording passwords also, is there any way to avoid
> the logging of passwords without droping extension package.
> testdb=> create user test1 with password 'test123';
> CREATE ROLE
> testdb=> create user test2 with encrypted password 'test123';
> CREATE ROLE
> test=> select query from pg_stat_statements where query like '%test%';
> query
> -----------------------------------------------------------
> create user test1 with encrypted password 'test123'
> create user test2 with password 'test123'
>
>
> edbss=> drop extension pg_stat_statements;
> DROP EXTENSION
>

This is documented behaviour and not a bug. The documentation for create
user/create role says:

"Caution must be exercised when specifying an unencrypted password with
this command. The password will be transmitted to the server in cleartext,
and it might also be logged in the client's command history or the server
log. The command createuser, however, transmits the password encrypted.
Also, psql contains a command \password that can be used to safely change
the password later."

So yes, per that page, use createuser, use \password in psql, or you can
also use the PQencryptPasswordConn() API function in libpq if you are
building an application and not just running it one-off.

You can of course also use pg_stat_statements_reset() to clear it out of
the existing history but that doesn't prevent it from being stored there
for a short while.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>