pg_stat_statements showing passwords while create or alter users.

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

pg_stat_statements showing passwords while create or alter users.

PG Bug reporting form
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
Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_statements showing passwords while create or alter users.

Magnus Hagander-2
On Fri, Apr 17, 2020 at 8:50 AM PG Doc comments form <[hidden email]> 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.
Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_statements showing passwords while create or alter users.

Laurenz Albe
In reply to this post by PG Bug reporting form
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



Reply | Threaded
Open this post in threaded view
|

Re: pg_stat_statements showing passwords while create or alter users.

Euler Taveira-2
In reply to this post by PG Bug reporting form
On Fri, 17 Apr 2020 at 03:50, PG Doc comments form <[hidden email]> 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