From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
cc: Aditya D <dsaditya91@gmail.com>,
        pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_restore -L reordering of the statements does not work
In-reply-to: <20231114231347.lmnu5q5t7yq62vga@awork3.anarazel.de>
References: 
 <CAEATte6vgZr6d2abZo9_wxTarwQP3TC_NLpCKTDzWW4E4OB9cw@mail.gmail.com>
 <1589825.1698431686@sss.pgh.pa.us>
 <20231114194933.ci3nxol3avtisyrs@awork3.anarazel.de>
 <2522830.1699994542@sss.pgh.pa.us>
 <20231114222743.x2c2r74ttgom2cfx@awork3.anarazel.de>
 <2537261.1700001602@sss.pgh.pa.us>
 <20231114231347.lmnu5q5t7yq62vga@awork3.anarazel.de>
Comments: In-reply-to Andres Freund <andres@anarazel.de>
	message dated "Tue, 14 Nov 2023 15:13:47 -0800"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2543034.1700004346.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Tue, 14 Nov 2023 18:25:46 -0500
Message-ID: <2543035.1700004346@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/2543035.1700004346%40sss.pgh.pa.us>
Precedence: bulk

Andres Freund <andres@anarazel.de> writes:
> On 2023-11-14 17:40:02 -0500, Tom Lane wrote:
>> ... no, it should be for the user executing the ALTER to have permissio=
n.

> That check succeed - what fails is a check on the new owner of the table=
. See
> tablecmds.c ATExecChangeOwner:

>                 /* New owner must have CREATE privilege on namespace */
>                 aclresult =3D object_aclcheck(NamespaceRelationId, names=
paceOid, newOwnerId,
>                                             ACL_CREATE);

Oh!  I was just thinking about the initial object-lookup check,
I'd forgotten about the one in the ALTER OWNER code itself.

Hm.  The intent of that check is to ensure that the ALTER doesn't
produce a situation that the object-recipient user couldn't have
created by himself.  But I wonder if that's too narrow-minded, and
we should craft a new rule that allows things dependent on only the
calling user's permissions.  Maybe allow if either the calling user or
the recipient has CREATE on the schema?  Or allow if calling user has
ownership on the schema (implying that he could temporarily GRANT the
necessary rights and then undo it)?  Either of those would legitimize
what pg_dump wants to do.

This is getting way off-topic for pgsql-admin, btw.

			regards, tom lane