Re: public schema default ACL

Greetings,

* Alvaro Herrera (alvherre(at)alvh(dot)no-ip(dot)org) wrote:
> Stephen Frost wrote:
>
> > * Noah Misch (noah(at)leadboat(dot)com) wrote:
>
> > > I like the idea of getting more SQL-compatible, if this presents a distinct
> > > opportunity to do so. I do think it would be too weird to create the schema
> > > in one database only. Creating it on demand might work. What would be the
> > > procedure, if any, for database owners who want to deny object creation in
> > > their databases?
> >
> > My suggestion was that this would be a role attribute. If an
> > administrator doesn't wish for that role to have a schema created
> > on-demand at login time, they would set the 'SCHEMA_CREATE' (or whatever
> > we name it) role attribute to false.
>
> Is a single attribute enough? I think we need two: one would authorize
> to create the schema $user to the user themselves (maybe
> SELF_SCHEMA_CREATE); another would automatically do so when connecting
> to a database that does not have it (perhaps AUTO_CREATE_SCHEMA).

I don't see a use-case for this SELF_SCHEMA_CREATE attribute and it
seems more likely to cause confusion than to be helpful. If the admin
sets AUTO_CREATE_SCHEMA for a user then that's what we should do.

> Now, maybe the idea of creating it as soon as a connection is
> established is not great. What about creating it only when the first
> object creation is attempted and there is no other schema to create in?
> This avoid pointless proliferation of empty user schemas, as well as
> avoid the overhead of checking existence of schem $user on each
> connection.

I don't see how creating schemas for roles which the admin has created
with the AUTO_CREATE_SCHEMA option would be pointless. To not do so
would be confusing, imo. Consider the user who logs in and doesn't
realize that they're allowed to create a schema and doesn't see a schema
of their own in the list- they aren't going to think "I should just try
to create an object and see if a schema appears", they're going to ask
the admin why they don't have a schema.

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Hmm. On first glance that sounds bizarre, but we do something pretty
> similar for the pg_temp schemas, so it could likely be made to work.

While I agree that it might not be that hard to make the code do it,
since we do this for temp schemas, I still don't see real value in it
and instead just a confusing system where schemas "appear" at some
arbitrary point when the user happens to try to create an object without
qualification.

I liken this to a well-known and well-trodden feature for auto creating
user home directories on Unix. Being different from that for, at best,
rare use-cases which could be handled in other ways is going against
POLA. If an admin is concerned about too many empty schemas or about
having $user in a search_path and needing to search it, then those are
entirely fixable rather easily, but those are the uncommon cases in my
experience.

> One issue to think about is exactly which $user we intend to make the
> schema for, if we've executed SET SESSION AUTHORIZATION, or are inside
> a SECURITY DEFINER function, etc etc. I'd argue that only the original
> connection username should get this treatment, which may mean that object
> creation can fail in those contexts.

This just strengthens the "this will be confusing to our users" argument,
imv.

Thanks!

Stephen