Re: public schema default ACL

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Noah Misch <noah(at)leadboat(dot)com>, Robert Haas <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: public schema default ACL
Date: 2018-03-07 15:05:59
Views: Raw Message | Whole Thread | Download mbox
Lists: pgsql-hackers


* 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

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

> 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,



In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Nikolay Shaplov 2018-03-07 15:08:49 Re: [PATCH][PROPOSAL] Add enum releation option type
Previous Message Alvaro Herrera 2018-03-07 14:58:51 Re: GSoC 2017: weekly progress reports (week 6)