Re: [GENERAL] intentional or oversight? pg_dump -c does not restoredefault priviliges on schema public - Mailing list pgsql-general

On 02/11/2017 12:42 PM, Frank van Vugt wrote:
> L.S.
>
> I noticed the following and wondered whether this is intentional or an
> oversight in pg_dump's '-c' option?

What version of Postgres?

Because when I do it on 9.5.5 I get:

test=# create database publictest;
                                                           
CREATE DATABASE
                                                           
test=# \c publictest;
                                                           
You are now connected to database "publictest" as user "postgres".
                                                           
publictest=# \dn+
                                                           
                          List of schemas
                                                           
  Name  |  Owner   |  Access privileges   |      Description
                                                           
--------+----------+----------------------+------------------------
                                                           
 public | postgres | postgres=UC/postgres+| standard public schema
                                                           
        |          | =UC/postgres         |
                                                           
(1 row)


aklaver@tito:~> pg_dump -c -f /tmp/publictest -h localhost -Fp -U postgres publictest

aklaver@tito:~> psql -d publictest -U postgres
                    
Null display is "NULL".
psql (9.5.5)
Type "help" for help.

publictest=# \i /tmp/publictest
SET
SET
SET
SET
SET
SET
SET
SET
DROP EXTENSION
DROP SCHEMA
CREATE SCHEMA
ALTER SCHEMA
COMMENT
CREATE EXTENSION
COMMENT
REVOKE
REVOKE
GRANT
GRANT

publictest=# \dn+
                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres+| standard public schema
        |          | =UC/postgres         |
(1 row)

>
> The clean option causes the public schema to be dropped and recreated, but
> this is done with the default schema priviliges, which are not the same as the
> ones assigned during create database:
>
>
> *** USING PSQL
>
> postgres=# create database publictest;
>
> postgres=# \c publictest;
>
> publictest=# \dn+
>                           List of schemas
>   Name  |  Owner   |  Access privileges   |      Description
> --------+----------+----------------------+------------------------
>  public | postgres | postgres=UC/postgres+| standard public schema
>         |          | =UC/postgres         |
> (1 row)
>
>
>
> *** USING SHELL
>
> host:~ # pg_dump -c -f /tmp/publictest -h localhost -Fp -U postgres publictest
>
>
>
> *** USING PSQL
>
> publictest=# \i /tmp/publictest
>
> publictest=# \dn+
>                         List of schemas
>   Name  |  Owner   | Access privileges |      Description
> --------+----------+-------------------+------------------------
>  public | postgres |                   | standard public schema
> (1 row)
>
> publictest=# grant usage on schema public to public;
> GRANT
> publictest=# grant create on schema public to public;
> GRANT
>
> testje=# \dn+
>                           List of schemas
>   Name  |  Owner   |  Access privileges   |      Description
> --------+----------+----------------------+------------------------
>  public | postgres | postgres=UC/postgres+| standard public schema
>         |          | =UC/postgres         |
> (1 row)
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com