Inconsistencies restoring public schema ownership from pg_dump

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

Responses

Browse pgsql-bugs by date

  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