Dear Tom,
> > Another heavier but more general approach would be to add a boolean to
> > pg_database to tell whether the first connection housekeeping was
> > performed,
>
> I was envisioning a bool column added to pg_database,
> and having the set of operations just hard-coded into the backend.
Why not. indeed it simplifies as it avoids the intermediate file. Also, if
there is no objection to modify a system catalog, it is fine for me.
> The only input information the process needs is the DB owner's ID,
> which it can of course get from the pg_database row.
Sure.
> I doubt that reading a file of SQL commands is easier.
I agree, you're idea is simpler.
> One definitional issue that remains to be resolved is "just what is the
> public schema anyway?". What I want to know is whether we want to
> forcibly change owner of any random schema that happens to be named
> "public"? Or should we insist on it having the original OID? Or some
> other way of identifying it?
> In the same vein: we probably need to alter the ACL for public so that
> its privileges appear to flow from the object owner and not from the
> postgres user.
I was thinking about something fuzzy enough as:
UPDATE pg_catalog.pg_namespace
SET nspowner=datdba, nspacl=NULL -- NULL means default rights...
FROM pg_catalog.pg_database
WHERE nspname NOT LIKE ALL(ARRAY['pg_%','information_schema']) AND datname=CURRENT_DATABASE();
But it can be changed to anything else...
> What do we do if the ACL is in a non-default state?
Well, one could suggest to fix the aclitem grantor to the owner, but as an
array of opaque type it is not very easy to manipulate from a query.
Or it could be a feature that non system schemas belong to the owner and
are initialized with the default rights, just as they would have been
if created by the owner with a "CREATE SCHEMA"...
The later is simple and makes sense anyway for a newly created database.
--
Fabien Coelho - coelho@cri.ensmp.fr
