[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: Re: [GENERAL] Question about "grant create on database" and pg_dump/pg_dumpall
From: "David G. Johnston" <david.g.johnston () gmail ! com>
Date: 2016-06-30 19:49:56
Message-ID: CAKFQuwbcrD4i=t+6HEuBwU+D9JyK1rc9bPMA4okSny50KJ93Ng () mail ! gmail ! com
[Download RAW message or body]
On Thu, Jun 30, 2016 at 1:38 PM, Murphy, Kevin <MURPHYKE@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.
[Attachment #3 (text/html)]
<div dir="ltr"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><span \
style="font-family:arial,sans-serif">On Thu, Jun 30, 2016 at 1:38 PM, Murphy, Kevin \
</span><span dir="ltr" style="font-family:arial,sans-serif"><<a \
href="mailto:MURPHYKE@email.chop.edu" \
target="_blank">MURPHYKE@email.chop.edu</a>></span><span \
style="font-family:arial,sans-serif"> wrote:</span><br></div><div \
class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px \
0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex">
<div style="word-wrap:break-word;color:rgb(0,0,0);font-size:16px;font-family:Calibri,sans-serif">
<div>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`?</div> <div><br>
</div>
<div>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.</div> <div><br>
</div>
<div>I've seen this discussed here: <a \
href="https://www.postgresql.org/message-id/E1VYMqi-0001P4-P4%40wrigleys.postgresql.org" \
target="_blank">https://www.postgresql.org/message-id/E1VYMqi-0001P4-P4%40wrigleys.postgresql.org</a>, \
but the discussion petered out prematurely.</div> \
<div><br></div></div></blockquote><div><br></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">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.</div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">Testing this out a bit \
on 9.5 Ubuntu 14.04 - I believe the last command, <pg_dump -C -s testdb>, is in \
error.</div></div><div><br></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><<SQL</div><br></div><div><div \
class="gmail_default" style="display:inline"><div style=""><font face="arial, \
helvetica, sans-serif">create user testuser;</font></div><div style=""><font \
face="arial, helvetica, sans-serif">create database testdb;</font></div><div \
style=""><font face="arial, helvetica, sans-serif">grant create on database testdb to \
testuser;</font></div><div style=""><font face="arial, helvetica, \
sans-serif"><br></font></div><div style=""><font face="arial, helvetica, \
sans-serif">$ pg_dumpall</font></div><div style=""><font face="arial, helvetica, \
sans-serif">[...]</font></div><div style=""><font face="arial, helvetica, \
sans-serif">CREATE ROLE testuser;</font></div><div style=""><font face="arial, \
helvetica, sans-serif">ALTER ROLE testuser WITH NOSUPERUSER INHERIT NOCREATEROLE \
NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;</font></div><div style=""><font \
face="arial, helvetica, sans-serif">CREATE DATABASE testdb WITH TEMPLATE = template0 \
OWNER = vagrant;</font></div><div style=""><font face="arial, helvetica, \
sans-serif">GRANT CREATE ON DATABASE testdb TO testuser;</font></div><div \
style=""><font face="arial, helvetica, sans-serif">REVOKE ALL ON SCHEMA public FROM \
PUBLIC;</font></div><div style=""><font face="arial, helvetica, sans-serif">REVOKE \
ALL ON SCHEMA public FROM postgres;</font></div><div style=""><font face="arial, \
helvetica, sans-serif">GRANT ALL ON SCHEMA public TO postgres;</font></div><div \
style=""><font face="arial, helvetica, sans-serif">GRANT ALL ON SCHEMA public TO \
PUBLIC;</font></div><div style=""><font face="arial, helvetica, \
sans-serif">[...]</font></div><div style=""><font face="arial, helvetica, \
sans-serif"><br></font></div><div style=""><font face="arial, helvetica, \
sans-serif">$ pg_dumpall -g </font></div><div style=""><font face="arial, helvetica, \
sans-serif">[...]</font></div><div style=""><font face="arial, helvetica, \
sans-serif">CREATE ROLE testuser;</font></div><div style=""><font face="arial, \
helvetica, sans-serif">ALTER ROLE testuser WITH NOSUPERUSER INHERIT NOCREATEROLE \
NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;</font></div><div style=""><font \
face="arial, helvetica, sans-serif">-------NO CREATE DATABASE (ok, db definitions are \
not globals)</font></div><div style=""><font face="arial, helvetica, \
sans-serif">-------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)</font></div><div style=""><font face="arial, helvetica, \
sans-serif">REVOKE ALL ON SCHEMA public FROM PUBLIC;</font></div><div style=""><font \
face="arial, helvetica, sans-serif">REVOKE ALL ON SCHEMA public FROM \
postgres;</font></div><div style=""><font face="arial, helvetica, sans-serif">GRANT \
ALL ON SCHEMA public TO postgres;</font></div><div style=""><font face="arial, \
helvetica, sans-serif">GRANT ALL ON SCHEMA public TO PUBLIC;</font></div><div \
style=""><font face="arial, helvetica, sans-serif">[...]</font></div><div \
style=""><font face="arial, helvetica, sans-serif"><br></font></div><div \
style=""><font face="arial, helvetica, sans-serif">$ pg_dump -s \
testdb</font></div><div style=""><font face="arial, helvetica, \
sans-serif">[...]</font></div><div style=""><font face="arial, helvetica, \
sans-serif">-------NO CREATE DATABASE (OK - didn't ask for one)</font></div><div \
style=""><font face="arial, helvetica, sans-serif">-------NO GRANT STATEMENTS (I \
guess, let whatever is presently in place rule - basically the same as pg_dumpall \
-g)</font></div><div style=""><font face="arial, helvetica, sans-serif">REVOKE ALL ON \
SCHEMA public FROM PUBLIC;</font></div><div style=""><font face="arial, helvetica, \
sans-serif">REVOKE ALL ON SCHEMA public FROM postgres;</font></div><div \
style=""><font face="arial, helvetica, sans-serif">GRANT ALL ON SCHEMA public TO \
postgres;</font></div><div style=""><font face="arial, helvetica, sans-serif">GRANT \
ALL ON SCHEMA public TO PUBLIC;</font></div><div style=""><font face="arial, \
helvetica, sans-serif">[...]</font></div><div style=""><font face="arial, helvetica, \
sans-serif"><br></font></div><div style=""><font face="arial, helvetica, \
sans-serif">$ pg_dump -C -s testdb</font></div><div style=""><font face="arial, \
helvetica, sans-serif">[...]</font></div><div style=""><font face="arial, helvetica, \
sans-serif">CREATE DATABASE testdb WITH TEMPLATE = template0 ENCODING = \
'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = \
'en_US.UTF-8';</font></div><div style=""><font face="arial, helvetica, \
sans-serif">--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!</font></div><div \
style=""><font face="arial, helvetica, sans-serif">--NO GRANT STATEMENTS (If we \
create the DB we should also be instantiating the GRANTs, like we do in \
pg_dumpall)</font></div><div style=""><font face="arial, helvetica, \
sans-serif">--!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!</font></div><div \
style=""><font face="arial, helvetica, sans-serif">REVOKE ALL ON SCHEMA public FROM \
PUBLIC;</font></div><div style=""><font face="arial, helvetica, sans-serif">REVOKE \
ALL ON SCHEMA public FROM postgres;</font></div><div style=""><font face="arial, \
helvetica, sans-serif">GRANT ALL ON SCHEMA public TO postgres;</font></div><div \
style=""><font face="arial, helvetica, sans-serif">GRANT ALL ON SCHEMA public TO \
PUBLIC;</font></div><div style=""><font face="arial, helvetica, \
sans-serif">[...]</font></div><div \
style="font-family:arial,helvetica,sans-serif"><br></div></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">SQL</div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline">David \
J.</div></div><div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif;display:inline"><br></div></div></div></div></div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic