Extension pg_trgm, permissions and pg_dump order

From: Färber, Franz-Josef (StMUK) <Franz-Josef(dot)Faerber(at)stmuk(dot)bayern(dot)de>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Extension pg_trgm, permissions and pg_dump order
Date: 2022-05-25 12:55:36
Message-ID: f8a4105f076544c180a87ef0c4822352@stmuk.bayern.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs 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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Haas 2022-05-25 16:41:14 Re: BUG #17497: Data directory has been changed to default
Previous Message Robert Haas 2022-05-25 12:39:14 Re: BUG #17485: Records missing from Primary Key index when doing REINDEX INDEX CONCURRENTLY

Browse pgsql-general by date

  From Date Subject
Next Message Laurenz Albe 2022-05-25 13:22:39 Re: existing row not found by SELECT ... WHERE CTID = ?
Previous Message Christoph Moench-Tegeder 2022-05-25 12:27:50 Re: existing row not found by SELECT ... WHERE CTID = ?