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
