pg_dumpall can't be restored with different bootstrap superuser
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:
On Tue, May 5, 2026, at 7:51 AM, Álvaro Rodríguez 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. There is no way that pg_dumpall knows that the bootstrap
user you want is another one. If you want to share roles and its properties
between clusters, don't use different bootstrap users. If you do so, you should
execute another step between dump and restore to replace the source bootstrap
user with the target bootstrap user or even collect the error messages and
rewrite the affected SQL commands.
I don't think an option to inject arbitrary grantor is acceptable for security
concerns. There isn't a role specification like BOOTSTRAP_USER (similar to
CURRENT_ROLE or SESSION_USER) that would avoid this situation. Maybe we should
add a sentence saying that GRANT on roles requires the same bootstrap user.
--
Euler Taveira
EDB https://www.enterprisedb.com/
On Tue, May 5, 2026 at 11:23 AM Euler Taveira <euler@eulerto.com> wrote:
On Tue, May 5, 2026, at 7:51 AM, Álvaro Rodríguez 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 user.
This does seem to contradict the claim in create role:
SUPERUSER
These clauses determine whether the new role is a “superuser”, 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.
"Euler Taveira" <euler@eulerto.com> writes:
On Tue, May 5, 2026, at 7:51 AM, Álvaro Rodríguez 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. There is no way that pg_dumpall knows that the bootstrap
user you want is another one.
I don't think that pg_dumpall is to be blamed; this is the backend's
fault. I thought we had made this better in dd1398f13, but it still
seems rather bogus:
regression=# create user super with superuser;
CREATE ROLE
regression=# create user a;
CREATE ROLE
regression=# create user b;
CREATE ROLE
regression=# grant a to b granted by super;
ERROR: permission denied to grant privileges as role "super"
DETAIL: The grantor must have the ADMIN option on role "a".
Surely a superuser should be considered to have admin options
on everything. Even more bogus, compare these results:
regression=# \c - super
You are now connected to database "regression" as user "super".
regression=# grant a to b granted by super;
ERROR: permission denied to grant privileges as role "super"
DETAIL: The grantor must have the ADMIN option on role "a".
regression=# grant a to b;
GRANT ROLE
Anyone would think that "GRANTED BY current_user" has the
same effect as omitting the clause, but here it doesn't.
So it seems to me that we're missing a superuserness check
somewhere in this, but I'm not entirely sure which bit of
code to blame.
I agree that the answer for existing branches is probably
going to be "so don't do that", but maybe we can improve
this in v19 or later.
regards, tom lane
On Tue, May 05, 2026 at 03:10:47PM -0400, Tom Lane wrote:
I don't think that pg_dumpall is to be blamed; this is the backend's
fault. I thought we had made this better in dd1398f13, but it still
seems rather bogus:[...]
regression=# grant a to b granted by super;
ERROR: permission denied to grant privileges as role "super"
DETAIL: The grantor must have the ADMIN option on role "a".Surely a superuser should be considered to have admin options
on everything.
I think this comes from commit ce6b672e44, which established the idea that
the bootstrap superuser was the "role owner".
Even more bogus, compare these results:
regression=# \c - super
You are now connected to database "regression" as user "super".
regression=# grant a to b granted by super;
ERROR: permission denied to grant privileges as role "super"
DETAIL: The grantor must have the ADMIN option on role "a".regression=# grant a to b;
GRANT ROLEAnyone would think that "GRANTED BY current_user" has the
same effect as omitting the clause, but here it doesn't.
Right. When omitted, check_role_grantor() uses the bootstrap superuser if
the current role is a superuser.
So it seems to me that we're missing a superuserness check
somewhere in this, but I'm not entirely sure which bit of
code to blame.I agree that the answer for existing branches is probably
going to be "so don't do that", but maybe we can improve
this in v19 or later.
I've added Robert to the thread for his thoughts. I'm not sure how much
wiggle room we have in the current design to make things more lenient, but
I haven't investigated too deeply yet.
--
nathan
On Tue, May 05, 2026 at 03:10:47PM -0400, Tom Lane wrote:
I don't think that pg_dumpall is to be blamed; this is the backend's
fault. I thought we had made this better in dd1398f13, but it still
seems rather bogus:[...]
regression=# grant a to b granted by super;
ERROR: permission denied to grant privileges as role "super"
DETAIL: The grantor must have the ADMIN option on role "a".Surely a superuser should be considered to have admin options
on everything.
For what it's worth, this lines up with my and my team's thinking on
this issue. The idea that there are two "tiers" of superusers
(bootstrap and the rest) seems to run against a) the general rule of
making permissions obvious and explicitly grantable, and b) the very
own definition of superuser as David pointed out. The fact that there
is no reasonable way of fixing the pg_dumpall output even if we wanted
to (bar, I guess, renaming the bootstrap superuser) seems to indicate
that something is off with the permission model on this.
Álvaro