Re: Default privileges for new databases (was Re: Can't

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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073