public schema default ACL

On 03/03/2018 01:56 AM, Noah Misch wrote:

Commit 5770172 ("Document security implications of search_path and the public
schema.") is largely a workaround for the fact that the boot_val of
search_path contains "public" while template0 gets "GRANT CREATE, USAGE ON
SCHEMA public TO PUBLIC". It's like having world-writable /usr/bin. The
security team opted not to change that in released branches, but we thought to
revisit it later. I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns?

+1. Doing this, or even revoking everything for schema public from
PUBLIC, is already common enough and good practice.

If we do that alone, databases reaching v11 via dump/reload or pg_upgrade will
get the new default ACL if they had not changed the ACL of schema public. If
they had GRANTed or REVOKEd on schema public, pg_dump will recreate the
resulting ACL. This is the standard pg_dump behavior for ACLs on system
objects. I think that's okay for the public schema, too, and I like
preserving that usual rule. However, if we wanted to minimize upgrade-time
surprises, we could make pg_dump include GRANT for schema public
unconditionally. That way, the default ACL change would apply to new
databases only. Does anyone want to argue for that?

What about a pg_dump option to do that and then a big note in the
release notes telling people why they might want to use it?

Joe

--
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development

On Sat, Mar 03, 2018 at 02:31:58AM -0800, Joe Conway wrote:

On 03/03/2018 01:56 AM, Noah Misch wrote:

If we do that alone, databases reaching v11 via dump/reload or pg_upgrade will
get the new default ACL if they had not changed the ACL of schema public. If
they had GRANTed or REVOKEd on schema public, pg_dump will recreate the
resulting ACL. This is the standard pg_dump behavior for ACLs on system
objects. I think that's okay for the public schema, too, and I like
preserving that usual rule. However, if we wanted to minimize upgrade-time
surprises, we could make pg_dump include GRANT for schema public
unconditionally. That way, the default ACL change would apply to new
databases only. Does anyone want to argue for that?

What about a pg_dump option to do that and then a big note in the
release notes telling people why they might want to use it?

I'd want any new pg_dump option to have use beyond this one case. That is,
not --old-public-schema-acl, but perhaps --old-system-acls-for=OBJECT-PATTERN.
But it's a simple task to loop over your databases and run a GRANT, so I
somewhat doubt that particular idea should win. Hmm.

On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:

Commit 5770172 ("Document security implications of search_path and the public
schema.") is largely a workaround for the fact that the boot_val of
search_path contains "public" while template0 gets "GRANT CREATE, USAGE ON
SCHEMA public TO PUBLIC". It's like having world-writable /usr/bin. The
security team opted not to change that in released branches, but we thought to
revisit it later. I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.

Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:

I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.

Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.

Well, the fundamental problem here is that the arrangements around schema
public were set up to allow a smooth transition from the pre-7.3
no-schemas world, not to provide any kind of security. If we want to use
schemas for security then we're going to have to do *something* that's not
compatible. Or we can continue to ship an insecure default configuration,
but I recall many people arguing against that sort of choice in the past.

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 ...)

regards, tom lane

Greetings Tom, all,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:

I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.

Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.

Well, the fundamental problem here is that the arrangements around schema
public were set up to allow a smooth transition from the pre-7.3
no-schemas world, not to provide any kind of security. If we want to use
schemas for security then we're going to have to do *something* that's not
compatible. Or we can continue to ship an insecure default configuration,
but I recall many people arguing against that sort of choice in the past.

I concur that this is the fundamental issue and that the privilege
system around schemas weren't considered due to the desire to provide a
smooth transition, but we are quite a long way from 7.3 and there's
abundent evidence that the current defaults are insecure by default.

I'll point out that a number of our *other* defaults are also insecure
(pg_hba.conf entries with 'trust' being particulalrly bad). Those have
been worked around by packagers, but that really isn't ideal. I'd love
to see us ship an actually secure (or even just reasonable, frankly...)
default configuration.

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...).

Thanks!

Stephen

On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:

I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.

Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.

It will, but the level of breakage seems similar to that from removing
PGC_SIGHUP GUCs, which we've done in major releases without great harm.

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?

On 07/03/18 08:23, Noah Misch wrote:

