Extension pg_trgm, permissions and pg_dump order

2022-05-25 Thread StMUK 
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

Extension has owner

2022-02-01 Thread StMUK 
Hello,

I discovered that extensions have owners (pg_extension.extowner).

The pgadmin4 schema diff ... well er, kind of tried to report to me that the 
owners differ (https://redmine.postgresql.org/issues/7145).

This seems to be badly supported:

· postgres does not seem to have support for something like ALTER 
EXTERNSION  OWNER TO ...

· pg_dump does not dump the owner.

Is this a bug? At least it is uncomfortable for me currently. Is any further 
support to change, export or otherwise handle extension owners planned?


Tested on a fresh postgres:14 docker container.


Regards,
Franz-Josef Färber