Re: BUG #14788: `pg_restore -c` won't restore schema access privileges.

On Mon, Aug 21, 2017 at 7:44 PM, <fujimoto(at)ceptord(dot)net> wrote:
> The following bug has been logged on the website:
>
> Bug reference: 14788
> Logged by: Fujimoto Seiji
> Email address: fujimoto(at)ceptord(dot)net
> PostgreSQL version: 9.6.4
> Operating system: Linux 4.9.0 (Debian 9.1)
> Description:
>
> ## How to reproduce
>
> 1. Create a new database:
>
> $ createdb --template=template0 test
> $ psql -c '\dn+' test
> list of schemas
> name | owner | access privileges | description
> --------+----------+----------------------+------------------------
> public | postgres | postgres=uc/postgres+| standard public schema
> | | =uc/postgres |
> (1 row)
>
> 2. pg_dump the database with 'custom' format:
>
> $ pg_dump -Fc -f test.dump test
>
> 3. Restore the dump with '-c' option:
>
> $ pg_restore -c -d test test.dump
>
>
> ## Bug/Problem
>
> Now all the access privileges on the public schema are gone:
>
> $ psql -c '\dn+' test
> List of schemas
> Name | Owner | Access privileges | Description
> --------+----------+-------------------+------------------------
> public | postgres | | standard public schema
> (1 row)
>
>
> ## Expected behabior
>
> Granted privileges gets restored correctly.
>
>
> ## Additional notes
>
> Interestingly, if the database has been dumped with '-c' option,
> pg_restore
> will restore the schema privileges correctly.
>
> $ createdb --template=template0 test
> $ pg_dump -Fc -c -f test.dump
> $ pg_restore -c -d test test.dump
> $ psql -c '\dn+' test
> List of schemas
> Name | Owner | Access privileges | Description
> --------+----------+----------------------+------------------------
> public | postgres | postgres=UC/postgres+| standard public schema
> | | =UC/postgres |
> (1 row)
>
> (I do not understand why '-c' option affects the custom format dump.
> This behaviour itself contradicts what the documentation states)
>
>> -c
>> --clean
>>
>> ...
>> This option is only meaningful for the plain-text format. For the
> archive
>> formats, you can specify the option when you call pg_restore.
>>
>> https://www.postgresql.org/docs/9.6/static/app-pgdump.html
>

I think it's not a bug. This behavior is introduced in PostgreSQL
9.6.3 by a discussion[1]. For the reason, please see PostgreSQL 9.6.3
release note[2].

----
Fix pg_dump/pg_restore to correctly handle privileges for the public
schema when using --clean option (Stephen Frost)
Other schemas start out with no privileges granted, but public does
not; this requires special-case treatment when it is dropped and
restored due to the --clean option.
----

[1] https://www.postgresql.org/message-id/3534542.o3cNaKiDID%40techfox
[2] https://www.postgresql.org/docs/current/static/release-9-6-3.html

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center