Re: Question about "grant create on database" and pg_dump/pg_dumpall

On Thu, Jun 30, 2016 at 1:38 PM, Murphy, Kevin <MURPHYKE(at)email(dot)chop(dot)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.