MIME-Version: 1.0
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>
In-Reply-To: <YWfh9O/36r+GlgNB@paquier.xyz>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Fri, 15 Oct 2021 09:05:07 +0900
Message-ID: 
 <CAD21AoDRg1E7AEM_5ifyjFbQ71J303OHA-R1HjzqC0FT306dKQ@mail.gmail.com>
Subject: Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES
To: Michael Paquier <michael@paquier.xyz>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Neil Chen <carpenter.nail.cz@gmail.com>,
	"Boris P. Korzun" <drtr0jan@yandex.ru>,
	"pgsql-bugs@lists.postgresql.org" <pgsql-bugs@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoDRg1E7AEM_5ifyjFbQ71J303OHA-R1HjzqC0FT306dKQ%40mail.gmail.com>
Precedence: bulk

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/