[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,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
Attachments:
create_database.sqltext/plain; charset=UTF-8; name=create_database.sql; x-mac-creator=0; x-mac-type=0Download
pg_controldata.txttext/plain; charset=UTF-8; name=pg_controldata.txtDownload
pg_config.txttext/plain; charset=UTF-8; name=pg_config.txtDownload
missing_privileges.sqltext/plain; charset=UTF-8; name=missing_privileges.sql; x-mac-creator=0; x-mac-type=0Download
bug_schema_dump.sqltext/plain; charset=UTF-8; name=bug_schema_dump.sql; x-mac-creator=0; x-mac-type=0Download
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