Re: Default privileges for new databases (was Re: Can't - Mailing list pgsql-hackers

I had a good chuckle with this.  It is the type of "shoot for the moon"
idea I would have.  Maybe I am rubbing off on you.  :-)

The only problem I see with this solution is it makes admins think their
template1 is safe, when it really isn't.  That seems more dangerous than
leaving it world-writable.  I don't think accidental writes into
template1 are common enough to add a possible admin confusion factor.

What we really need is some mode on template1 that says, "I am not
world-writable, but the admin hasn't made me world-non-writable, so I
will create new databases that are world-writable".  Does that make
sense?

I have an idea.  Could we have the template1 per-database GUC settings
control the writeability of databases created from template1, sort of a
'creation GUC setting', so we could run it on the new database once it
is created?  That way, we could make template1 public
non-world-writable, and put something in the template1 per-database GUC
setting to make databases created from template1 world-writable.  If
someone removes that GUC setting, the databases get created non-world
writable.

Oh, there I go again, shooting at the moon.  ;-)

Another idea. Is there a GUC setting we could put in template1 that
would disable writing to public for world and _couldn't_ be revoked by
the user, except for super users?

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > So template1 is writable (yuck) only so databases created from template1
> > are writeable to world by default.  Is that accurate?
> 
> Yup.
> 
> I had a probably-harebrained idea about this: the writeability of public
> is only a serious issue when it is the default creation-target schema.
> It's likely that you'd say "create table foo" without reflecting about
> the fact that you're connected to template1; much less likely that you'd
> say "create table public.foo".  So, what if the default per-database GUC
> settings for template1 include setting the search_path to empty?  That
> would preclude accidental table creation in template1's public schema.
> As long as CREATE DATABASE doesn't copy the per-database GUC settings of
> the template database, copied databases wouldn't be similarly crippled.
> 
> Now I'm not entirely convinced that CREATE DATABASE shouldn't copy the
> per-database GUC settings of the template.  But at the moment it
> doesn't, and if we're willing to institutionalize that behavior then
> it'd provide a way out.
> 
> Or is that too weird?
> 
>             regards, tom lane
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073