Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nttYI-0002gg-Ew for pgsql-general@arkaria.postgresql.org; Wed, 25 May 2022 16:08:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nttYF-0001MA-Mr for pgsql-general@arkaria.postgresql.org; Wed, 25 May 2022 16:07:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ntqYD-0000d6-7E for pgsql-general@lists.postgresql.org; Wed, 25 May 2022 12:55:45 +0000 Received: from mailout94.bayern.de ([195.200.70.94]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ntqY7-0003qN-TW for pgsql-general@postgresql.org; Wed, 25 May 2022 12:55:44 +0000 Received: from post19.bybn.de (post19.bybn.de [10.197.20.19]) by mailout94.bayern.de (8.15.2/8.15.2) with ESMTP id 24PCtcLv007579 for ; Wed, 25 May 2022 14:55:38 +0200 Received: from ZMX-EXP4-P17741.max.bayern.de (ZMX-EXP4-P17741.max.bayern.de [10.173.241.167]) by post19.bybn.de (8.14.4/8.14.4) with ESMTP id 24PCtaqH007244 for ; Wed, 25 May 2022 14:55:38 +0200 (envelope-from Franz-Josef.Faerber@stmuk.bayern.de) Received: from ZMX-EXPB-P17750.max.bayern.de (10.173.241.172) by ZMX-EXP4-P17741.max.bayern.de (10.173.241.167) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.1.2507.9; Wed, 25 May 2022 14:55:36 +0200 Received: from ZMX-EXPB-P17750.max.bayern.de (10.173.241.172) by ZMX-EXPB-P17750.max.bayern.de (10.173.241.172) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256_P256) id 15.1.2507.9; Wed, 25 May 2022 14:55:36 +0200 Received: from ZMX-EXPB-P17750.max.bayern.de ([10.173.241.172]) by ZMX-EXPB-P17750.max.bayern.de ([10.173.241.172]) with mapi id 15.01.2507.009; Wed, 25 May 2022 14:55:36 +0200 From: =?iso-8859-1?Q?F=E4rber=2C_Franz-Josef_=28StMUK=29?= To: "pgsql-general@postgresql.org" Subject: Extension pg_trgm, permissions and pg_dump order Thread-Topic: Extension pg_trgm, permissions and pg_dump order Thread-Index: AdhwMCynSJWZF4PTTnuTh8umBZxKsA== Date: Wed, 25 May 2022 12:55:36 +0000 Message-ID: Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.171.82.81] Content-Type: multipart/alternative; boundary="_000_f8a4105f076544c180a87ef0c4822352stmukbayernde_" MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_f8a4105f076544c180a87ef0c4822352stmukbayernde_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello, using the postgres:14.3 docker container, the following fails - whereas usi= ng the 14.2 container, it succeeds. A bug? 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 limitedrol= e;`) 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 ext_= trgm". Please note that in the dump, "GRANT USAGE ON SCHEMA ext_trgm TO limitedrol= e;" comes after "CREATE INDEX x_y_idx ON public.x USING gist (y ext_trgm.gi= st_trgm_ops);"; but this shouldn't matter since the restoration is called w= ith "postgres" superuser privileges!? Regards, Franz-Josef F=E4rber --_000_f8a4105f076544c180a87ef0c4822352stmukbayernde_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello,

 

using the postgres:14.3 docker = container, the following fails – whereas using the 14.2 container, it= succeeds. A bug?

 

My minimal example goes like th= is: 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; D= ROP TABLE x;

```

(or alternatively create a fres= h database and do a ` CREATE ROLE limitedrole;`)

 

Then try to restore the dump wi= th `cat /tmp/x | psql`.

 

On version 14.2, this succeeds.=

On version 14.3, this fails wit= h “ERROR:  permission denied for schema ext_trgm”.

 

Please note that in the dump, &= #8220;GRANT USAGE ON SCHEMA ext_trgm TO limitedrole;” comes after = 220;CREATE INDEX x_y_idx ON public.x USING gist (y ext_trgm.gist_trgm_ops);= ”; but this shouldn’t matter since the restoration is called with “postgres” superuser privileges!?

 

 

Regards,

Franz-Josef = F=E4rber

 <= /o:p>

--_000_f8a4105f076544c180a87ef0c4822352stmukbayernde_--