Extension pg_trgm, permissions and pg_dump order - Mailing list pgsql-general

From Färber, Franz-Josef (StMUK)
Subject Extension pg_trgm, permissions and pg_dump order
Date
Msg-id f8a4105f076544c180a87ef0c4822352@stmuk.bayern.de
Whole thread Raw
List pgsql-general

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 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 limitedrole;`)

 

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 limitedrole;” comes after “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ärber

 

pgsql-general by date:

Previous
From: Christoph Moench-Tegeder
Date:
Subject: Re: existing row not found by SELECT ... WHERE CTID = ?
Next
From: Laurenz Albe
Date:
Subject: Re: existing row not found by SELECT ... WHERE CTID = ?