[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