public inbox for [email protected]
help / color / mirror / Atom feedFrom: Tom Lane <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: Hüseyin Demir <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
Date: Mon, 22 Jun 2026 14:30:54 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAKAnmmLYXcBSV8i5MVuiwu8tkX-JDGmZtJ38_YbdUTwoDW7xKg@mail.gmail.com>
<[email protected]>
<CAB5wL7bo-7okBCJ24fdVn4UhnuPLPG22eQjaLJZ9GJtv0FEWMg@mail.gmail.com>
<CAB5wL7bunPCi93Bwi7zN_1gFAZhKLwS=Fsub91SW417xujGtNA@mail.gmail.com>
<CAB5wL7aKGFFEF1SM54GxvaQPH=7Bs=HuUCqi7F4Ve025+Z950A@mail.gmail.com>
<CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAB5wL7ZuFwu2Sd-xmn-w5-BbrKk_Mu_1ubYow_qaqFGhJOiMXw@mail.gmail.com>
<CAB5wL7ai8AiaFV6B8J=w6vK1Q6Z9Hmp_y3PFL2Zzpx6Y8xPZ2A@mail.gmail.com>
<[email protected]>
Laurenz Albe <[email protected]> writes:
> Since there have been very few reports of this problem, the question
> remains if we need this patch at all, or of it should be backpatched.
> My opinion is that it should; every upgrade or restore failure is
> one too many.
I have a more pressing concern: has any performance testing been
done on this? It looks like it'd be absolutely catastrophic for
pg_dump performance on databases with lots of objects.
The implementation direction I'd been vaguely imagining was for
pg_dump's buildACLCommands() to drop any AclItems that contain
dangling role references (ie, numeric OIDs where a role name
should be). If the given role name contains any non-digit
characters then it's certainly not dangling, so most of the time
this'd be a very cheap check. However, if somebody does
CREATE USER "007";
GRANT ALL ON TABLE mi6_operations TO "007";
we mustn't get fooled by that. The backend is doing us no favors by
not making numeric OIDs visibly different from all-digit role names
in AclItems. In HEAD I'd advocate fixing that on the server side
(as attached), but we can't assume that a back-branch server has such
a fix. What we could do with an old server is issue a query (once per
pg_dump run) to collect all the valid all-digit role names, which
should surely be a short list in most databases, and then filter
against that within buildACLCommands().
regards, tom lane
Attachments:
[text/x-diff] quote-all-digit-role-names-in-aclitemout.patch (451B, ../[email protected]/2-quote-all-digit-role-names-in-aclitemout.patch)
download | inline diff:
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 01caa12eca7..84803351f18 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -235,6 +235,10 @@ putid(char *p, const char *s)
break;
}
}
+ /* If name is all-digits, quote it to distinguish from lookup failure */
+ if (safe && strspn(s, "0123456789") == strlen(s))
+ safe = false;
+
if (!safe)
*p++ = '"';
for (src = s; *src; src++)
view thread (27+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox