Re: pg_upgrade check for invalid role-specific default config

Bruce Momjian <bruce(at)momjian(dot)us> writes:
> On Mon, Apr 12, 2021 at 01:28:19PM +0000, Charlie Hornsby wrote:
>> While troubleshooting a failed upgrade from v11 -> v12 I realised I had
>> encountered a bug previously reported on the pgsql-bugs mailing list:
>> #14242 Role with a setconfig "role" setting to a nonexistent role causes
>> pg_upgrade to fail
>> https://www.postgresql.org/message-id/20160711223641.1426.86096%40wrigleys.postgresql.org
>> Since it is possible to write a query to identify these cases, would there
>> be appetite for me to submit a patch to add a check for this to
>> pg_upgrade?

> Yes, I think a patch would be good, but the fix might be for pg_dump
> instead, which pg_upgrade uses.

I'm not sure I buy the premise that "it is possible to write a query
to identify these cases". It seems to me that the general problem is
that ALTER ROLE/DATABASE SET values might have become incorrect since
they were installed and would thus fail when reloaded in dump/restore.
We're not going to be able to prevent that in the general case, and
it's not obvious to me what special case might be worth going after.

I do find it interesting that we now have two reports of somebody
doing "ALTER ROLE SET role = something". In the older thread,
I was skeptical that that had any real use-case, so I wonder if
Charlie has a rationale for having done that.

regards, tom lane