MIME-Version: 1.0
References: <CA+C_kKWHMP4c56jx1BPvP1jmjp2pmBu0Cw07fPVECUmkJSnT4w@mail.gmail.com>
 <fb3f446c-3a5e-40c0-82da-18ce6b81926b@app.fastmail.com>
In-Reply-To: <fb3f446c-3a5e-40c0-82da-18ce6b81926b@app.fastmail.com>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tue, 5 May 2026 11:59:48 -0700
Message-ID: <CAKFQuwZYVes3zgoDU=FXxQxNnR8A1D4wtge8n0wnj9kUeLpcZA@mail.gmail.com>
Subject: Re: pg_dumpall can't be restored with different bootstrap superuser
To: Euler Taveira <euler@eulerto.com>
Cc: =?UTF-8?B?w4FsdmFybyBSb2Ryw61ndWV6?= <alvaro@datadoghq.com>, 
	pgsql-bugs@lists.postgresql.org, 
	Javier Maellas <javier.maellas@datadoghq.com>, 
	Diego Revenga <diego.revengagonzalez@datadoghq.com>
Content-Type: multipart/alternative; boundary="000000000000e71353065116a879"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwZYVes3zgoDU%3DFXxQxNnR8A1D4wtge8n0wnj9kUeLpcZA%40mail.gmail.com>
Precedence: bulk

--000000000000e71353065116a879
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, May 5, 2026 at 11:23=E2=80=AFAM Euler Taveira <euler@eulerto.com> w=
rote:

> On Tue, May 5, 2026, at 7:51 AM, =C3=81lvaro Rodr=C3=ADguez wrote:
> >
> > We have hit an issue with pg_dumpall --roles-only where the role grants
> > to other roles can't be reapplied in a clean database, if the bootstrap
> > superuser does not have the same name in both databases.
> >
>
> This is not a bug.


> Maybe we should
> add a sentence saying that GRANT on roles requires the same bootstrap use=
r.
>
>
This does seem to contradict the claim in create role:
SUPERUSER
These clauses determine whether the new role is a =E2=80=9Csuperuser=E2=80=
=9D, who can
override all access restrictions within the database.

This at least feels like an access restriction being applied to a
superuser.  IIUC, the reason the bootstrap superuser doesn't get this
applied is because as owner of all roles in a system they alone can bypass
the "with admin" privilege check.

This may not be a bug in the code but it seems a reasonable indicator that
our documentation hasn't imparted a solid mental model as to how this is
supposed to behave in the new, more locked down, regime.

I wouldn't object to giving pg_dumpall a --bootstrap-name parameter though,
to avoid having to tell people to perform string munging on its output.  We
already have a --no-owner option to pg_dump, this doesn't seem all that
different. (Or --no-granted-by-on-role-grants ?) (Or make --no-owner on
pg_dumpall apply here.)

David J.

--000000000000e71353065116a879
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div class=3D"gmail_default" style=3D"fon=
t-family:arial,helvetica,sans-serif"><span style=3D"font-family:Arial,Helve=
tica,sans-serif">On Tue, May 5, 2026 at 11:23=E2=80=AFAM Euler Taveira &lt;=
<a href=3D"mailto:euler@eulerto.com">euler@eulerto.com</a>&gt; wrote:</span=
></div></div><div class=3D"gmail_quote gmail_quote_container"><blockquote c=
lass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px soli=
d rgb(204,204,204);padding-left:1ex">On Tue, May 5, 2026, at 7:51 AM, =C3=
=81lvaro Rodr=C3=ADguez wrote:<br>
&gt;<br>
&gt; We have hit an issue with pg_dumpall --roles-only where the role grant=
s <br>
&gt; to other roles can&#39;t be reapplied in a clean database, if the boot=
strap <br>
&gt; superuser does not have the same name in both databases.<br>
&gt;<br>
<br>
This is not a bug. </blockquote><div>=C2=A0</div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex">Maybe we should<br>
add a sentence saying that GRANT on roles requires the same bootstrap user.=
<br><br></blockquote><div><br></div><div class=3D"gmail_default" style=3D"f=
ont-family:arial,helvetica,sans-serif">This does seem to contradict the cla=
im in create role:</div><div class=3D"gmail_default" style=3D"font-family:a=
rial,helvetica,sans-serif">SUPERUSER</div><div class=3D"gmail_default" styl=
e=3D"font-family:arial,helvetica,sans-serif">These clauses determine whethe=
r the new role is a =E2=80=9Csuperuser=E2=80=9D, who can override all acces=
s restrictions within the database.</div><div class=3D"gmail_default" style=
=3D"font-family:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_d=
efault" style=3D"font-family:arial,helvetica,sans-serif">This at least=C2=
=A0feels like an access restriction being applied to a superuser.=C2=A0 IIU=
C, the reason the bootstrap superuser doesn&#39;t get this applied is becau=
se as owner of all roles in a system they alone can bypass the &quot;with a=
dmin&quot; privilege check.</div><div class=3D"gmail_default" style=3D"font=
-family:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_default" =
style=3D"font-family:arial,helvetica,sans-serif">This may not be a bug in t=
he code but it seems a reasonable indicator that our documentation hasn&#39=
;t imparted a solid mental model as to how this is supposed to behave in th=
e new, more locked down, regime.</div><div class=3D"gmail_default" style=3D=
"font-family:arial,helvetica,sans-serif"><br></div><div class=3D"gmail_defa=
ult" style=3D"font-family:arial,helvetica,sans-serif">I wouldn&#39;t object=
 to giving pg_dumpall a --bootstrap-name parameter though, to avoid having =
to tell people to perform string munging on its output.=C2=A0 We already ha=
ve a --no-owner option to pg_dump, this doesn&#39;t seem all that different=
. (Or --no-granted-by-on-role-grants ?) (Or make --no-owner on pg_dumpall a=
pply here.)</div><div class=3D"gmail_default" style=3D"font-family:arial,he=
lvetica,sans-serif"><br></div><div class=3D"gmail_default" style=3D"font-fa=
mily:arial,helvetica,sans-serif">David J.</div><div class=3D"gmail_default"=
 style=3D"font-family:arial,helvetica,sans-serif"><br></div></div></div>

--000000000000e71353065116a879--