From: Tom Lane <tgl@sss.pgh.pa.us>
To: =?iso-8859-1?Q?F=E4rber=2C_Franz-Josef_=28StMUK=29?=
 <Franz-Josef.Faerber@stmuk.bayern.de>
cc: Noah Misch <noah@leadboat.com>, pgsql-bugs@lists.postgresql.org
Subject: Re: Extension pg_trgm, permissions and pg_dump order
In-reply-to: <f8a4105f076544c180a87ef0c4822352@stmuk.bayern.de>
References: <f8a4105f076544c180a87ef0c4822352@stmuk.bayern.de>
Comments: In-reply-to =?iso-8859-1?Q?F=E4rber=2C_Franz-Josef_=28StMUK=29?=
 <Franz-Josef.Faerber@stmuk.bayern.de>
	message dated "Wed, 25 May 2022 12:55:36 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1961550.1653498155.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Wed, 25 May 2022 13:02:35 -0400
Message-ID: <1961551.1653498155@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1961551.1653498155%40sss.pgh.pa.us>
Precedence: bulk

[ redirecting to -bugs ]

=3D?iso-8859-1?Q?F=3DE4rber=3D2C_Franz-Josef_=3D28StMUK=3D29?=3D <Franz-Jo=
sef.Faerber@stmuk.bayern.de> writes:
> My minimal example goes like this: On the fresh container, execute

> ```sql
> CREATE ROLE limitedrole;
> CREATE SCHEMA ext_trgm;
> CREATE EXTENSION pg_trgm SCHEMA ext_trgm;
> GRANT USAGE ON SCHEMA ext_trgm TO limitedrole;

> SET ROLE limitedrole;
> CREATE TABLE x(y text);
> CREATE INDEX ON x USING gist(y ext_trgm.gist_trgm_ops);
> ```

> Dump the database with `pg_dump > /tmp/x`, then do
> ```sql
> DROP SCHEMA ext_trgm CASCADE; DROP TABLE x;
> ```
> (or alternatively create a fresh database and do a ` CREATE ROLE limited=
role;`)

> Then try to restore the dump with `cat /tmp/x | psql`.

> On version 14.2, this succeeds.
> On version 14.3, this fails with "ERROR:  permission denied for schema e=
xt_trgm".

I think what is happening here is that parse analysis of the index
expression is now being done as the owner of the table (already assigned
as limitedrole), as a consequence of the patch for CVE-2022-1552.

So basically, that patch has completely broken pg_dump's scheme for
when it can issue GRANTs.  I'm not sure there is a simple fix :-(.
We could issue the GRANTs earlier but that is going to break some
other use-cases, if memory serves.

			regards, tom lane