| From: | Álvaro Rodríguez <alvaro(at)datadoghq(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | Javier Maellas <javier(dot)maellas(at)datadoghq(dot)com>, Diego Revenga <diego(dot)revengagonzalez(at)datadoghq(dot)com> |
| Subject: | pg_dumpall can't be restored with different bootstrap superuser |
| Date: | 2026-05-05 10:51:44 |
| Message-ID: | CA+C_kKWHMP4c56jx1BPvP1jmjp2pmBu0Cw07fPVECUmkJSnT4w@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
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=postgres -e POSTGRES_PASSWORD=postgres \
-p 5432:5432 postgres:18
docker run -d --name pg18-notpostgres \
-e POSTGRES_USER=notpostgres -e POSTGRES_PASSWORD=notpostgres \
-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,
Álvaro Rodríguez
| Attachment | Content-Type | Size |
|---|---|---|
| dumpall.sql | application/octet-stream | 1002 bytes |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Euler Taveira | 2026-05-05 18:23:01 | Re: pg_dumpall can't be restored with different bootstrap superuser |
| Previous Message | Euler Taveira | 2026-05-05 02:36:54 | Re: BUG #19470: PostgreSQL backend aborts (assert failure) when a prepared statement returns a composite type cast t |