Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> What if there is a dangling role OID 65432 in pg_init_privs *and*
> a valid role with the same name (but a different OID)? Then the patch
> would tacitly restore the dangling reference to the latter role.

That is a good point, but I would put the blame on aclitemout: in such
a case it's entirely impossible for pg_dump to distinguish whether an
apparently all-numeric role name in an ACL item is the valid role or a
dangling OID.

I was tempted yesterday to propose a simpler solution in which
we back-patch the putid() fix I showed earlier, and just change
dumputils.c to drop ACLs that have unquoted all-numeric grantees.
(If the grantor part is a dangling OID, we could omit GRANTED BY, as
we did recently for role grants.) Now the problem with this is that
if you have a case like my "007" example, you're going to lose some
grants if you dump with an updated pg_dump from a not-updated server.
That cure is very likely worse than the disease.

So what I'm thinking today is we apply the putid() fix only in HEAD,
and make dumputils.c ignore unquoted all-numeric roles only if
server version >= 19. This means we don't have a fix for the actually
known problems with old server versions, which is kind of sad, but
given Laurenz's point I don't think a reliable fix is possible with
an unpatched server.

An alternative answer is to back-patch the putid() fix and teach
dumputils.c to consider the server minor version when deciding whether
to reject unquoted all-numeric roles. We don't typically make pg_dump
pay attention to minor versions, but it would provide a pathway for
users to deal with this problem: if you've got dangling grants then
update the old server before dumping.

regards, tom lane