Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES

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/