Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges.
От | Masahiko Sawada |
---|---|
Тема | Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges. |
Дата | |
Msg-id | CAD21AoC78vkRWLXnLOuGjDWW7_gmwt8VDSpkPVC4rGqnUW2o0A@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [BUGS] BUG #14788: `pg_restore -c` won't restore schema access privileges. (Masahiko Sawada <sawada.mshk@gmail.com>) |
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: