Обсуждение: Inconsistencies restoring public schema ownership from pg_dump

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

I just wanted to echo that this has also caused me problems. We just used
pg_restore and pg_dump to migrate a bunch of databases to a newer version of
postgres. Some of the databases had changed the ownership of the public
schema, and that was lost in the migration.

Maybe changing the owner of the public schema is considered a bad idea, but
our use case is that we (the postgres admins) support a group of developers
that creates many database driven apps. Most of the databases are secured,
but some are public. By letting that group own the public role (they already
own the database and all its other contents), they can decide whether to
grant or revoke permission on the public schema without granting any of them
superuser access.

Basically, what I am suggesting is that, assuming an unmodified public
schema, running these commands
pg_dump -Fc -f test.pg test
pg_restore -s test.pg | grep -i 'alter schema'

should output:
ALTER SCHEMA public OWNER TO postgres;

And of course if the owner had been changed, that would also be reflected.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html