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

Thanks! That discussion thread is very informative.

My understanding is:

1. By default, a newly created schema has an empty ACL. It means
that normal users cannot access it without permission granted
explicitly.

2. However, "public" schema has special semantics; It gets created
with permissive ACL so that anyone can access it.

3. Since 9.6, pg_dump stops to output this "special" privilege part
to the dump (for some internal reasons). 9.6.3 tries to fix this
issue by patching `pg_dump -c` behavior.

Although I'm not so sure that the current behavior ("-c option affects
'custom' format dumps") is intended one, it seems we can reasonably
work around this issue.

So thanks for the response. It really was a lot of help.

On Wed, Aug 23, 2017 at 05:06:43PM +0900, Masahiko Sawada wrote:
> 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