Re: intentional or oversight? pg_dump -c does not restore default priviliges on schema public

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(at)tito:~> pg_dump -c -f /tmp/publictest -h localhost -Fp -U postgres publictest

aklaver(at)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(dot)klaver(at)aklaver(dot)com