From: | Reinier Haasjes <Reinier(dot)Haasjes(at)adyen(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | [pg_dump] not dumping some default privileges |
Date: | 2017-10-23 11:13:58 |
Message-ID: | 39fd69e3-279e-357d-008b-f6508f4c48b8@adyen.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
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
Attachment | Content-Type | Size |
---|---|---|
pg_controldata.txt | text/plain | 2.2 KB |
pg_config.txt | text/plain | 1.9 KB |
missing_privileges.sql | text/plain | 237 bytes |
bug_schema_dump.sql | text/plain | 1.8 KB |
create_database.sql | text/plain | 2.1 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2017-10-23 12:54:24 | Re: [pg_dump] not dumping some default privileges |
Previous Message | gomer94 | 2017-10-23 07:44:58 | BUG #14866: The generated constraint in the typed table causes the server to crash |