Re: incorrect pg_dump output due to not handling dropped roles correctly

Greetings,

* Floris Van Nee (florisvannee(at)Optiver(dot)com) wrote:
> I noticed I wasn't able to apply my usual pg_dump schema output without errors anymore after I dropped some roles. After some digging, I found it has to do with Postgres not correctly updating the pg_init_privs table upon dropping roles. I can reproduce a similar scenario with the following steps (output from v13devel, but AFAIK all versions affected, I ran into the issue on v11.2):

Ok, this is ... interesting.

> postgres=# create role test;
> CREATE ROLE
> postgres=# alter default privileges in schema public grant all privileges on tables to test;
> ALTER DEFAULT PRIVILEGES
> postgres=# create extension pg_stat_statements;
> CREATE EXTENSION

So- in this case, the 'test' role is being granted these privileges
because it was given default privs in the public schema for objects that
are created by the superuser, even though the 'test' role never shows up
in the actual pg_stat_statements sql script.

I'm on the fence about if all of the objects which are created by an
extension should actually be subject to default privileges or not, but I
definitely don't think that the pg_init_privs system should be treating
those privileges that come from default privileges, instead of from the
extension's sql script, as being part of the 'initial privileges' for
the extension.

In short, I don't think any of the downthread discussion about how to
fix this is at all correct- the problem, as I view it, is that these
entries are getting into pg_init_privs in the first place and they
really shouldn't be because these privileges aren't coming from the
*extension* which is what pg_init_privs is trying to track.

Another way to view this is that I think the way we should be thinking
about the order of operations here is:

create role test;
alter default privs;
create extension;
-- extension .sql runs WITHOUT any default privs being applied
-- ACLs are recorded into pg_init_privs from the .sql script
-- default privs are applied to the objects from the extension

Maybe we implement it that way, maybe we don't, but the above is my
feeling as to what the perception should be.

This would also mean that pg_dump would automatically figure out that
these privileges have been added AFTER the extension was created (and
aren't part of the extension's .sql script) and therefore there should
be some independent GRANT commands to add those privileges back included
in the pg_dump file.

Maybe something else to point out is that if we keep these entries in
pg_init_privs the way the downthread discussion seems to be assuming,
then pg_dump would *not* include the GRANT commands to add them back and
therefore you'd have to imagine re-ordering things in pg_dump so that
the default privileges are installed before the extension gets created,
and I'm hoping everyone here agress that'd be pretty crazy to try and
do.

Thanks,

Stephen