Thread: BUG #16449: Log file and the query field of the pg_stat_statements table display clear text password.
BUG #16449: Log file and the query field of the pg_stat_statements table display clear text password.
From
PG Bug reporting form
Date:
The following bug has been logged on the website: Bug reference: 16449 Logged by: yi Ding Email address: abcxiaod@126.com PostgreSQL version: 10.12 Operating system: linux Description: 1、The log_statement is set to ALL 2、Execute statement:alter user t password 'adsf123asg'; 3、Log file shows clear text password. 2020-05-18 10:32:11.606 CST,"postgres","postgres",16959,"[local]",5ec1f354.423f,3,"",2020-05-18 10:30:44 CST,14/26972,0,LOG,00000,"statement: alter user t password 'adsf123asg';",,,,,,,,"exec_simple_query, postgres.c:963","psql" 4、The query field of the pg_stat_statements table displays the clear text of the password. postgres=# select * from pg_stat_statements where queryid = '2555618481'; -[ RECORD 1 ]-------+----------------------------------- userid | 10 dbid | 12298 queryid | 2555618481 query | alter user t password 'adsf123asg' calls | 2 total_time | 0.266986 min_time | 0.127103 max_time | 0.139883 mean_time | 0.133493 stddev_time | 0.00639000000000001 rows | 0 shared_blks_hit | 6 shared_blks_read | 0 shared_blks_dirtied | 2 shared_blks_written | 0 local_blks_hit | 0 local_blks_read | 0 local_blks_dirtied | 0 local_blks_written | 0 temp_blks_read | 0 temp_blks_written | 0 blk_read_time | 0 blk_write_time | 0
Re: BUG #16449: Log file and the query field of thepg_stat_statements table display clear text password.
From
Magnus Hagander
Date:
On Mon, May 18, 2020 at 11:41 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 16449
Logged by: yi Ding
Email address: abcxiaod@126.com
PostgreSQL version: 10.12
Operating system: linux
Description:
1、The log_statement is set to ALL
2、Execute statement:alter user t password 'adsf123asg';
3、Log file shows clear text password.
Yes, if you intentionally send the query in clear text, it will be logged in clear text.
Just like with your report about creating user, it is clearly documented in the ALTER ROLE documentation that if you don't want this, you should use \password or a similar functionality, and not the cleartext ALTER USER.
Re: BUG #16449: Log file and the query field of thepg_stat_statements table display clear text password.
From
Oleksandr Shulgin
Date:
On Mon, May 18, 2020 at 11:45 AM Magnus Hagander <magnus@hagander.net> wrote:
1、The log_statement is set to ALL
2、Execute statement:alter user t password 'adsf123asg';
3、Log file shows clear text password.Yes, if you intentionally send the query in clear text, it will be logged in clear text.Just like with your report about creating user, it is clearly documented in the ALTER ROLE documentation that if you don't want this, you should use \password or a similar functionality, and not the cleartext ALTER USER.
I think it's worth noting that using psql's \password command still results in an ALTER USER being sent to the server, and thus ending up in the logs. The difference is that the logged password is already encrypted:
postgres=# \password t
Enter new password:
Enter it again:
********* QUERY **********
ALTER USER t PASSWORD 'md5cf853b7f00ed64ef120b3f6af0d073c2'
**************************
Enter new password:
Enter it again:
********* QUERY **********
ALTER USER t PASSWORD 'md5cf853b7f00ed64ef120b3f6af0d073c2'
**************************
Cheers,
--
Alex
Re: BUG #16449: Log file and the query field of thepg_stat_statements table display clear text password.
From
Michael Paquier
Date:
On Tue, May 19, 2020 at 08:22:09AM +0200, Oleksandr Shulgin wrote: > I think it's worth noting that using psql's \password command still results > in an ALTER USER being sent to the server, and thus ending up in the logs. > The difference is that the logged password is already encrypted: > > postgres=# \set ECHO_HIDDEN > postgres=# \password t > Enter new password: > Enter it again: > ********* QUERY ********** > ALTER USER t PASSWORD 'md5cf853b7f00ed64ef120b3f6af0d073c2' > ************************** Even with that, please also remember that for a md5-hashed password, having the MD5 hash is enough to be able to log into the server. That's not the case with SCRAM... -- Michael