Greetings, * Michael Paquier (mich...@paquier.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
signature.asc
Description: PGP signature
