Re: ACL dump ordering broken as well for tablespaces

Greetings,

* Michael Paquier (michael(at)paquier(dot)xyz) wrote:
> On Wed, May 22, 2019 at 06:35:31PM +0000, Bossart, Nathan wrote:
> > A bit of digging led me to the commit that removed databases and
> > tablespaces from pg_init_privs [0] and to a related thread [1]. IIUC
> > the problem is that using pg_init_privs for databases is complicated
> > by the ability to drop and recreate the template databases.
>
> I don't quite get this argument. If a user is willing to drop
> template1, then it is logic to also drop its initial privilege entries
> and recreate new ones from scratch. I think that this deserves a
> closer lookup. For tablespaces, we are limited by the ability of not
> sharing pg_init_privs?

Why do you feel that's the case? The point of pg_init_privs is to
provide a way to go from initdb-time state to
whatever-the-current-privs-are. Dropping initdb-time privs and
reverting back to a no-privs default wouldn't be right in any case
where the initdb-time privs are different from no-privs.

As an example- if someone dropped the template1 database and then
re-created it, it won't have the same privileges as the initdb-time
template1 and instead wouldn't have any privileges- but an actual new
installation would bring back template1 with the initdb-time privileges.

Basically, the pg_dump output in that case *should* include the
equivilant of "REVOKE initdb-time privs" if we want it to be a minimal
change from what is there at initdb-time, but the only way to have that
happen is to know what the initdb-time privs were, and that's not what
will be in pg_init_privs if you drop the entries from pg_init_priv when
template1 is dropped.

I'm not sure where/why tablespaces came into this discussion at all
since we don't have any initdb-time tablespaces that can be dropped or
recreated, and the ones we do have exist with no-privs at initdb-time,
so I don't think there's any reason we'd need to have entries in
pg_init_privs for those, and it seems like they should be able to just
use the buildACLQueries magic, though it looks like you might have to
add a flag that will basically be the same as the binary_upgrade flag to
say "this object doesn't have any init privs, so we aren't joining
against pg_init_privs, and don't include that in the query".

Unfortunately, that isn't going to work for databases though. I haven't
got any particularly great solution there. We could possibly have a
catalog which defined the initdb-time objects using their name instead
of the initdb-time OID but that seems awful grotty.. Or we could try to
do the same thing the user did and just DROP template1 and then recreate
it, in which case it'd be created with no-privs and then do the same as
tablespaces above, if it has a too-high OID, but again, that seems
pretty ugly.

Open to other thoughts, but I really don't think we can just stick
things into pg_init_privs for global objects and then remove them if
that object is dropped, and it isn't a shared catalog either, so there's
also that... Having a shared catalog just in case someone wants to
drop/recreate template1 strikes me as pretty massive overkill.

Thanks,

Stephen