Re: inconsistent owners in newly created databases?

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(at)cri(dot)ensmp(dot)fr