From: Masahiko Sawada <sawada.mshk@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Neil Chen <carpenter.nail.cz@gmail.com>
Cc: Boris P. Korzun <drtr0jan@yandex.ru>
Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES
Date: Fri, 15 Oct 2021 09:05:07 +0900
Message-ID: <CAD21AoDRg1E7AEM_5ifyjFbQ71J303OHA-R1HjzqC0FT306dKQ@mail.gmail.com> (raw)
In-Reply-To: <YWfh9O/36r+GlgNB@paquier.xyz>
References: <CAA3qoJnr2+1dVJObNtfec=qW4Z0nz=A9+r5bZKoTSy5RDjskMw@mail.gmail.com>
<8261632200581@mail.yandex.ru>
<CAA3qoJ=f6jZAkkfwH+rtH_8C9z-nfcgNFYZnGYxVdBjz7Vkhig@mail.gmail.com>
<CAD21AoBrCbjiJt3OCDQCLrM6OQOxjV9cAyM6bn4=SjHq0konBQ@mail.gmail.com>
<CAD21AoC+m46JEsFE2d6JQuPuNN5ODj1vpZ3z2HtPdn+HFDJfdg@mail.gmail.com>
<721942.1634180151@sss.pgh.pa.us>
<CAD21AoAUCNCo77LJLAFGEkhtgruMpzPD153SC7dY7TyahvM_Fg@mail.gmail.com>
<YWfh9O/36r+GlgNB@paquier.xyz>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: sawada.mshk@gmail.com, michael@paquier.xyz, tgl@sss.pgh.pa.us, carpenter.nail.cz@gmail.com, drtr0jan@yandex.ru, pgsql-bugs@lists.postgresql.org
Subject: Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES
In-Reply-To: <CAD21AoDRg1E7AEM_5ifyjFbQ71J303OHA-R1HjzqC0FT306dKQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox