Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema accessprivileges. - Mailing list pgsql-bugs

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
permissiongranted    explicitly.    2. However, "public" schema has special semantics; It gets created    with
permissiveACL 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
triesto 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@ceptord.net> wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference:      14788
> > Logged by:          Fujimoto Seiji
> > Email address:      fujimoto@ceptord.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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs