From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | "Murphy, Kevin" <MURPHYKE(at)email(dot)chop(dot)edu> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Question about "grant create on database" and pg_dump/pg_dumpall |
Date: | 2016-06-30 19:49:56 |
Message-ID: | CAKFQuwbcrD4i=t+6HEuBwU+D9JyK1rc9bPMA4okSny50KJ93Ng@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Venkata Balaji N | 2016-07-01 03:21:31 | Re: Replication with non-read-only standby. |
Previous Message | Jeff Janes | 2016-06-30 19:19:09 | Re: Log archiving failing. Seems to be wrong timeline |