On Thu, Jun 30, 2016 at 1:38 PM, Murphy, Kevin <murph...@email.chop.edu> wrote:
> 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.
