Обсуждение: [BUGS] [pg_dump] not dumping some default privileges
Hi, I noticed that pg_dump since 9.6 is missing some default privileges in the dump. I noticed it since 9.6.2 but it's still there in 9.6.5. To reproduce: 1) run 'create_database.sql' to create a database with some default privileges. create_database.sql is a modified pg_dump to reproduce this problem. 2) Check default privileges, should be like this: > bug=# \ddp > Default access privileges > Owner | Schema | Type | Access privileges > ----------+--------+----------+---------------------- > bug | public | sequence | bug=rwU/bug > bug | public | table | bug=arwdDxt/bug > postgres | public | sequence | bug=rwU/postgres > postgres | public | table | bug=arwdDxt/postgres > (4 rows) 3) Dump & re-create database with this dump $ pg_dump -cCs -d bug > bug_schema_dump.sql $ psql -f bug_schema_dump.sql 4) re-check default privileges: > bug=# \ddp > Default access privileges > Owner | Schema | Type | Access privileges > ----------+--------+----------+---------------------- > postgres | public | sequence | bug=rwU/postgres > postgres | public | table | bug=arwdDxt/postgres > (2 rows) As show with a grep it's clearly in the dump (and thus not in psql not loading): > $ grep "^ALTER DEFAULT" create_database.sql > ALTER DEFAULT PRIVILEGES FOR ROLE bug IN SCHEMA public REVOKE ALL ON SEQUENCES FROM bug; > ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON SEQUENCES FROM postgres; > ALTER DEFAULT PRIVILEGES FOR ROLE bug IN SCHEMA public REVOKE ALL ON TABLES FROM bug; > ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES FROM postgres; > ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT USAGE,SELECT,UPDATE ON SEQUENCES TO bug; > ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGERON TABLES TO bug; > ALTER DEFAULT PRIVILEGES FOR ROLE bug IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER ONTABLES TO bug; > ALTER DEFAULT PRIVILEGES FOR ROLE bug IN SCHEMA public GRANT USAGE,SELECT,UPDATE ON SEQUENCES TO bug; > > $ grep "^ALTER DEFAULT" bug_schema_dump.sql > ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON SEQUENCES FROM postgres; > ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON SEQUENCES TO bug; > ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE ALL ON TABLES FROM postgres; > ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public GRANT ALL ON TABLES TO bug; The new database is missing 2 privileges (states in missing_privileges.sql), I obviously expected that all privileges were dumped. I added two txt files (pg_controldata.txt & pg_config.txt) to show my environment, which is a Mac with PG9.6.5. I initially noticed this bug on a CentOS6 system with PG9.6.2 and it was still there after upgrade to PG9.6.5. I hope to have added all needed information, if not please let me know what extra information you need. Gr Reinier -- Reinier Haasjes Senior System Administrator office +31 20 240 16 31 Adyen Headquarters Simon Carmiggeltstraat 6-50, 5th floor 1011 DJ Amsterdam, The Netherlands www.adyen.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Вложения
Reinier Haasjes wrote: > Hi, > > I noticed that pg_dump since 9.6 is missing some default privileges in > the dump. I noticed it since 9.6.2 but it's still there in 9.6.5. Hmm, I can confirm that 9.6 seems to have this problem while 9.5 does not seem to have it. Must have been broken at some point during the extensive pg_dump patches in 9.6 ... I'll give this a look. Thanks for reporting! -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On 23 October 2017 at 14:54, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
>
> Reinier Haasjes wrote:
> > Hi,
> >
> > I noticed that pg_dump since 9.6 is missing some default privileges
> Hmm, I can confirm that 9.6 seems to have this problem while 9.5 does
> not seem to have it. Must have been broken at some point during the
> extensive pg_dump patches in 9.6 ... I'll give this a look.
in buildACLQueries there is a filter to exclude those acl's that match the pg_init_privs or default pg_catalog.acldefault for that role.
If I run the create_database.sql with a slightly altered GRANT section, pg_dump does return the actual DEFAULT PRIVILEGES for that role, these don't match the defaults.
-ALTER DEFAULT PRIVILEGES FOR ROLE bug IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER ON TABLES TO bug;
+ALTER DEFAULT PRIVILEGES FOR ROLE bug IN SCHEMA public GRANT SELECT,INSERT,UPDATE,DELETE,TRUNCATE,TRIGGER ON TABLES TO bug;
regards,
Feike
Feike Steenbergen wrote: > On 23 October 2017 at 14:54, Alvaro Herrera <alvherre@2ndquadrant.com> > wrote: > > > > Reinier Haasjes wrote: > > > Hi, > > > > > > I noticed that pg_dump since 9.6 is missing some default privileges > > Hmm, I can confirm that 9.6 seems to have this problem while 9.5 does > > not seem to have it. Must have been broken at some point during the > > extensive pg_dump patches in 9.6 ... I'll give this a look. > > in buildACLQueries there is a filter to exclude those acl's that match the > pg_init_privs or default pg_catalog.acldefault for that role. Ah, thanks, Feike. So what is going on is that those commands don't actually have any visible effect in pg_dump because they change the privileges from the default state to a state that's identical to the default. I suppose that makes sense. However, I don't think Reinier would have gone to the trouble of reporting the problem unless there was something actually being affected by it. Am I mistaken? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Hi, On 23/10/2017 16:03, Alvaro Herrera wrote: > However, I don't think Reinier would have gone to the trouble of > reporting the problem unless there was something actually being affected > by it. Am I mistaken? I reported this problem because my automated privileges script was complaining after dump/restore. I did some more checking and I can't find any other thing being affected by it (beside my check script). Apparently the bug is not in pg_dump but it's my script not fully 9.6 compliant. I will adapt the script to be fully 9.6 compatible. Sorry for the trouble but thanks for the help. We can mark this one as NOT_A_BUG ;) Gr Reinier -- Reinier Haasjes Senior System Administrator office +31 20 240 16 31 Adyen Headquarters Simon Carmiggeltstraat 6-50, 5th floor 1011 DJ Amsterdam, The Netherlands www.adyen.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs