Pg14, pg_dumpall and "password_encryption=true"

Greetings

Consider the following:

postgres=# SELECT current_setting('server_version');
current_setting
-----------------
12.5
(1 row)

postgres=# SELECT * FROM pg_db_role_setting WHERE setrole = 10;
setdatabase | setrole | setconfig
-------------+---------+----------------------------
0 | 10 | {password_encryption=true}
(1 row)

$ $PG_14/pg_dumpall --version
pg_dumpall (PostgreSQL) 14devel

$ $PG_14/pg_dumpall -d 'port=5414' --globals-only | grep
"password_encryption"
ALTER ROLE postgres SET password_encryption TO 'true';

This command will fail in Pg14, e.g. when running pg_upgrade (which is where
I bumped into the issue):

$ pg_upgrade --whatever
...
Restoring global objects in the new cluster
*failure*

Consult the last few lines of "pg_upgrade_utility.log" for
the probable cause of the failure.
Failure, exiting

$ tail -3 pg_upgrade_utility.log
ALTER ROLE "postgres" SET "password_encryption" TO 'true';
psql:pg_upgrade_dump_globals.sql:75: ERROR: invalid value for
parameter "password_encryption": "true"
HINT: Available values: md5, scram-sha-256.

This is a consquence of commit c7eab0e97, which removed support for the
legacy
values "on"/"true"/"yes"/"1".

I see following options to resolve this issue.

1. Re-add support for a "true" as an alias for "md5".
------------------------------------------------------------

Easiest option.

The disadvantage is that in Pg10 ~ Pg13, "password_encryption=true"
implictly meant "use the default encryption method", which was "md5",
however the default in Pg14 is "scram-sha-256", so the meaning
of "true" changes to "set to the non-default method".

2. Transparently rewrite "true" to "md5"
----------------------------------------

AFAICS this isn't supported (we can remap parameter names, e.g.
sort_mem -> work_mem, but not enum values), so would require some
refactoring.

Adding a 4th field to "config_enum_entry" which, if not NULL, would cause
the setting to be stored as the specified value, e.g.:

{"true", PASSWORD_TYPE_MD5, true, "md5"},

seems like a nice way of defining it, but poking about, it looks non-trivial
to actually implement, and a lot of code for a single workaround.
I suppose, if feasible, it might be useful future, but can't think
of anywhere else it might be useful.

3. Have pg_dumpall map "true" to "md5"
--------------------------------------

This would require adding an exception for "password_encryption" (currently
all
settings are dumped as-is).

However this would result in dumps from pre-Pg10 versions which would not be
valid for restoring into those versions (assuming we support later versions
of
pg_dump/pg_dumpall being able to produce dumps from older versions which are
valid for those older versions).

4. Add an option to pg_dumpall to specify the target version
------------------------------------------------------------

As (4), but only remap if the target version is 10 or later.

And have pg_upgrade pass the target version option to pg_dumpall.

5. Have pg_upgrade emit a warning/hint
--------------------------------------

This would catch what a common occurrence of this issue.

However it's annoying for the user to have to deal with this during an
upgrade. And it still leaves Pg14's pg_dumpall at risk of creating dumps
which
are not actually valid for Pg14, which will cause issues when doing
upgrades via
logical replication etc.

6. Document this as a backwards-compatibility thing
----------------------------------------------------

Add an item in the documentation (release notes, pg_upgrade, pg_dumpall)
stating
that any occurrences of "password_encryption" which are not valid in Pg14
should
be updated before performing an upgrade.

This has the disadvantage that it's just documentation and as such at risk
of
being overlooked, time and time again.

And it still also leaves Pg14's pg_dumpall at risk of creating dumps which
are
not actually valid for Pg14.

I'll add to the next CF so this doesn't get lost.

Regards

Ian Barwick

--
EnterpriseDB: https://www.enterprisedb.com