From: | Chris Pacejo <cpacejo(at)clearskydata(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Inconsistencies restoring public schema ownership from pg_dump |
Date: | 2018-05-15 16:14:22 |
Message-ID: | CAC8iE5ibBidOjg=x==byUV7YtXCULJ3P2OWiguH3Qp=feEPSmg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2018-05-15 16:52:12 | Re: Cannot create an aggregate function with variadic parameters and enabled for parallel execution |
Previous Message | AYahorau | 2018-05-15 15:36:07 | pg_ctl -D PGDATA stop -m fast gets the following message 57P03 FATAL: the database system is shutting down |