Re: PATCH: warn about, and deprecate, clear text passwords

On Fri, Jan 9, 2026 at 3:58 PM Greg Sabino Mullane <htamfids(at)gmail(dot)com> wrote:
>
>
> On Wed, Mar 19, 2025 at 11:01 AM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
>>
>> One of the main reasons I'm not totally sold on a clear-text password warning is because we don't have
>> agreement on removing that ability anytime soon, not to mention Bruce's point about the debate extending into mid-March.
>
>
>
> Okay, we are now safely past last March :). Any further thoughts on this? My preference is still warn by default, but I could also be on board with ignore by default.
>
> Nathan, I'm not sure that having a concrete resolve to someday remove cleartext passwords should be a blocker to warning now that they are a bad idea, but open to discussion there.

I owed a review to Greg, so I've taken a look at this old thread, and oh my
I didn't realize what I was up to, but I'll try to revive the attempt.

I think Robert in [1] had the best longer-term idea about passing password in
extended protocol only and using a new command dedicated for this. As for the
Tom's question from [2] I had immediate thought: why not use psql's \bind
there in pg_dumpall -g to pass password via extended protocol?

There are some PoC patches (full-disclaimer: by Claude Opus), but they are for
re-igniting the important discussion, and NOT for commit (just seeking how it
would work/behave, not for proper impl. and for finding weak spots in overall
concept, I hope I'm not going to be grilled too harshly for doing that here
;), as the topic seems to be a bit critical)

0001 - this is by Greg, cleartext_passwords_action GUC; it's just re-applied
by LLM due to bit-rot, so this is not an AI patch. Just for someone
to have ready to thing on -master. If that ever gets in, he is the
author
0002 - introduce 'PASSWORD $1, $2' top level planning command (so it works
with extended protocol)
0003 - desperate attempt to tame the passwords from server log; doomed(?)
0004 - same as above, but for extended protocol too; doomed(?)
0005 - teach pg_dumpall -g to issue PASSWORD with \bind

Intro, my rationale:
====================

1. I've run a 'wildai' semi-production-like experiments where the LLM was
used to attack semi-safe PG~master connecting as "app_dba" kind of account
(owner of all data, pg_monitor for troubleshooting/monitoring) and plenty
of extensions. I did not want the LLM to concentrate on finding buffer
overflows, but I intentionally planted some *typical* mistakes that people
regularly do from what I saw (this is largely outside the scope of this
thread), but first thing was ... in under like 5mins and a couple of USD it
deduced on its own that I've created some other users from pg_stat_statements
with CREATE USER/ALTER USER that had pg_read_all_data, then it connected there
using dblink extension over 127.0.0.1, fetched pg_authid superuser passwords,
used john the ripper, used dblink again to reach now that superuser and
execute touch /var/lib/pgsql/data/DONE.txt )) (that was its objective).
The key thing for me here is: how __FAST__ this was. Now the thing is that
even if I know all of this I have NO way to force any of my co-workers (DBAs)
to prevent that vulnerable thing from happening. One could say that SCRAM
password could be also there, so it would only delay the thing (it would take
more time for jtr)..

2. Earlier Greg mentioned PCI DSS compliance, so I've checked and PCI DSS says
via requirement 8.3.2 (v4.x) or 8.2.1 (v3.x):

Using strong cryptography, render all authentication credentials (such as
passwords/phrases) unreadable during transmission and __storage on all
system components__

Later they even say (10.7):
Retain audit trail history for at least one year, with a minimum of three
months immediately available for analysis (for example, online, archived, or
restorable from backup).

This literally means that people having log_statements=ddl (useful for
SRE, not as proper audit trail) are cooked. pg_stat_statements also
potentially stores them (.save=on)... and this is backed up which even further
undermines the pg_authid/pg_shadow encryption.

Meanwhile Greg provided a patch that started this thread with:

> 1. "warn" The current default, this issues a warning if a clear
> text password is used
>
> 2. "allow" This does nothing, and thus emulates the historical behavior.
>
> 3. "disallow". This prevents the use of plain text completely, by throwing
> an error if a password set or change is attempted.

I'm +1 to this idea in general, but with the default of "allow" for now. DBAs
could temporarily switch to "warn" to detect uses of it and then later after
some time switch to "disallow" to meet harder security/compliance objectives.
But all of that is - as discussed - an even bigger security risk, because we
would have *more* passwords.

I would probably vote +1 to adding some deprecation/security note that
starting with PG-20, cleartext passwords are deprecated, but there are no
plans to remove it, then gradually maybe evaluate position; but the point is
security installations could enable opt-in.

> PGC_HUP

