pg_stat_statements showing passwords while create or alter users.

On Fri, Apr 17, 2020 at 8:50 AM PG Doc comments form <noreply@postgresql.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/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

On Fri, 2020-04-17 at 02:01 +0000, PG Doc comments form wrote:

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'

There is no way to obfuscate such passwords.
Note that these statements will also show up in the server log
if you set "log_statement = 'ddl'".

For these reasons it is recommended to never set your password like
this, but always hash it on the client side first.

Most interactive clients have a way to do that, like psql`s "\password".

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

On Fri, 17 Apr 2020 at 03:50, PG Doc comments form <noreply@postgresql.org>
wrote:

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'

pg_stat_statements has parameter "pg_stat_statements.track_utility" to

control whether utility commands are tracked. It is on by default.

Regards,

--
Euler Taveira http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services