Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES

On 10/14/21, 12:55 AM, "Michael Paquier" <michael@paquier.xyz> wrote:
> 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;

This one looks correct 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;
>
> So the patch removes a REVOKE ALL ON TABLES on
> regress_dump_test_role after the addition of only the GRANT EXECUTE ON
> FUNCTIONS.  That seems off.  Am I missing something?

I might be missing something as well, but this one looks correct to
me, too.  I suspect that REVOKE statement was generated by comparing
against the wrong default ACL and that it actually has no effect.

Nathan