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

On Fri, Jul 1, 2016 at 5:49 AM, David G. Johnston
<david(dot)g(dot)johnston(at)gmail(dot)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