I would also make it somehow possible to be per-role GUC (overridable with
ALTER USER .. SET)

Naming => how about allow_cleartext_passwords=allow/warn/disallow (or deny?)?
And with the context of those attached patches, how about even
allow_alter_user_passwords? (to disable the whole thing). We have those
'allow_%' and they seem to be doing great.

Extended protocol (\bind) inital attemps
========================================

So, in [1] Robert proposed something and I've come up initially with this
kludge as starting point:

CREATE OR REPLACE FUNCTION public.set_password(uname text, pwd text)
RETURNS void

LANGUAGE plpgsql
SECURITY DEFINER

SET search_path TO 'pg_catalog', 'pg_temp'

SET
log_min_duration_statement TO '-1'

SET log_parameter_max_length TO
'0'

SET log_parameter_max_length_on_error TO '0'

SET log_min_messages
TO 'error'
SET log_error_verbosity TO 'terse'
SET log_statement TO 'none'
AS $function$
DECLARE
v_msg text;
v_query text := current_query(); -- detect non-bind
BEGIN
-- check if not using extended protocol
IF v_query != 'select set_password($1, $2) ' THEN
RAISE EXCEPTION 'do not use simple query protocol to run this';
END IF;
-- scram_sha256_verifier() not provided for clarity
EXECUTE format('ALTER USER %I PASSWORD %L',
uname, public.scram_sha256_verifier(pwd));
-- just in case silence the exception stack
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS v_msg = MESSAGE_TEXT;
RAISE EXCEPTION '%', v_msg;
END $function$;

Ofc, it misses plenty of stuff this way and was only written in a way
to prevent leaks by superuser, it literally requires running as:

BEGIN;
SET LOCAL log_parameter_max_length = 0;
SET LOCAL log_parameter_max_length_on_error = 0;
SET LOCAL log_statement = 'none';
-- yay!
SELECT public.set_password($1, $2) \bind 'user' 'pw' \g
COMMIT;

but of course that won't work for non-superusers due to lack of privs on some
of those GUCs :( Nobody is going to convince me that our userbase should use
this frankenstein command to change passwords to avoid leaking the password

Ofc, it won't fully solve problems, e.g:

ERROR: do not use simple query protocol to run this
STATEMENT: select set_password('user', 'secretpw');

PASSWORD cmd
============

So of course I had to try to see what Robert's idea could look like (0002, btw
he is not to be blamed for this attached slop, and he's not aware of what
I'm trying to do here at all )

To use/test it, just turn on all password logging you can think of, and try to
use \password from psql and ALTER USER .. PASSWORD 'cleartext' and see what
happens. Or maybe use this as base to come up with something better.

0002..0005 are probably full of slop, security bugs and so on, perhaps
concentrate on the UX or outcomes ? To me it behaves OK-ish or at least the
least worst option among all the bad ones (including current state).
It won't cure
all the leaks for sure, but apparently nothing will (even with 0003/0004,
this thing comes to the mind):
DO $$ BEGIN EXECUTE format('ALTER USER import PASSWORD ''new'''); END; $$;

As such 0004 and 0005 are marked to be doomed already probably, because as
Tom provided a good argument in [3][4] this opens some precedent, but there
seems to be no other way(?) Well, we could at least build some new contrib/
extension that would do that, but add plain warnings there that this is
not fully 100% secure way to do this (because it is impossible).

Some open questions:

- still some old psql client could connect and issue plaintext/even hashed
password, so without 0003/0004 we stand no chances. Maybe we could still
try to do damage control, but add some documentation that logs shall be
protected anyway, because they still may contain sensitive password and those
are not security issues (?)

- attached patches have of course no backwards compatibility (e.g. newer
libpq's PQchangePassword() reaching an older server would fail because it
would send them an unknown 'PASSWORD' command)

- maybe command fit is after all wrong idea? How about a new dedicated protocol
message type dedicated to just changing passwords (???)

- or maybe reworking ALTER TABLE from being a utility statement to a parsable
statement[4], but that sounded way too challenging for myself even in theory
(risk of introducing bugs, etc)

-J.

[1] - https://www.postgresql.org/message-id/CA%2BTgmoYDtt0TR-9B4sMFRGYzompcu-Ph9k7nM3gu7hTPrWWYgQ%40mail.gmail.com
[2] - https://www.postgresql.org/message-id/4061037.1741027647%40sss.pgh.pa.us
[3] - https://www.postgresql.org/message-id/3139897.1740157064%40sss.pgh.pa.us
[4] - https://www.postgresql.org/message-id/1601051.1740499733%40sss.pgh.pa.us