From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Euler Taveira" <euler@eulerto.com>
cc: =?UTF-8?Q?=C3=81lvaro_Rodr=C3=ADguez?= <alvaro@datadoghq.com>,
        pgsql-bugs@lists.postgresql.org,
        "Javier Maellas" <javier.maellas@datadoghq.com>,
        "Diego Revenga" <diego.revengagonzalez@datadoghq.com>,
        Nathan Bossart <nathandbossart@gmail.com>
Subject: Re: pg_dumpall can't be restored with different bootstrap superuser
In-reply-to: <fb3f446c-3a5e-40c0-82da-18ce6b81926b@app.fastmail.com>
References: <CA+C_kKWHMP4c56jx1BPvP1jmjp2pmBu0Cw07fPVECUmkJSnT4w@mail.gmail.com> <fb3f446c-3a5e-40c0-82da-18ce6b81926b@app.fastmail.com>
Comments: In-reply-to "Euler Taveira" <euler@eulerto.com>
	message dated "Tue, 05 May 2026 15:23:01 -0300"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <671133.1778008247.1@sss.pgh.pa.us>
Content-Transfer-Encoding: 8bit
Date: Tue, 05 May 2026 15:10:47 -0400
Message-ID: <671134.1778008247@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/671134.1778008247%40sss.pgh.pa.us>
Precedence: bulk

"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