public inbox for [email protected]
help / color / mirror / Atom feedpg_dumpall can't be restored with different bootstrap superuser
6+ messages / 5 participants
[nested] [flat]
* pg_dumpall can't be restored with different bootstrap superuser
@ 2026-05-05 10:51 Álvaro Rodríguez <[email protected]>
2026-05-05 18:23 ` Re: pg_dumpall can't be restored with different bootstrap superuser Euler Taveira <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Álvaro Rodríguez @ 2026-05-05 10:51 UTC (permalink / raw)
To: [email protected]; +Cc: Javier Maellas <[email protected]>; Diego Revenga <[email protected]>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dumpall can't be restored with different bootstrap superuser
2026-05-05 10:51 pg_dumpall can't be restored with different bootstrap superuser Álvaro Rodríguez <[email protected]>
@ 2026-05-05 18:23 ` Euler Taveira <[email protected]>
2026-05-05 18:59 ` Re: pg_dumpall can't be restored with different bootstrap superuser David G. Johnston <[email protected]>
2026-05-05 19:10 ` Re: pg_dumpall can't be restored with different bootstrap superuser Tom Lane <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Euler Taveira @ 2026-05-05 18:23 UTC (permalink / raw)
To: Álvaro Rodríguez <[email protected]>; [email protected]; +Cc: Javier Maellas <[email protected]>; Diego Revenga <[email protected]>
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dumpall can't be restored with different bootstrap superuser
2026-05-05 10:51 pg_dumpall can't be restored with different bootstrap superuser Álvaro Rodríguez <[email protected]>
2026-05-05 18:23 ` Re: pg_dumpall can't be restored with different bootstrap superuser Euler Taveira <[email protected]>
@ 2026-05-05 18:59 ` David G. Johnston <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2026-05-05 18:59 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: Álvaro Rodríguez <[email protected]>; [email protected]; Javier Maellas <[email protected]>; Diego Revenga <[email protected]>
On Tue, May 5, 2026 at 11:23 AM Euler Taveira <[email protected]> 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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dumpall can't be restored with different bootstrap superuser
2026-05-05 10:51 pg_dumpall can't be restored with different bootstrap superuser Álvaro Rodríguez <[email protected]>
2026-05-05 18:23 ` Re: pg_dumpall can't be restored with different bootstrap superuser Euler Taveira <[email protected]>
@ 2026-05-05 19:10 ` Tom Lane <[email protected]>
2026-05-05 19:40 ` Re: pg_dumpall can't be restored with different bootstrap superuser Nathan Bossart <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2026-05-05 19:10 UTC (permalink / raw)
To: Euler Taveira <[email protected]>; +Cc: Álvaro Rodríguez <[email protected]>; [email protected]; Javier Maellas <[email protected]>; Diego Revenga <[email protected]>; Nathan Bossart <[email protected]>
"Euler Taveira" <[email protected]> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dumpall can't be restored with different bootstrap superuser
2026-05-05 10:51 pg_dumpall can't be restored with different bootstrap superuser Álvaro Rodríguez <[email protected]>
2026-05-05 18:23 ` Re: pg_dumpall can't be restored with different bootstrap superuser Euler Taveira <[email protected]>
2026-05-05 19:10 ` Re: pg_dumpall can't be restored with different bootstrap superuser Tom Lane <[email protected]>
@ 2026-05-05 19:40 ` Nathan Bossart <[email protected]>
2026-05-06 07:15 ` Re: pg_dumpall can't be restored with different bootstrap superuser Álvaro Rodríguez <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Nathan Bossart @ 2026-05-05 19:40 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Euler Taveira <[email protected]>; Álvaro Rodríguez <[email protected]>; [email protected]; Javier Maellas <[email protected]>; Diego Revenga <[email protected]>; [email protected]
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 ROLE
>
> Anyone 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dumpall can't be restored with different bootstrap superuser
2026-05-05 10:51 pg_dumpall can't be restored with different bootstrap superuser Álvaro Rodríguez <[email protected]>
2026-05-05 18:23 ` Re: pg_dumpall can't be restored with different bootstrap superuser Euler Taveira <[email protected]>
2026-05-05 19:10 ` Re: pg_dumpall can't be restored with different bootstrap superuser Tom Lane <[email protected]>
2026-05-05 19:40 ` Re: pg_dumpall can't be restored with different bootstrap superuser Nathan Bossart <[email protected]>
@ 2026-05-06 07:15 ` Álvaro Rodríguez <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Álvaro Rodríguez @ 2026-05-06 07:15 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Tom Lane <[email protected]>; Euler Taveira <[email protected]>; [email protected]; Javier Maellas <[email protected]>; Diego Revenga <[email protected]>; [email protected]
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2026-05-06 07:15 UTC | newest]
Thread overview: 6+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-05 10:51 pg_dumpall can't be restored with different bootstrap superuser Álvaro Rodríguez <[email protected]>
2026-05-05 18:23 ` Euler Taveira <[email protected]>
2026-05-05 18:59 ` David G. Johnston <[email protected]>
2026-05-05 19:10 ` Tom Lane <[email protected]>
2026-05-05 19:40 ` Nathan Bossart <[email protected]>
2026-05-06 07:15 ` Álvaro Rodríguez <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox