Is it expected that "grant * on database" grants are dumped only by `pg_dumpall -s` and not by `pg_dump -s` or `pg_dumpall -g`?
Some people might assume that to restore a cluster it should be sufficient to restore pg_dumpall globals output followed by individual pg_dump output. Seemingly, this would not be a good assumption, unless plain `pg_dump` actually incorporates these grants even though `pg_dump -s` does not. Regardless, something about this situation seems off to me. I'm using 9.5, BTW.
I've seen this discussed here: https://www.postgresql.org/message-id/E1VYMqi-0001P4-P4%40wrigleys.postgresql.org, but the discussion petered out prematurely.
I have to agree. At worse this is a documentation bug but I do think we have an actual oversight here - although probably not exactly this or the linked bug report.
Testing this out a bit on 9.5 Ubuntu 14.04 - I believe the last command, <pg_dump -C -s testdb>, is in error.
<<SQL
create user testuser;
create database testdb;
grant create on database testdb to testuser;
$ pg_dumpall
[...]
CREATE ROLE testuser;
ALTER ROLE testuser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
CREATE DATABASE testdb WITH TEMPLATE = template0 OWNER = vagrant;
GRANT CREATE ON DATABASE testdb TO testuser;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
[...]
$ pg_dumpall -g
[...]
CREATE ROLE testuser;
ALTER ROLE testuser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
-------NO CREATE DATABASE (ok, db definitions are not globals)
-------NO GRANT STATEMENTS (since we don't create the DB it doesn't make sense to perform grants on it - might not even have the same name when restored)
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
[...]
$ pg_dump -s testdb
[...]
-------NO CREATE DATABASE (OK - didn't ask for one)
-------NO GRANT STATEMENTS (I guess, let whatever is presently in place rule - basically the same as pg_dumpall -g)
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
[...]
$ pg_dump -C -s testdb
[...]
CREATE DATABASE testdb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--NO GRANT STATEMENTS (If we create the DB we should also be instantiating the GRANTs, like we do in pg_dumpall)
--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;
[...]
SQL
David J.
