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

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

From

"Murphy, Kevin"

Date:

30 June 2016, 20:38:41

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.

Thanks,

Kevin

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

From

"David G. Johnston"

Date:

30 June 2016, 22:50:04

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.

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

From

Haribabu Kommi

Date:

05 July 2016, 09:39:32

On Fri, Jul 1, 2016 at 5:49 AM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> 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_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;
> [...]


I also feel that not generating GRANT statements may not be correct.
But from the
other side of the problem, if the grant user is not present in the
system where this
dump is restored may create problems.

Still i feel the GRANT statements should be present, as the create
database statement
is generated only with -C option. So attached patch produces the GRANT
statements based
on the -x option.


Regards,
Hari Babu
Fujitsu Australia

Attachment

pg_dump_grant_stmt_fix.patch

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

From

Rafia Sabih

Date:

23 September 2016, 11:13:44

On Tue, Jul 5, 2016 at 06:39 AM, Haribabu Kommi
kommi(dot)haribabu(at)gmail(dot)com wrote:

Still i feel the GRANT statements should be present, as the create
database statement
is generated only with -C option. So attached patch produces the GRANT
statements based
on the -x option.

The attached patch does the job fine. However, I am a little skeptical about this addition, since, it is clearly mentioned in the documentation of pg_dump that it would not restore global objects, then why expecting this. Adding this makes pg_dump -C somewhat special as now it is restoring these grant statements. Only if we consider the popular method of dump-restore mentioned in the thread (https://www.postgresql.org/message-id/E1VYMqi-0001P4-P4%40wrigleys.postgresql.org) with pg_dumpall -g and then individual pg_dump, then it would be helpful to have this patch.