Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES
От | Masahiko Sawada |
---|---|
Тема | Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES |
Дата | |
Msg-id | CAD21AoDRg1E7AEM_5ifyjFbQ71J303OHA-R1HjzqC0FT306dKQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES (Michael Paquier <michael@paquier.xyz>) |
Ответы |
Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES
("Bossart, Nathan" <bossartn@amazon.com>)
|
Список | pgsql-bugs |
On Thu, Oct 14, 2021 at 4:53 PM Michael Paquier <michael@paquier.xyz> wrote: > > On Thu, Oct 14, 2021 at 02:22:21PM +0900, Masahiko Sawada wrote: > > Agreed. Please find an attached new patch. > > I have not dived into the details of the patch yet, but I can see the > following diffs in some of the dumps dropped by the new test added > between HEAD and the patch: I've checked where these differences come from: > 1) For DEFAULT PRIVILEGES FOR FUNCTIONS: > -ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA > dump_test REVOKE ALL ON FUNCTIONS FROM PUBLIC; > +ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA > dump_test GRANT ALL ON FUNCTIONS TO regress_dump_test_role; The test query and the default privileges I got are: ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA dump_test GRANT EXECUTE ON FUNCTIONS TO regress_dump_test_role; Default access privileges Owner | Schema | Type | Access privileges ------------------------+-----------+----------+------------------------------------------------- regress_dump_test_role | dump_test | function | regress_dump_test_role=X/regress_dump_test_role (1 row) The query dumped by the current pg_dump (i.g., HEAD, w/o patch) is: ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA dump_test REVOKE ALL ON FUNCTIONS FROM PUBLIC; The query dumped by pg_dump with the patch is: ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA dump_test GRANT ALL ON FUNCTIONS TO regress_dump_test_role; The query dumped by the current pg_dump is wrong and the patch fixes it. This difference looks good to me. > 2) For DEFAULT PRIVILEGES FOR TABLES: > -ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA > dump_test REVOKE ALL ON TABLES FROM regress_dump_test_role; > ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA > dump_test GRANT SELECT ON TABLES TO regress_dump_test_role; The test query and the default privileges I got are: ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA dump_test GRANT SELECT ON TABLES TO regress_dump_test_role; Default access privileges Owner | Schema | Type | Access privileges ------------------------+-----------+-------+------------------------------------------------- regress_dump_test_role | dump_test | table | regress_dump_test_role=r/regress_dump_test_role (1 row) The query dumped by the current pg_dump (i.g., HEAD, w/o patch) is: ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA dump_test REVOKE ALL ON TABLES FROM regress_dump_test_role; ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA dump_test GRANT SELECT ON TABLES TO regress_dump_test_role; The query dumped by pg_dump with the patch is: ALTER DEFAULT PRIVILEGES FOR ROLE regress_dump_test_role IN SCHEMA dump_test GRANT SELECT ON TABLES TO regress_dump_test_role; The current pg_dump produced a REVOKE ALL ON TABLES FROM regress_dump_test_role but it seems unnecessary. The patch removes it so looks good to me too. Regards, -- Masahiko Sawada EDB: https://www.enterprisedb.com/
В списке pgsql-bugs по дате отправления:
Предыдущее
От: "Bossart, Nathan"Дата:
Сообщение: Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES
Следующее
От: "Andrey V. Lepikhov"Дата:
Сообщение: SET SESSION AUTHORIZATION command doesn't update status of backend