On 07/03/18 13:14, Stephen Frost wrote:
> Greetings,
> * Noah Misch (n...@leadboat.com) wrote:
>> On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:
>>> * Tom Lane (t...@sss.pgh.pa.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.

Question would be what happens if schema is then explicitly dropper (in
either case).

  Petr Jelinek                  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

Reply via email to