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

On Fri, Aug 25, 2017 at 12:27 AM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
> On Thu, Aug 24, 2017 at 11:49 PM, Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com> wrote:
>> On Thu, Aug 24, 2017 at 11:26 PM, Stephen Frost <sfrost(at)snowman(dot)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