pg_stat_statements: password in command is not obfuscated

On 24 March 2018 at 10:30, legrand legrand <legrand_legrand@hotmail.com> wrote:

It seems that passwords used in commands are not removed when caught by
pg_stat_statements
(they are not "normalized" being utility statements)

exemple:
alter role tt with password '123';

select query from public.pg_stat_statements
where query like '%password%';

query
----------------------------------------
alter role tt with password '123';

Do you think its a bug ?

If it is, then it's not a bug in pg_stat_statements. log_statement =
'ddl' would have kept a record of the same thing.

Perhaps the best fix would be a documentation improvement to mention
the fact and that it's best not to use plain text passwords in
CREATE/ALTER ROLE. Passwords can be md5 encrypted.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Sat, Mar 24, 2018 at 12:17:30PM +1300, David Rowley wrote:

If it is, then it's not a bug in pg_stat_statements. log_statement =
'ddl' would have kept a record of the same thing.

Perhaps the best fix would be a documentation improvement to mention
the fact and that it's best not to use plain text passwords in
CREATE/ALTER ROLE. Passwords can be md5 encrypted.

Yeah, this is bad practice. That's one of the reasons why storage of
plain text passwords has been removed in Postgres 10 still they can be
passed via command, and also why PQencryptPasswordConn and
PQencryptPassword are useful. Using psql's \password is a good habit to
have.
--
Michael