MIME-Version: 1.0
From: =?UTF-8?B?w4FsdmFybyBSb2Ryw61ndWV6?= <alvaro@datadoghq.com>
Date: Tue, 5 May 2026 12:51:44 +0200
Message-ID: <CA+C_kKWHMP4c56jx1BPvP1jmjp2pmBu0Cw07fPVECUmkJSnT4w@mail.gmail.com>
Subject: pg_dumpall can't be restored with different bootstrap superuser
To: pgsql-bugs@lists.postgresql.org
Cc: Javier Maellas <javier.maellas@datadoghq.com>, 
	Diego Revenga <diego.revengagonzalez@datadoghq.com>
Content-Type: multipart/mixed; boundary="000000000000fb3f6406510fd515"
Archived-At: <https://www.postgresql.org/message-id/CA%2BC_kKWHMP4c56jx1BPvP1jmjp2pmBu0Cw07fPVECUmkJSnT4w%40mail.gmail.com>
Precedence: bulk

--000000000000fb3f6406510fd515
Content-Type: multipart/alternative; boundary="000000000000fb3f6306510fd513"

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

Hi there,

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. The problem is
that dumpall generates a statement such as this:

GRANT a TO b WITH INHERIT TRUE GRANTED BY postgres;

However, if postgres is merely a superuser and not the bootstrap superuser,
this fails because, from the docs, "The role recorded as the grantor must
have ADMIN OPTION on the target role, unless it is the bootstrap
superuser".

*Environment*
  - PostgreSQL 18.3 (Debian 18.3-1.pgdg13+1), official postgres:18 Docker
image
  - Host: macOS (Darwin 25.4.0), Docker 29.4.1

*Steps to reproduce*
  1. Start two Postgres 18 containers with different superusers:
  docker run -d --name pg18-postgres \
    -e POSTGRES_USER=3Dpostgres -e POSTGRES_PASSWORD=3Dpostgres \
    -p 5432:5432 postgres:18

  docker run -d --name pg18-notpostgres \
    -e POSTGRES_USER=3Dnotpostgres -e POSTGRES_PASSWORD=3Dnotpostgres \
    -p 5433:5432 postgres:18
  2. On the source container, create two roles and grant one to the other:
  docker exec pg18-postgres psql -U postgres \
    -c "CREATE ROLE role_a;" \
    -c "CREATE ROLE role_b;" \
    -c "GRANT role_a TO role_b;"
  3. Dump roles only and save to a file (attached):
  docker exec pg18-postgres pg_dumpall -U postgres --roles-only > dumpall
.sql
  4. Replay the dump against the second container:
  cat dumpall.sql | docker exec -i pg18-notpostgres psql -U notpostgres

Expected: restore succeeds
Actual:
  ERROR:  permission denied to grant privileges as role "postgres"
  DETAIL:  The grantor must have the ADMIN option on role "role_a".

Thank you,
=C3=81lvaro Rodr=C3=ADguez

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

<div dir=3D"ltr">Hi there,<br><br>We have hit an issue with pg_dumpall --ro=
les-only where the role grants to other roles can&#39;t be reapplied in a c=
lean database, if the bootstrap superuser does not have the same name in bo=
th databases. The problem is that dumpall generates a statement such as thi=
s:<br><br><font face=3D"monospace">GRANT a TO b WITH INHERIT TRUE GRANTED B=
Y postgres;<br></font><div><br></div><div>However, if postgres is merely a =
superuser and not the bootstrap superuser, this fails because, from the doc=
s, &quot;The role recorded as the grantor must have ADMIN OPTION on the tar=
get role, unless it is the bootstrap superuser&quot;.=C2=A0</div><div><br><=
/div><div><u>Environment</u></div><div>=C2=A0 - PostgreSQL 18.3 (Debian 18.=
3-1.pgdg13+1), official postgres:18 Docker image<br>=C2=A0 - Host: macOS (D=
arwin 25.4.0), Docker 29.4.1</div><div><br></div><div><u>Steps to reproduce=
</u></div><div>=C2=A0 1. Start two Postgres 18 containers with different su=
perusers:<br><font face=3D"monospace">=C2=A0 docker run -d --name pg18-post=
gres \<br>=C2=A0 =C2=A0 -e POSTGRES_USER=3Dpostgres -e POSTGRES_PASSWORD=3D=
postgres \<br>=C2=A0 =C2=A0 -p 5432:5432 postgres:18<br><br>=C2=A0 docker r=
un -d --name pg18-notpostgres \<br>=C2=A0 =C2=A0 -e POSTGRES_USER=3Dnotpost=
gres -e POSTGRES_PASSWORD=3Dnotpostgres \<br>=C2=A0 =C2=A0 -p 5433:5432 pos=
tgres:18</font><br>=C2=A0 2. On the source container, create two roles and =
grant one to the other:<br><font face=3D"monospace">=C2=A0 docker exec pg18=
-postgres psql -U postgres \<br>=C2=A0 =C2=A0 -c &quot;CREATE ROLE role_a;&=
quot; \<br>=C2=A0 =C2=A0 -c &quot;CREATE ROLE role_b;&quot; \<br>=C2=A0 =C2=
=A0 -c &quot;GRANT role_a TO role_b;&quot;</font><br>=C2=A0 3. Dump roles o=
nly and save to a file (attached):<br><font face=3D"monospace">=C2=A0 docke=
r exec pg18-postgres pg_dumpall -U postgres --roles-only &gt;=C2=A0</font><=
span style=3D"font-family:monospace">dumpall</span><font face=3D"monospace"=
>.sql<br></font>=C2=A0 4. Replay the dump against the second container:<br>=
<font face=3D"monospace">=C2=A0 cat dumpall.sql | docker exec -i pg18-notpo=
stgres psql -U notpostgres</font><u></u></div><div><br></div><div>Expected:=
 restore succeeds</div><div>Actual:=C2=A0 =C2=A0</div><div><font face=3D"mo=