On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:

I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.

Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.

It will, but the level of breakage seems similar to that from removing
PGC_SIGHUP GUCs, which we've done in major releases without great harm.

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

Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.

One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.

TBH I would personally prefer if we got rid of search_path as GUC
completely because it makes certain aspects of DDL logical replication
and connection pooling much more complex, but that does not seem to be a
realistic change.

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?

Well, REVOKE CREATE ON DATABASE already exists.

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

Greetings,

* Noah Misch (noah@leadboat.com) wrote:

On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:

* Tom Lane (tgl@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.

Thanks!

Stephen

Greetings,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.

I agree that we do *not* want to force role creation on schema creation.

One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.

I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?

TBH I would personally prefer if we got rid of search_path as GUC
completely because it makes certain aspects of DDL logical replication
and connection pooling much more complex, but that does not seem to be a
realistic change.

No, I don't think we're going to get rid of it.

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?

Well, REVOKE CREATE ON DATABASE already exists.

That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.

Thanks!

Stephen

On 3/6/18 15:20, Robert Haas wrote:

On Sat, Mar 3, 2018 at 4:56 AM, Noah Misch <noah@leadboat.com> wrote:

I propose, for v11, switching to "GRANT USAGE ON SCHEMA
public TO PUBLIC" (omit CREATE). Concerns? An alternative is to change the
default search_path to "$user"; that would be break more applications, and I
don't see an advantage to compensate for that.

Isn't this going to cause widespread breakage? Unprivileged users
will suddenly find that they can no longer create tables, because
$user doesn't exist and they don't have permission on public. That
seems quite unfriendly.

Moreover, the problem is that if you have database owners that are not
superusers, they can't easily fix the issue themselves. Since the
public schema is owned by postgres, they database owner can't just go in
and run GRANT CREATE ON SCHEMA PUBLIC TO whomever to restore the old
behavior or grant specific access. It would be simpler if we didn't
install a public schema by default at all.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Stephen Frost wrote:

* Noah Misch (noah@leadboat.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).

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.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Alvaro Herrera <alvherre@alvh.no-ip.org> writes:

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.

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.

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.

regards, tom lane

Greetings,

* Alvaro Herrera (alvherre@alvh.no-ip.org) wrote:

Stephen Frost wrote:

* Noah Misch (noah@leadboat.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@sss.pgh.pa.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

On 07/03/18 13:18, Stephen Frost wrote:

Greetings,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.

I agree that we do *not* want to force role creation on schema creation.

One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.

I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?

What I am suggesting is that we add option to set user's default schema
to something other than user name so that if people don't want the
schema with the name of the user auto-created, it won't be.

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?

Well, REVOKE CREATE ON DATABASE already exists.

That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.

What's the point of creating schema for them if they don't have CREATE
privilege?

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

Greeting Petr, all,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

On 07/03/18 13:18, Stephen Frost wrote:

Greetings,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.

I agree that we do *not* want to force role creation on schema creation.

One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.

I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?

What I am suggesting is that we add option to set user's default schema
to something other than user name so that if people don't want the
schema with the name of the user auto-created, it won't be.

We have ALTER USER joe SET search_path already though..? And ALTER
DATABASE, and in postgresql.conf? What are we missing?

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?

Well, REVOKE CREATE ON DATABASE already exists.

That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.

What's the point of creating schema for them if they don't have CREATE
privilege?

They would own the schema and therefore have CREATE and USAGE rights on
the schema itself. Creating objects checks for schema rights, it
doesn't check for database rights- that's only if you're creating
schemas.

Thanks!

Stephen

On 07/03/18 16:26, Stephen Frost wrote:

Greeting Petr, all,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

On 07/03/18 13:18, Stephen Frost wrote:

Greetings,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.

I agree that we do *not* want to force role creation on schema creation.

One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.

I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?

What I am suggesting is that we add option to set user's default schema
to something other than user name so that if people don't want the
schema with the name of the user auto-created, it won't be.

We have ALTER USER joe SET search_path already though..? And ALTER
DATABASE, and in postgresql.conf? What are we missing?

That will not change the fact that we have created schema joe for that
user though. While something like CREATE USER joe DEFAULT_SCHEMA foobar
would.

My point is that I don't mind if we create schemas for users by default,
but I want simple way to opt out.

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?

Well, REVOKE CREATE ON DATABASE already exists.

That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.

What's the point of creating schema for them if they don't have CREATE
privilege?

They would own the schema and therefore have CREATE and USAGE rights on
the schema itself. Creating objects checks for schema rights, it
doesn't check for database rights- that's only if you're creating
schemas.

Yes, but should the schema for them be created at all if they don't have
CREATE privilege on the database? If yes then I have same question as
Noah, how does dba prevent object creation in their databases?

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

On 07/03/18 13:14, Stephen Frost wrote:

Greetings,

* Noah Misch (noah@leadboat.com) wrote:

On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:

* Tom Lane (tgl@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

Greetings Petr, all,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

On 07/03/18 16:26, Stephen Frost wrote:

Greeting Petr, all,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

On 07/03/18 13:18, Stephen Frost wrote:

Greetings,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

Certain "market leader" database behaves this way as well. I just hope
we won't go as far as them and also create users for schemas (so that
the analogy of user=schema would be complete and working both ways).
Because that's one of the main reasons their users depend on packages so
much, there is no other way to create a namespace without having to deal
with another user which needs to be secured.

I agree that we do *not* want to force role creation on schema creation.

One thing we could do to limit impact of any of this is having
DEFAULT_SCHEMA option for roles which would then be the first one in the
search_path (it could default to the role name), that way making public
schema work again for everybody would be just about tweaking the roles a
bit which can be easily scripted.

I don't entirely get what you're suggesting here considering we already
have $user, and it is the first in the search_path..?

What I am suggesting is that we add option to set user's default schema
to something other than user name so that if people don't want the
schema with the name of the user auto-created, it won't be.

We have ALTER USER joe SET search_path already though..? And ALTER
DATABASE, and in postgresql.conf? What are we missing?

That will not change the fact that we have created schema joe for that
user though. While something like CREATE USER joe DEFAULT_SCHEMA foobar
would.

My point is that I don't mind if we create schemas for users by default,
but I want simple way to opt out.

Oh, yes, we would definitely need an opt-out mechanism. It's unclear to
me what adding a 'default schema' role option would do though that's
different from setting the search_path for a user. I certainly wouldn't
expect it to create a new schema....

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?

Well, REVOKE CREATE ON DATABASE already exists.

That really isn't the same.. In this approach, regular roles are *not*
given the CREATE right on the database, the system would just create the
schema for them on login automatically if the role attribute says to do
so.

What's the point of creating schema for them if they don't have CREATE
privilege?

They would own the schema and therefore have CREATE and USAGE rights on
the schema itself. Creating objects checks for schema rights, it
doesn't check for database rights- that's only if you're creating
schemas.

Yes, but should the schema for them be created at all if they don't have
CREATE privilege on the database? If yes then I have same question as
Noah, how does dba prevent object creation in their databases?

Yes, the schema would be created regardless of the rights of the user on
the database, because the admin set the flag on the role saying 'create
a schema for this user when they log in.'

If we think there is a use-case for saying "this user should only have
schemas in these databases, not all databases" then I could see having
the role attribute be a list of databases or "all", instead. In the
end, I do think this is something which is controlled at the role level
and not something an individual database owner could override or
prevent, though perhaps there is some room for discussion there.

What I don't want is for this feature to *depend* on the users having
CREATE rights on the database, as that would allow them to create other
schemas (perhaps even one which is named the same as a likely new user
whose account hasn't been created yet or they haven't logged in yet...).

Thanks!

Stephen

Greetings Petr, all,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:

On 07/03/18 13:14, Stephen Frost wrote:

* Noah Misch (noah@leadboat.com) wrote:

On Tue, Mar 06, 2018 at 09:28:21PM -0500, Stephen Frost wrote:

* Tom Lane (tgl@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.

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. Those are two different things and
I don't think we should mix them.

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

I'm not sure that I see an issue with that- if it's dropped then it gets
recreated when that user logs back in. The systems I'm aware of, as
best as I can recall, didn't have any particular check or explicit
additional behavior for such a case.

Thanks!

Stephen