public inbox for [email protected]  

help / color / mirror / Atom feed

From: Álvaro Rodríguez <[email protected]>
To: [email protected]
Cc: Javier Maellas <[email protected]>
Cc: Diego Revenga <[email protected]>
Subject: pg_dumpall can't be restored with different bootstrap superuser
Date: Tue, 5 May 2026 12:51:44 +0200
Message-ID: <CA+C_kKWHMP4c56jx1BPvP1jmjp2pmBu0Cw07fPVECUmkJSnT4w@mail.gmail.com> (raw)

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


Attachments:

  [application/octet-stream] dumpall.sql (1002B, 3-dumpall.sql)
  download

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]
  Subject: Re: pg_dumpall can't be restored with different bootstrap superuser
  In-Reply-To: <CA+C_kKWHMP4c56jx1BPvP1jmjp2pmBu0Cw07fPVECUmkJSnT4w@mail.gmail.com>

* 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