nospace">=C2=A0 ERROR: =C2=A0permission denied to grant privileges as role =
&quot;postgres&quot;</font></div><font face=3D"monospace">=C2=A0 DETAIL: =
=C2=A0The grantor must have the ADMIN option on role &quot;role_a&quot;.</f=
ont><div><font face=3D"monospace"><br></font></div><div>Thank you,</div><di=
v>=C3=81lvaro Rodr=C3=ADguez<br></div></div>

--000000000000fb3f6306510fd513--
--000000000000fb3f6406510fd515
Content-Type: application/octet-stream; name="dumpall.sql"
Content-Disposition: attachment; filename="dumpall.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mosi9plu0>
X-Attachment-Id: f_mosi9plu0

LS0KLS0gUG9zdGdyZVNRTCBkYXRhYmFzZSBjbHVzdGVyIGR1bXAKLS0KClxyZXN0cmljdCB6SE9X
cGVySDBlazNhRTN1T3FJeG9yMnZXRXNWd2JBT2xNaHVBYTltYzh3TEczWEhoMzdMd1VIWW1aSklP
OVkKClNFVCBkZWZhdWx0X3RyYW5zYWN0aW9uX3JlYWRfb25seSA9IG9mZjsKClNFVCBjbGllbnRf
ZW5jb2RpbmcgPSAnVVRGOCc7ClNFVCBzdGFuZGFyZF9jb25mb3JtaW5nX3N0cmluZ3MgPSBvbjsK
Ci0tCi0tIFJvbGVzCi0tCgpDUkVBVEUgUk9MRSBwb3N0Z3JlczsKQUxURVIgUk9MRSBwb3N0Z3Jl
cyBXSVRIIFNVUEVSVVNFUiBJTkhFUklUIENSRUFURVJPTEUgQ1JFQVRFREIgTE9HSU4gUkVQTElD
QVRJT04gQllQQVNTUkxTIFBBU1NXT1JEICdTQ1JBTS1TSEEtMjU2JDQwOTY6elMwQThRSTROUjNq
R1RwWVNlRk43dz09JFpnSjhsdER2a253YlBQZDlpNWxrUy9kQ1JqK2NhNm9GMTB0bzNVZWExNzg9
OmxpV2VYWEhFYjJkYTFSYmZRZERneElUbmRQc2RYRGlKTXJ0SmJKSnY5S3M9JzsKQ1JFQVRFIFJP
TEUgcm9sZV9hOwpBTFRFUiBST0xFIHJvbGVfYSBXSVRIIE5PU1VQRVJVU0VSIElOSEVSSVQgTk9D
UkVBVEVST0xFIE5PQ1JFQVRFREIgTk9MT0dJTiBOT1JFUExJQ0FUSU9OIE5PQllQQVNTUkxTOwpD
UkVBVEUgUk9MRSByb2xlX2I7CkFMVEVSIFJPTEUgcm9sZV9iIFdJVEggTk9TVVBFUlVTRVIgSU5I
RVJJVCBOT0NSRUFURVJPTEUgTk9DUkVBVEVEQiBOT0xPR0lOIE5PUkVQTElDQVRJT04gTk9CWVBB
U1NSTFM7CgotLQotLSBVc2VyIENvbmZpZ3VyYXRpb25zCi0tCgoKLS0KLS0gUm9sZSBtZW1iZXJz
aGlwcwotLQoKR1JBTlQgcm9sZV9hIFRPIHJvbGVfYiBXSVRIIElOSEVSSVQgVFJVRSBHUkFOVEVE
IEJZIHBvc3RncmVzOwoKCgoKXHVucmVzdHJpY3QgekhPV3BlckgwZWszYUUzdU9xSXhvcjJ2V0Vz
VndiQU9sTWh1QWE5bWM4d0xHM1hIaDM3THdVSFltWkpJTzlZCgotLQotLSBQb3N0Z3JlU1FMIGRh
dGFiYXNlIGNsdXN0ZXIgZHVtcCBjb21wbGV0ZQotLQoK
--000000000000fb3f6406510fd515--