Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges. - Mailing list pgsql-bugs
From | Masahiko Sawada |
---|---|
Subject | Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges. |
Date | |
Msg-id | CAD21AoAyHEm11Jt7PN_wFcJ9T4sY4uueyVv3eTZ=y75tFTY_Pg@mail.gmail.com Whole thread Raw |
In response to | [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges. (fujimoto@ceptord.net) |
List | pgsql-bugs |
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
pgsql-bugs by date: