Inconsistencies restoring public schema ownership from pg_dump
От | Chris Pacejo |
---|---|
Тема | Inconsistencies restoring public schema ownership from pg_dump |
Дата | |
Msg-id | CAC8iE5ibBidOjg=x==byUV7YtXCULJ3P2OWiguH3Qp=feEPSmg@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Inconsistencies restoring public schema ownership from pg_dump
|
Список | pgsql-bugs |
Hi all. pg_dump (10.3) does not seem to correctly handle restoring ownership of the "public" schema if it has been changed from the default of "postgres". Consider a database created as follows: postgres=# CREATE ROLE admin; postgres=# CREATE DATABASE foo WITH OWNER=admin TEMPLATE=template0; postgres=# \c foo foo=# ALTER SCHEMA public OWNER TO admin; foo=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+-------+-------------------+------------------------ public | admin | admin=UC/admin +| standard public schema | | =UC/admin | A straight `pg_dump` (incorrectly) attempts to restore this configuration with the following commands: REVOKE ALL ON SCHEMA public FROM postgres; REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO admin; GRANT ALL ON SCHEMA public TO PUBLIC; This of course leaves the schema owned by "postgres": foo=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+----------+-------------------+------------------------ public | postgres | admin=UC/postgres+| standard public schema | | =UC/postgres | `pg_dump -C` is no better: CREATE DATABASE foo WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; ALTER DATABASE foo OWNER TO admin; \connect foo REVOKE ALL ON SCHEMA public FROM postgres; REVOKE ALL ON SCHEMA public FROM PUBLIC; GRANT ALL ON SCHEMA public TO admin; GRANT ALL ON SCHEMA public TO PUBLIC; `pg_dump -c` restores the "public" schema ownership correctly, but `pg_dump -c` is rarely useful to me (on a fresh installation, the DROPs produce excessive errors; on an existing installation, it fails to drop objects which may have been added since the dump): DROP SCHEMA public; CREATE SCHEMA public; ALTER SCHEMA public OWNER TO admin; COMMENT ON SCHEMA public IS 'standard public schema'; GRANT ALL ON SCHEMA public TO PUBLIC; `pg_dump -c -C` is the worst of the bunch, not even restoring the "public" schema's ACL: DROP DATABASE foo; CREATE DATABASE foo WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8'; ALTER DATABASE foo OWNER TO admin; \connect foo GRANT ALL ON SCHEMA public TO PUBLIC; foo=# \dn+ List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | postgres | postgres=UC/postgres+| standard public schema | | =UC/postgres | I would expect all of these to produce output comparable to that of `pg_dump -c`; that is, assume that (or instruct that) the database is created from "template0" (so, "public" exists, owned by "postgres", with ALL granted to PUBLIC), and adjust *both* ownership *and* the ACL of the "public" schema to match exactly the state of the database. Am I misinterpreting something here or is this a bug? Thanks, Chris
В списке pgsql-bugs по дате отправления: