Re: public schema default ACL

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: Petr Jelinek <petr(dot)jelinek(at)2ndquadrant(dot)com>
Cc: Noah Misch <noah(at)leadboat(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 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 17:17:32
Message-ID: 20180307171732.GP2416@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Greetings Petr,

* Petr Jelinek (petr(dot)jelinek(at)2ndquadrant(dot)com) wrote:
> On 07/03/18 17:55, Stephen Frost wrote:
> > Greetings Petr, all,
> >
> > * Petr Jelinek (petr(dot)jelinek(at)2ndquadrant(dot)com) wrote:
> >> On 07/03/18 13:14, Stephen Frost wrote:
> >>> * Noah Misch (noah(at)leadboat(dot)com) wrote:
> >>>> On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
> >>>>> * Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> >>>>>> I wonder whether it'd be sensible for CREATE USER --- or at least the
> >>>>>> createuser script --- to automatically make a matching schema. Or we
> >>>>>> could just recommend that DBAs do so. Either way, we'd be pushing people
> >>>>>> towards the design where "$user" does exist for most/all users. Our docs
> >>>>>> comment (section 5.8.7) that "the concepts of schema and user are nearly
> >>>>>> equivalent in a database system that implements only the basic schema
> >>>>>> support specified in the standard", so the idea of automatically making
> >>>>>> a schema per user doesn't seem ridiculous on its face. (Now, where'd I
> >>>>>> put my flameproof long johns ...)
> >>>>>
> >>>>> You are not the first to think of this in recent days, and I'm hopeful
> >>>>> to see others comment in support of this idea. For my 2c, I'd suggest
> >>>>> that what we actually do is have a new role attribute which is "when
> >>>>> this user connects to a database, if they don't have a schema named
> >>>>> after their role, then create one." Creating the role at CREATE ROLE
> >>>>> time would only work for the current database, after all (barring some
> >>>>> other magic that allows us to create schemas in all current and future
> >>>>> databases...).
> >>>>
> >>>> 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.
> >>>
> >> Yeah I think role attribute makes sense, it's why I suggested something
> >> like DEFAULT_SCHEMA, that seems to address both schema creation (dba can
> >> point the schema to public for example) and also the fact that $user
> >> schema which is first in search_path might or might not exist.
> >
> > What I dislike about this proposal is that it seems to conflate two
> > things- if the schema will be created for the user automatically or not,
> > and what the search_path setting is.
>
> Well, what $user in search_path resolves to rather than what search_path is.

Alright, that makes a bit more sense to me. I had thought you were
suggesting we would just combine these two pieces to make up the "real"
search path, which I didn't like.

Having it replace what $user is in the search_path would be a bit
confusing, I think. Perhaps having a new '$default' would be alright
though I'm still having a bit of trouble imagining the use-case and it
seems like we'd probably still keep the "wil this schema be created
automatically or not" seperate from this new search path variable.

> > Those are two different things and
> > I don't think we should mix them.
>
> I guess I am missing the point of the schema creation for user then if
> it's not also automatically the default schema for that user.

With our default search path being $user, public, it would be...

Thanks!

Stephen

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2018-03-07 17:17:53 Re: FOR EACH ROW triggers on partitioned tables
Previous Message Petr Jelinek 2018-03-07 16:59:52 Re: public schema default ACL