Thread: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges.
[BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges.
From
fujimoto@ceptord.net
Date:
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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges.
From
Masahiko Sawada
Date:
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
Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema accessprivileges.
From
Fujimoto Seiji
Date:
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
Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges.
From
Masahiko Sawada
Date:
On Wed, Aug 23, 2017 at 11:00 PM, Fujimoto Seiji <fujimoto@ceptord.net> wrote:
>
> 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.
>
It doesn't depend on format types. You can get same result even with
'plain' format dump.
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
fujimoto@ceptord.net writes:
> [ dump to archive, then pg_restore with -c option ]
> Now all the access privileges on the public schema are gone:
I can confirm this is broken in HEAD: "pg_dump -c test >test.out"
produces a script including "GRANT ALL ON SCHEMA public TO PUBLIC",
but "pg_dump -Fc test >test.dump; pg_restore -c test.dump" produces
a script lacking that.
Stephen attempted to fix this in 330b84d8c, but the fix was evidently done
incorrectly. There should never be any difference between the results of
these two procedures. I've not studied 330b84d8c, but my strong suspicion
is that the logic was added to the wrong place in pg_dump.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema accessprivileges.
From
Stephen Frost
Date:
* Tom Lane (tgl@sss.pgh.pa.us) wrote: > fujimoto@ceptord.net writes: > > [ dump to archive, then pg_restore with -c option ] > > > Now all the access privileges on the public schema are gone: > > I can confirm this is broken in HEAD: "pg_dump -c test >test.out" > produces a script including "GRANT ALL ON SCHEMA public TO PUBLIC", > but "pg_dump -Fc test >test.dump; pg_restore -c test.dump" produces > a script lacking that. Ugh. > Stephen attempted to fix this in 330b84d8c, but the fix was evidently done > incorrectly. There should never be any difference between the results of > these two procedures. I've not studied 330b84d8c, but my strong suspicion > is that the logic was added to the wrong place in pg_dump. I'm afraid it's not quite that simple, unfortunately. I'll see what I can do though. I'm really curious how this was working previously.. Thanks! Stephen
Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges.
From
Masahiko Sawada
Date:
On Thu, Aug 24, 2017 at 11:26 PM, Stephen Frost <sfrost@snowman.net> wrote: > I'll see what I can do though. I'm really curious how this was working > previously.. > In PostgreSQL 9.6.1, both "pg_dump -c; pg_restore" and "pg_dump; pg_restore -c" don't produce a script including 'GRANT ALL ON SCHEMA public TO PUBLIC'. So we can reproduce it in 9.6.1 as well. I think we should fix pg_restore so that "pg_dump; pg_restore -c" produces a script including 'GRANT ALL ON SCHEMA public TO PUBLIC'. 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
Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges.
From
Masahiko Sawada
Date:
On Thu, Aug 24, 2017 at 11:49 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: > On Thu, Aug 24, 2017 at 11:26 PM, Stephen Frost <sfrost@snowman.net> wrote: >> I'll see what I can do though. I'm really curious how this was working >> previously.. >> > > In PostgreSQL 9.6.1, both "pg_dump -c; pg_restore" and "pg_dump; > pg_restore -c" don't produce a script including 'GRANT ALL ON SCHEMA > public TO PUBLIC'. So we can reproduce it in 9.6.1 as well. I think this is right but, > I think we > should fix pg_restore so that "pg_dump; pg_restore -c" produces a > script including 'GRANT ALL ON SCHEMA public TO PUBLIC'. > this might be wrong. Let me think it again. Sorry for the noise. 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
Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges.
From
Masahiko Sawada
Date:
On Fri, Aug 25, 2017 at 12:27 AM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: > On Thu, Aug 24, 2017 at 11:49 PM, Masahiko Sawada <sawada.mshk@gmail.com> wrote: >> On Thu, Aug 24, 2017 at 11:26 PM, Stephen Frost <sfrost@snowman.net> wrote: >>> I'll see what I can do though. I'm really curious how this was working >>> previously.. >>> >> >> In PostgreSQL 9.6.1, both "pg_dump -c; pg_restore" and "pg_dump; >> pg_restore -c" don't produce a script including 'GRANT ALL ON SCHEMA >> public TO PUBLIC'. So we can reproduce it in 9.6.1 as well. > > I think this is right but, > >> I think we >> should fix pg_restore so that "pg_dump; pg_restore -c" produces a >> script including 'GRANT ALL ON SCHEMA public TO PUBLIC'. >> > > this might be wrong. Let me think it again. Sorry for the noise. > IIUC this is not resolved yet. To be clear, I've confirmed the behavior of both pg_dump and pg_restore in 9.6.0 and 9.6.4. I extracted DDLs for the restore that is generated by pg_dump or pg_restore. Prepare ======= CREATE DATABASE test_db; Test ======= 1. pg_dump -c -d test_db -Fp - 9.6.0 DROP EXTENSION plpgsql; DROP SCHEMA public; CREATE SCHEMA public; ALTER SCHEMA public OWNER TO masahiko; COMMENT ON SCHEMA public IS 'standard public schema'; CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; - 9.6.4 DROP EXTENSION plpgsql; DROP SCHEMA public; CREATE SCHEMA public; ALTER SCHEMA public OWNER TO masahiko; COMMENT ON SCHEMA public IS 'standard public schema'; CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; GRANT ALL ON SCHEMA public TO PUBLIC; In 9.6.4, pg_dump -c additionally writes GRANT, which is fine. ----- 2. pg_dump -c -d test_db -Fc | pg_restore - 9.6.0 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; - 9.6.4 CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; GRANT ALL ON SCHEMA public TO PUBLIC; In 9.6.4, pg_dump -c additionally writes GRANT DDL, which is bad because pg_dump -c option should not effect to the custom format dump. ----- 3. pg_dump -d test_db -Fc | pg_restore -c - 9.6.0 DROP EXTENSION plpgsql; DROP SCHEMA public; CREATE SCHEMA public; ALTER SCHEMA public OWNER TO masahiko; COMMENT ON SCHEMA public IS 'standard public schema'; CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; - 9.6.4 DROP EXTENSION plpgsql; DROP SCHEMA public; CREATE SCHEMA public; ALTER SCHEMA public OWNER TO masahiko; COMMENT ON SCHEMA public IS 'standard public schema'; CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog; COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language'; The output is the same, which is not good because pg_restore -c should write GRANT DDL. Otherwise the privileges of test_db.publlic schema will be dropped after restored. To summary, both pg_dump and pg_restore have a bug. * pg_dump -c -Fc writes GRANT DDL but -c option should not effect the custom format dump. * pg_restore -c option doesn't write GRANT DDL but should do that. To fix that, I think we can make pg_dump dump the GRANT DDL even if --clean option is not specified and make pg_restore not restore the GRANT DDL if -c option is not specified. Am I missing something? 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