From: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
To: pgsql-general@postgresql.org <pgsql-general@postgresql.org>
Subject: Extension pg_trgm, permissions and pg_dump order
Date: Wed, 25 May 2022 12:55:36 +0000
Message-ID: <f8a4105f076544c180a87ef0c4822352@stmuk.bayern.de> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: Franz-Josef.Faerber@stmuk.bayern.de, pgsql-general@postgresql.org
Subject: Re: Extension pg_trgm, permissions and pg_dump order
In-Reply-To: <f8a4105f076544c180a87ef0c4822352@stmuk.bayern.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox