Thread

1. [pg_dump] not dumping some default privileges

   Reinier Haasjes <reinier.haasjes@adyen.com> — 2017-10-23T11:13:58Z

   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,TRIGGER ON TABLES TO bug;
   > 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 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
   

2. Re: [pg_dump] not dumping some default privileges

   Alvaro Herrera <alvherre@2ndquadrant.com> — 2017-10-23T12:54:24Z

   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
   
   
   

3. Re: [pg_dump] not dumping some default privileges

   Feike Steenbergen <feikesteenbergen@gmail.com> — 2017-10-23T13:16:40Z

   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
   

4. Re: [pg_dump] not dumping some default privileges

   Alvaro Herrera <alvherre@2ndquadrant.com> — 2017-10-23T14:03:57Z

   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
   
   
   

5. Re: [pg_dump] not dumping some default privileges

   Reinier Haasjes <reinier.haasjes@adyen.com> — 2017-10-23T14:12:46Z

   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