Re: Pg14, pg_dumpall and "password_encryption=true"

On Fri, Jan 15, 2021 at 01:35:50PM +0900, Ian Lawrence Barwick wrote:
> $ 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".

Right. This can happen anytime we reduce the domain of a setting having
GucContext PGC_SU_BACKEND, PGC_BACKEND, PGC_SUSET, or PGC_USERSET.

> I see following options to resolve this issue.
>
> 1. Re-add support for a "true" as an alias for "md5".
> 2. Transparently rewrite "true" to "md5"
> 3. Have pg_dumpall map "true" to "md5"
> 4. Add an option to pg_dumpall to specify the target version

I expect rather few databases override this particular setting in
pg_proc.proconfig or pg_db_role_setting.setconfig. The restore failure has a
clear error message, which is enough. Each of the above would be overkill.

> 5. Have pg_upgrade emit a warning/hint

If done in a way not specific to this one setting, +1 for this. That is to
say, do something like the following. Retrieve every pg_proc.proconfig and
pg_db_role_setting.setconfig value from the old cluster. Invoke the new
postmaster binary to test acceptance of each value. I'm not generally a fan
of adding pg_upgrade code to predict whether the dump will fail to restore,
because such code will never be as good as just trying the restore. That
said, this checking of GUC acceptance could be self-contained and useful for
the long term.

> 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.

The release notes will document the password_encryption change, though they
probably won't specifically mention the interaction with pg_dump. I would not
document it elsewhere.