pg_dumpall can't be restored with different bootstrap superuser

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

Responses

Browse pgsql-bugs by date

  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