Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES

On 10/14/21, 12:55 AM, "Michael Paquier" <michael(at)paquier(dot)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