Default privileges for new databases (was Re: Can't import large objects in most recent cvs)

Ron Snyder <snyder(at)roguewave(dot)com> writes:
> May 31 16:11:50 vault pgcvs[2135]: [91] LOG: query: Create Temporary Table
> pg_dump_blob_xref(oldOid pg_catalog.oid, newOid pg_catalog.oid);
> May 31 16:11:50 vault pgcvs[2135]: [93] ERROR: quickview: not authorized to
> create temp tables

> My theory is that I'm getting this last message (not authorized to create
> temp tables) because the permissions have been tightened down.

Yeah. Right at the moment, new databases default to only-db-owner-has-
any-rights, which means that others cannot create schemas or temp tables
in that database (unless they're superusers). I'm of the opinion that
this is a bad default, but was waiting to see if anyone complained
before starting a discussion about it.

Probably we should have temp table creation allowed to all by default.
I'm not convinced that that's a good idea for schema-creation privilege
though. Related issues: what should initdb set as the permissions for
template1? Would it make sense for newly created databases to copy
their permission settings from the template database? (Probably not,
since the owner is likely to be different.) What about copying those
per-database config settings Peter just invented?

Comments anyone?

regards, tom lane