Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Thu, Jun 30, 2016 at 1:38 PM, Murphy, Kevin <MURPHYKE@xxxxxxxxxxxxxx> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux