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