Extension pg_trgm, permissions and pg_dump order
(unknown)
From: Färber, Franz-Josef (StMUK) <Franz-Josef.Faerber@stmuk.bayern.de>
To: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Date: 2022-05-25T12:55:36Z
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
Commits
-
CREATE INDEX: use the original userid for more ACL checks.
- 88b39e61486a 10.22 landed
- 6d49cc28613b 11.17 landed
- 93731d549e15 12.12 landed
- 8782ce49e4d0 13.8 landed
- ace9973867c2 14.5 landed
- 00377b9a02b8 15.0 landed