Thread: public schema default ACL

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.

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?

Thanks,
nm

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

On 07/03/18 17:55, Stephen Frost wrote:
> 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.

Well, what $user in search_path resolves to rather than what search_path is.

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

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

Greetings Petr,

* Petr Jelinek (petr.jelinek@2ndquadrant.com) wrote:
> On 07/03/18 17:55, Stephen Frost wrote:
> > 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.
> 
> 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

On 3/7/18 10:05, Stephen Frost wrote:
> I liken this to a well-known and well-trodden feature for auto creating
> user home directories on Unix.

I don't think likening schemas to home directories is really addressing
the most typical use cases.  Database contents are for the most part
carefully constructed in a collaborative way.  If your organization has
three DBAs foo, bar, and baz, it's quite unlikely that they will want to
create or look at objects in schemas named foo, bar, or baz.  More
likely, they will be interested in the schemas myapp or myotherapp.  Or
they don't care about schemas and will want the database to behave as if
there wasn't a schema layer between the database and the tables.

The existing structures are not bad.  They work for a lot of users.  The
problem is just that by default everyone can do whatever they want in a
shared space.  The fix is probably to not let them do that.  What is
being discussed here instead is to let them do whatever they want in
their own non-shared spaces.  That addresses the security concern, but
it doesn't support the way people actually work right now.

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

On Wed, Mar 7, 2018 at 5:11 PM, David G. Johnston
<david.g.johnston@gmail.com> wrote:
> I still feel like I want to mull this over more but auto-creating schemas
> strikes me as being "spooky action at a distance".

I don't think that it's a terrible proposal, but I don't see it as
fixing the real issue.  If we do something even as simple as removing
'public' from the default search_path, then I predict that a very
significant number of people will run pg_upgrade (or dump and restore,
or logically replicate the database), restart their application, and
find that it no longer works and they have absolutely no idea what has
gone wrong or how to fix it.  That seems like a major usability fail
to me, and auto-creating per-user schemas does absolutely nothing to
improve the situation.  That's not to say that it's a bad idea;
honestly, I think it's kind of nifty, and it certainly makes things a
lot nicer if there's no public schema any more because it makes CREATE
TABLE work out of the box again, something that we certainly want.
But if we don't have some solution to the problem of upgrade =>
everything breaks, then I don't think we really have a good solution
here.

I also wonder why we're all convinced that this urgently needs to be
changed.  I agree that the default configuration we ship is not the
most secure configuration that we could ship.  However, I think it's a
big step from saying that the configuration is not as secure as it
could be to saying that we absolutely must change it for v11.  We have
shipped tons of releases with sslmode=prefer and a wide-open
pg_hba.conf, and those aren't the most secure default configurations
either.  And changing either of those things would probably break a
lot fewer users than the changes being proposed on this thread.  This
issue isn't something that is brand new in a recent release of
PostgreSQL, and a lot of users are unaffected by it.  People need to
be aware that having the Donald Trump campaign and the Hilary Clinton
campaign share access to the same public schema, to which both
campaigns have CREATE and USAGE access, is probably asking for
trouble, but to be honest I suspect a fair number of users had figured
that out well before this security release went out the door.

It's certainly worth considering ideas for improving PostgreSQL's
security out-of-the-box, but the sky isn't falling, and it appears to
me that the risk of collateral damage from changes in this area is
pretty high.

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

On Wed, Mar 07, 2018 at 09:22:16AM -0500, Peter Eisentraut wrote:
> 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.

That's a good point.  Worse, a user with CREATEDB privilege would be able to
create new databases and immediately create and use any schema _except_
public.  That is rather silly.

On Wed, Mar 07, 2018 at 07:14:43AM -0500, 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.

I had in mind a site with diverse database owners, where the administrators
(folks with CREATEROLE or superuser) don't know every database owner
preference.  If we had a SCHEMA_CREATE like you describe, I expect its
documentation would say something like this:

  Since SCHEMA_CREATE provides the user one writable schema in each database,
  this allows the user to create permanent objects in any database that
  permits them to connect.  The database owner can prevent that by creating
  the schema in advance of the user's first login.  However, once the user has
  connected once, a non-superuser database owner cannot modify or drop it.

Is that good enough?

On Thu, Mar 08, 2018 at 02:00:23PM -0500, Robert Haas wrote:
> I also wonder why we're all convinced that this urgently needs to be
> changed.  I agree that the default configuration we ship is not the
> most secure configuration that we could ship.  However, I think it's a
> big step from saying that the configuration is not as secure as it
> could be to saying that we absolutely must change it for v11.

Did someone say that?  I, for one, wanted to change it but didn't intend to
present it as a "must change".

On Thu, Mar 08, 2018 at 11:14:59PM -0800, Noah Misch wrote:
> On Thu, Mar 08, 2018 at 02:00:23PM -0500, Robert Haas wrote:
> > I also wonder why we're all convinced that this urgently needs to be
> > changed.  I agree that the default configuration we ship is not the
> > most secure configuration that we could ship.  However, I think it's a
> > big step from saying that the configuration is not as secure as it
> > could be to saying that we absolutely must change it for v11.
> 
> Did someone say that?  I, for one, wanted to change it but didn't intend to
> present it as a "must change".

In light of the mixed reception, I am withdrawing this proposal.

On Fri, Mar 23, 2018 at 07:47:39PM -0700, Noah Misch wrote:
> In light of the mixed reception, I am withdrawing this proposal.

I'd like to reopen this.  Reception was mixed, but more in favor than against.
Also, variations on the idea trade some problems for others and may be more
attractive.  The taxonomy of variations has three important dimensions:

Interaction with dump/restore (including pg_upgrade) options:
a. If the schema has a non-default ACL, dump/restore reproduces it.
   Otherwise, the new default prevails.
b. Dump/restore always reproduces the schema ACL.

Initial ownership of schema "public" options:
1. Bootstrap superuser owns it.  (Without superuser cooperation, database
   owners can't drop it or create objects in it.)
2. Don't create the schema during initdb.  Database owners can create it or
   any other schema.  (A superuser could create it in template1, which
   converts an installation to option (1).)
3. Database owner owns it.  (One might implement this by offering ALTER SCHEMA
   x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID meaning
   "refer to pg_database.datdba".  A superuser could issue DDL to convert to
   option (1) or (2).)

Automatic creation of $user schemas options:
X. Automatic schema creation doesn't exist.
Y. Create $user schemas on-demand (at login time or CREATE TABLE/CREATE
   FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option in the
   CREATE ROLE statement.
Z. Like (Y), but SCHEMA_CREATE is the default.

I started the thread by proposing (a)(1)(X) and mentioning (b)(1)(X) as an
alternative.  Given the compatibility concerns, I now propose ruling out (a)
in favor of (b).
http://postgr.es/m/0e61bd66-07a2-255b-2b0f-7a8488ea1647@2ndquadrant.com
identified (b)(2)(X) and identified the problem with (1).

I dislike (Z), because it requires updating security guidelines to specify
NOSCHEMA_CREATE; I think it would be better to leave $SUBJECT unchanged than
to adopt (Z).  I like (Y) from an SQL standard perspective, but I don't think
it resolves the ease-of-first-use objections raised against (a)(1)(X).  (If
changing the public schema ACL is too much of an obstacle for a DBA, adopting
SCHEMA_CREATE is no easier.)  Hence, I propose ruling out (Y) and (Z).

That leaves the choice between (2) and (3).  Under (b)(2)(X), first-use guides
would need to add some CREATE SCHEMA.  While (3) avoids that, some users may
find themselves setting ownership back to the bootstrap superuser.  (3) also
makes the system more complex overall.

Between (b)(2)(X) and (b)(3)(X), what are folks' preferences?  Does anyone
strongly favor some other option (including the option of changing nothing)
over both of those two?

Thanks,
nm

On Mon, Aug 3, 2020 at 2:30 AM Noah Misch <noah@leadboat.com> wrote:
> Between (b)(2)(X) and (b)(3)(X), what are folks' preferences?  Does anyone
> strongly favor some other option (including the option of changing nothing)
> over both of those two?

I don't think we have any options here that are secure but do not
break backward compatibility. The present situation, with a writable
public schema, is equivalent to a UNIX system in which /usr/bin is
drwxrwxrwt. Nobody would seriously propose that such a system design
is secure, not so much because it's intrinsically broken if everyone
is careful not to execute any executables they don't know to have been
deposited by people they trust, but because it's quite easy to
accidentally execute one that isn't. However, if people are used to
being able to deposit stuff in /usr/bin and you tell them that they
now can't (because the permissions will henceforth be drwxr-xr-x or
the directly won't exist at all) then some of them are going to
complain. I don't know what to do about that: it's a straightforward
trade-off between security and backward compatibility, and you can't
have both.

I support the idea of having an automatic schema creation option. I
think that would be quite a cool thing to have, whether it's the
default (Y) or not (Z). But I don't know how to choose between (1),
(2), and (3).

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

On Sun, Aug  2, 2020 at 11:30:50PM -0700, Noah Misch wrote:
> On Fri, Mar 23, 2018 at 07:47:39PM -0700, Noah Misch wrote:
> > In light of the mixed reception, I am withdrawing this proposal.
> 
> I'd like to reopen this.  Reception was mixed, but more in favor than against.
> Also, variations on the idea trade some problems for others and may be more
> attractive.  The taxonomy of variations has three important dimensions:
> 
> Interaction with dump/restore (including pg_upgrade) options:
> a. If the schema has a non-default ACL, dump/restore reproduces it.
>    Otherwise, the new default prevails.
> b. Dump/restore always reproduces the schema ACL.

I am worried that someone _slightly_ modifies the ACL permissions on the
schema, and we reproduce it, and they think they are secure, but they
are not.  I guess for the public, and change would be to make it more
secure, so maybe this works, but it seems tricky.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

Greetings,

* Noah Misch (noah@leadboat.com) wrote:
> I'd like to reopen this.  Reception was mixed, but more in favor than against.
> Also, variations on the idea trade some problems for others and may be more
> attractive.  The taxonomy of variations has three important dimensions:
>
> Interaction with dump/restore (including pg_upgrade) options:
> a. If the schema has a non-default ACL, dump/restore reproduces it.
>    Otherwise, the new default prevails.
> b. Dump/restore always reproduces the schema ACL.
>
> Initial ownership of schema "public" options:
> 1. Bootstrap superuser owns it.  (Without superuser cooperation, database
>    owners can't drop it or create objects in it.)
> 2. Don't create the schema during initdb.  Database owners can create it or
>    any other schema.  (A superuser could create it in template1, which
>    converts an installation to option (1).)
> 3. Database owner owns it.  (One might implement this by offering ALTER SCHEMA
>    x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID meaning
>    "refer to pg_database.datdba".  A superuser could issue DDL to convert to
>    option (1) or (2).)
>
> Automatic creation of $user schemas options:
> X. Automatic schema creation doesn't exist.
> Y. Create $user schemas on-demand (at login time or CREATE TABLE/CREATE
>    FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option in the
>    CREATE ROLE statement.
> Z. Like (Y), but SCHEMA_CREATE is the default.
>
> I started the thread by proposing (a)(1)(X) and mentioning (b)(1)(X) as an
> alternative.  Given the compatibility concerns, I now propose ruling out (a)
> in favor of (b).

I agree that we don't want to effectively change these privileges on a
dump/restore or pg_upgrade.

> I dislike (Z), because it requires updating security guidelines to specify
> NOSCHEMA_CREATE; I think it would be better to leave $SUBJECT unchanged than
> to adopt (Z).  I like (Y) from an SQL standard perspective, but I don't think
> it resolves the ease-of-first-use objections raised against (a)(1)(X).  (If
> changing the public schema ACL is too much of an obstacle for a DBA, adopting
> SCHEMA_CREATE is no easier.)  Hence, I propose ruling out (Y) and (Z).

I'm also in favor of having some flavor of automatic schema creation,
but I view that as something independent from this discussion and which
this change shouldn't depend on.

> That leaves the choice between (2) and (3).  Under (b)(2)(X), first-use guides
> would need to add some CREATE SCHEMA.  While (3) avoids that, some users may
> find themselves setting ownership back to the bootstrap superuser.  (3) also
> makes the system more complex overall.
>
> Between (b)(2)(X) and (b)(3)(X), what are folks' preferences?  Does anyone
> strongly favor some other option (including the option of changing nothing)
> over both of those two?

Having the database owner own the public schema makes the most sense to
me- that this doesn't happen today has always seemed a bit odd to me as,
notionally, you'd imagine the "owner" of a database as, well, owning the
objects in that database (clearly they shouldn't actually own system
catalogs or functions or such, but the public schema isn't some internal
thing like the system catalogs and such).  Having the database owner not
have to jump through hoops to create objects immediately upon connection
to a new database also seems like it reduces the compatibility impact
that this will have.

In general, I'm still in favor of the overall change and moving to
better and more secure defaults.

Thanks,

Stephen

On 2020-08-03 15:46, Robert Haas wrote:
> However, if people are used to
> being able to deposit stuff in /usr/bin and you tell them that they
> now can't (because the permissions will henceforth be drwxr-xr-x or
> the directly won't exist at all) then some of them are going to
> complain. I don't know what to do about that: it's a straightforward
> trade-off between security and backward compatibility, and you can't
> have both.

File system conventions, permissions, and restrictions have been changed 
many times in the history of Unix, Linux, and the like.  Recent examples 
are /usr/bin and /bin unification and that /tmp is changing to a 
per-user mount.  There are of course always a few complaints and some 
breakage, but generally this has been going well and is usually 
appreciated overall.

The important things in my mind are that you keep an easy onboarding 
experience (you can do SQL things without having to create and unlock a 
bunch of things first) and that advanced users can do the things they 
want to do *somehow*.

As an example, per-user /tmp is not hardcoded into the kernel, it's just 
a run-time configuration.  If you want it to behave differently, you can 
set that up.

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

On Mon, Aug 03, 2020 at 09:46:23AM -0400, Robert Haas wrote:
> On Mon, Aug 3, 2020 at 2:30 AM Noah Misch <noah@leadboat.com> wrote:
> > Between (b)(2)(X) and (b)(3)(X), what are folks' preferences?  Does anyone
> > strongly favor some other option (including the option of changing nothing)
> > over both of those two?
> 
> I don't think we have any options here that are secure but do not
> break backward compatibility.

I agree, but compatibility breaks vary in pain caused.  I want to offer a
simple exit to a backward-compatible configuration, and I want a $NEW_DEFAULT
pleasing enough that a decent fraction of deployments keep $NEW_DEFAULT (forgo
the exit).  The move to default standard_conforming_strings=on is an example
to follow (editing postgresql.conf was the simple exit).

> I don't know how to choose between (1), (2), and (3).

One way is to envision deployments you know and think about a couple of
questions in the context of those deployments.  If $EACH_OPTION happened,
would this deployment keep $NEW_DEFAULT, override $NEW_DEFAULT to some other
secure configuration, or exit to $v13_DEFAULT?  Where the answer is "exit",
would those deployments rate the exit recipe easy, medium, or difficult?

On Mon, Aug 03, 2020 at 07:46:02PM +0200, Peter Eisentraut wrote:
> The important things in my mind are that you keep an easy onboarding
> experience (you can do SQL things without having to create and unlock a
> bunch of things first) and that advanced users can do the things they want
> to do *somehow*.

Makes sense.  How do these options differ in ease of onboarding?  In that
light, what option would you pick?

Greetings,

* Magnus Hagander (magnus@hagander.net) wrote:
> On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost <sfrost@snowman.net> wrote:
> > * Noah Misch (noah@leadboat.com) wrote:
> > > I'd like to reopen this.  Reception was mixed, but more in favor than
> > against.
> > > Also, variations on the idea trade some problems for others and may be
> > more
> > > attractive.  The taxonomy of variations has three important dimensions:
> > >
> > > Interaction with dump/restore (including pg_upgrade) options:
> > > a. If the schema has a non-default ACL, dump/restore reproduces it.
> > >    Otherwise, the new default prevails.
> > > b. Dump/restore always reproduces the schema ACL.
> > >
> > > Initial ownership of schema "public" options:
> > > 1. Bootstrap superuser owns it.  (Without superuser cooperation, database
> > >    owners can't drop it or create objects in it.)
> > > 2. Don't create the schema during initdb.  Database owners can create it
> > or
> > >    any other schema.  (A superuser could create it in template1, which
> > >    converts an installation to option (1).)
> > > 3. Database owner owns it.  (One might implement this by offering ALTER
> > SCHEMA
> > >    x OWNER TO DATABASE_OWNER, which sets nspowner to a system OID meaning
> > >    "refer to pg_database.datdba".  A superuser could issue DDL to
> > convert to
> > >    option (1) or (2).)
> > >
> > > Automatic creation of $user schemas options:
> > > X. Automatic schema creation doesn't exist.
> > > Y. Create $user schemas on-demand (at login time or CREATE TABLE/CREATE
> > >    FUNCTION/etc. time) if the DBA specified a "SCHEMA_CREATE" option in
> > the
> > >    CREATE ROLE statement.
> > > Z. Like (Y), but SCHEMA_CREATE is the default.
> > >
> > > I started the thread by proposing (a)(1)(X) and mentioning (b)(1)(X) as
> > an
> > > alternative.  Given the compatibility concerns, I now propose ruling out
> > (a)
> > > in favor of (b).
> >
> > I agree that we don't want to effectively change these privileges on a
> > dump/restore or pg_upgrade.
>
> Agreed. But it might be worthwhile having pg_dump spit out something like
> "current defaults are insecure, pass in parameter --update-default-acls to
> migrate to new defaults" when it detects the old default ones. (Or even
> specifically look for known insecure ones, like people who just added
> things to the acl which already had public with create -- obviously there's
> a limit how far one can go there)

Interesting idea, though that seems like it would be an extremely useful
*independent* tool from pg_dump (but, sure, we could run it as part of
pg_dump too).  Indeed, such tools already exist and having one of our
own would be nice.

I wonder if we should also consider having a tool for post-release
updates/fixes (eg: catalog changes).  Today we currently "deploy" such
fixes through the release notes, which isn't great.  Not sure why I
thought of that as being related but maybe it's not crazy to have the
same tool for both..?

pg_checkdb
  -- catalog updates
  -- security
  -- other stuff?

> > I dislike (Z), because it requires updating security guidelines to specify
> > > NOSCHEMA_CREATE; I think it would be better to leave $SUBJECT unchanged
> > than
> > > to adopt (Z).  I like (Y) from an SQL standard perspective, but I don't
> > think
> > > it resolves the ease-of-first-use objections raised against (a)(1)(X).
> > (If
> > > changing the public schema ACL is too much of an obstacle for a DBA,
> > adopting
> > > SCHEMA_CREATE is no easier.)  Hence, I propose ruling out (Y) and (Z).
> >
> > I'm also in favor of having some flavor of automatic schema creation,
> > but I view that as something independent from this discussion and which
> > this change shouldn't depend on.
>
> I'm a bit torn on this one.
>
> Because, in the end, how many people *actually* want the "user<->schema"
> tie-in? While I've seen some people actually use it, they are very few and
> far apart, and mostly only connected with migrating over from
> $BIG_DATABASE_VENDOR. I think we'd find a lot more people who are annoyed
> by "I just created a table, and now I have to go clean up this weird schema
> that got auto-created for me".
>
> So on that, I'd definitely say Y over Z. Having it as an option would
> certainly find useful scenarios, but I think having it on by default would
> be annoying.

I tend to agree with this also.

> And it would also question whether $user should actually be in the default
> search_path at all, or not.

That's certainly an interesting question.

> In the comparison with filesystems, people are used to creating directories
> before placing files in them... (except those that put all their files
> directly on their desktop, but those are not likely going to be the ones
> creating objects in the database)

Not sure how much it happens in these days of docker and containers, but
certainly it was common at one point to have home directories
automatically created on login.  There's one particularly large
difference here though- home directories go in /home/ (or whatever) and
have a specific namespace, which our schemas don't.  That is to say, if
someone has CREATE rights on the database they can create an 'sfrost'
schema that they own, dump whatever they want into it, and then it's in
my default search_path when I log in, even if this feature to
auto-create role schemas exists.  Sure, you could argue that in the unix
case, that would have been an 'admin' user to be able to make a
directory in /home/, but we haven't got any other way to make
'directories', so perhaps the analogy just doesn't fit close enough.

Thanks,

Stephen

On Mon, Aug 03, 2020 at 11:22:48AM -0400, Bruce Momjian wrote:
> On Sun, Aug  2, 2020 at 11:30:50PM -0700, Noah Misch wrote:
> > On Fri, Mar 23, 2018 at 07:47:39PM -0700, Noah Misch wrote:
> > > In light of the mixed reception, I am withdrawing this proposal.
> > 
> > I'd like to reopen this.  Reception was mixed, but more in favor than against.
> > Also, variations on the idea trade some problems for others and may be more
> > attractive.  The taxonomy of variations has three important dimensions:
> > 
> > Interaction with dump/restore (including pg_upgrade) options:
> > a. If the schema has a non-default ACL, dump/restore reproduces it.
> >    Otherwise, the new default prevails.
> > b. Dump/restore always reproduces the schema ACL.
> 
> I am worried that someone _slightly_ modifies the ACL permissions on the
> schema, and we reproduce it, and they think they are secure, but they
> are not.  I guess for the public, and change would be to make it more
> secure, so maybe this works, but it seems tricky.

Unless someone advocates for (a), we have dodged that problem, right?

On Mon, Aug 10, 2020 at 10:21:06AM +0200, Magnus Hagander wrote:
> On Thu, Aug 6, 2020 at 3:34 PM Stephen Frost <sfrost@snowman.net> wrote:
> > Not sure how much it happens in these days of docker and containers, but
> > certainly it was common at one point to have home directories
> > automatically created on login.  There's one particularly large
> > difference here though- home directories go in /home/ (or whatever) and
> > have a specific namespace, which our schemas don't.  That is to say, if
> > someone has CREATE rights on the database they can create an 'sfrost'
> > schema that they own, dump whatever they want into it, and then it's in
> > my default search_path when I log in, even if this feature to
> > auto-create role schemas exists.  Sure, you could argue that in the unix
> > case, that would have been an 'admin' user to be able to make a
> > directory in /home/, but we haven't got any other way to make
> > 'directories', so perhaps the analogy just doesn't fit close enough.
> 
> Yeah, the fact that a owner can just create a schema called "postgres" and
> thereby sticking things in the search path of postgres is not great. And
> that's not fixed by changing how "public" works, per any of the suggested
> methods I think. Only the database owner can do mean things there, but
> database owner != superuser (at least in theory).

https://www.postgresql.org/docs/devel/ddl-schemas.html#DDL-SCHEMAS-PATTERNS
does document the power of untrusted database owners.  Unfortunately, I've not
seen or thought of a specification of database owner powers that included
enough power to be useful yet not enough power to cause mischief.

On Wed, Aug 05, 2020 at 10:05:28PM -0700, Noah Misch wrote:
> On Mon, Aug 03, 2020 at 07:46:02PM +0200, Peter Eisentraut wrote:
> > The important things in my mind are that you keep an easy onboarding
> > experience (you can do SQL things without having to create and unlock a
> > bunch of things first) and that advanced users can do the things they want
> > to do *somehow*.
> 
> Makes sense.  How do these options differ in ease of onboarding?  In that
> light, what option would you pick?

Ping.  Your statement above seems to suggest one of the options more than
others, but I'd rather not assume you see it the same way.

On Wed, Aug 05, 2020 at 10:00:02PM -0700, Noah Misch wrote:
> On Mon, Aug 03, 2020 at 09:46:23AM -0400, Robert Haas wrote:
> > On Mon, Aug 3, 2020 at 2:30 AM Noah Misch <noah@leadboat.com> wrote:
> > > Between (b)(2)(X) and (b)(3)(X), what are folks' preferences?  Does anyone
> > > strongly favor some other option (including the option of changing nothing)
> > > over both of those two?
> > 
> > I don't think we have any options here that are secure but do not
> > break backward compatibility.
> 
> I agree, but compatibility breaks vary in pain caused.  I want to offer a
> simple exit to a backward-compatible configuration, and I want a $NEW_DEFAULT
> pleasing enough that a decent fraction of deployments keep $NEW_DEFAULT (forgo
> the exit).  The move to default standard_conforming_strings=on is an example
> to follow (editing postgresql.conf was the simple exit).
> 
> > I don't know how to choose between (1), (2), and (3).
> 
> One way is to envision deployments you know and think about a couple of
> questions in the context of those deployments.  If $EACH_OPTION happened,
> would this deployment keep $NEW_DEFAULT, override $NEW_DEFAULT to some other
> secure configuration, or exit to $v13_DEFAULT?  Where the answer is "exit",
> would those deployments rate the exit recipe easy, medium, or difficult?

It sounds like you might prefer to wait for better ideas and not change
$SUBJECT for now.  Is that right?

On Thu, Aug 06, 2020 at 12:48:17PM +0200, Magnus Hagander wrote:
> On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost <sfrost@snowman.net> wrote:
> > * Noah Misch (noah@leadboat.com) wrote:
> > > Between (b)(2)(X) and (b)(3)(X), what are folks' preferences?  Does anyone
> > > strongly favor some other option (including the option of changing nothing)
> > > over both of those two?
> > 
> > Having the database owner own the public schema makes the most sense to
> > me- that this doesn't happen today has always seemed a bit odd to me as,
> > notionally, you'd imagine the "owner" of a database as, well, owning the
> > objects in that database (clearly they shouldn't actually own system
> > catalogs or functions or such, but the public schema isn't some internal
> > thing like the system catalogs and such).  Having the database owner not
> > have to jump through hoops to create objects immediately upon connection
> > to a new database also seems like it reduces the compatibility impact
> > that this will have.
> > 
> 
> +1. This feels mostly like a weird quirk in the current system. Having the database owner own it would feel a lot
morelogical.
 
> 
> > In general, I'm still in favor of the overall change and moving to
> > better and more secure defaults.
> 
> +<many>.

(b)(2)(X) got no votes.

(b)(3)(X) got votes from Stephen Frost and Magnus Hagander.  I'll pick it,
too.  Peter Eisentraut did not vote, but I'm counting him as +0.2 for it in
light of this comment:

On Mon, Aug 03, 2020 at 07:46:02PM +0200, Peter Eisentraut wrote:
> The important things in my mind are that you keep an easy onboarding
> experience (you can do SQL things without having to create and unlock a
> bunch of things first) and that advanced users can do the things they want
> to do *somehow*.

Robert Haas did not vote, but this seems more consistent with a request to
wait for better ideas and change nothing for now:

On Mon, Aug 03, 2020 at 09:46:23AM -0400, Robert Haas wrote:
> I don't think we have any options here that are secure but do not
> break backward compatibility.

Overall, that's 3.2 votes for (b)(3)(X) and 0.0 to 1.0 votes for changing
nothing.  That suffices to proceed with (b)(3)(X).  However, given the few
votes and the conspicuous non-responses, work in this area has a high risk of
failure.  Hence, I will place it at a low-priority position in my queue.
Would anyone else would like to take over implementation?


More details on the semantics I'll use:

1. initdb will change like this:
   @@ -1721 +1721 @@ setup_privileges(FILE *cmdfd)
   -        "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
   +        "GRANT USAGE ON SCHEMA public TO PUBLIC;\n\n",
   +        "ALTER SCHEMA public OWNER TO DATABASE_OWNER;\n\n",

2. If schema public does not exist, pg_dump will emit nothing about it.  This
   is what happens today.  (I suspect it would be better for pg_dump to emit
   DROP SCHEMA public RESTRICT, but that is drifting offtopic for $SUBJECT.)
   Otherwise, when dumping from v13 or earlier, pg_dump will always emit
   REVOKE and/or GRANT statements to reproduce the old ACL.  When dumping from
   v14 or later, pg_dump will use pg_init_privs to compute GRANT and REVOKE
   statements, as it does today.  (This may interfere with cross-version
   pg_upgrade testing.  I haven't looked at how best to fix that.  Perhaps add
   more fix_sql in test.sh.)

3. pg_upgrade from v13 to later versions will transfer template1's ACL for
   schema public, even if that ACL was unchanged since v13 initdb.  (This is
   purely a consequence of the pg_dump behavior decision.)  template0 will
   keep the new default.

4. OWNER TO DATABASE_OWNER will likely be available for schemas only, though I
   might propose it for all object classes if class-specific complexity proves
   negligible.

5. ALTER DATABASE OWNER TO changes access control decisions involving
   nspowner==DATABASE_OWNER.  Speed of nspacl checks is more important than
   reacting swiftly to ALTER DATABASE OWNER TO.  Sessions running concurrently
   will be eventually-consistent with respect to the ALTER DATABASE.
   (Existing access control decisions, too, allow this sort of anomaly.)

6. pg_dump hasn't been reproducing ALTER SCHEMA public OWNER TO.  That's a
   mild defect today, but it wouldn't be mild anymore.  We'll need pg_dump of
   v13 databases to emit "ALTER SCHEMA public OWNER TO postgres" and for a v14
   => v15 upgrade to propagate that.  This project can stand by itself; would
   anyone else like to own it?

Thanks,
nm

On 2020-10-31 17:35, Noah Misch wrote:
> Overall, that's 3.2 votes for (b)(3)(X) and 0.0 to 1.0 votes for changing
> nothing.  That suffices to proceed with (b)(3)(X).  However, given the few
> votes and the conspicuous non-responses, work in this area has a high risk of
> failure.  Hence, I will place it at a low-priority position in my queue.

My vote would also be (b)(3)(X).  Allowing the database owner to manage 
the public schema within their database makes a lot of sense, 
independent of any overarching goals.

I'm not convinced, however, that this would would really move the needle 
in terms of the general security-uneasiness about the public schema and 
search paths.  AFAICT, in any of your proposals, the default would still 
be to have the public schema world-writable and in the path.

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

Greetings,

* Peter Eisentraut (peter.eisentraut@2ndquadrant.com) wrote:
> On 2020-10-31 17:35, Noah Misch wrote:
> >Overall, that's 3.2 votes for (b)(3)(X) and 0.0 to 1.0 votes for changing
> >nothing.  That suffices to proceed with (b)(3)(X).  However, given the few
> >votes and the conspicuous non-responses, work in this area has a high risk of
> >failure.  Hence, I will place it at a low-priority position in my queue.
>
> My vote would also be (b)(3)(X).  Allowing the database owner to manage the
> public schema within their database makes a lot of sense, independent of any
> overarching goals.

Agreed.

> I'm not convinced, however, that this would would really move the needle in
> terms of the general security-uneasiness about the public schema and search
> paths.  AFAICT, in any of your proposals, the default would still be to have
> the public schema world-writable and in the path.

Looks like the proposal wasn't explicitly clear on this point and I, at
least, took the proposal to implicitly also be saying that the public
schema's ACL would be the default- meaning that the owner would be able
to create objects in the schema and to use it, but other users wouldn't
be able to (or, perhaps, that USAGE rights would be GRANT'd to public,
but not CREATE).

Seems we probably need another round of votes where it's made very clear
what the default ACL (not from a dump/reload) on the public schema would
be.

Thanks,

Stephen

On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
<peter.eisentraut@2ndquadrant.com> wrote:
> I'm not convinced, however, that this would would really move the needle
> in terms of the general security-uneasiness about the public schema and
> search paths.  AFAICT, in any of your proposals, the default would still
> be to have the public schema world-writable and in the path.

Noah's proposed change to initdb appears to involve removing CREATE
permission by default, so I don't think this is true.

It's hard to predict how many users that might inconvenience, but I
suppose it's probably a big number. On the other hand, the only
alternative is to continue shipping a configuration that, by default,
is potentially insecure. It's hard to decide which thing we should
care more about.

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

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com> wrote:
>> I'm not convinced, however, that this would would really move the needle
>> in terms of the general security-uneasiness about the public schema and
>> search paths.  AFAICT, in any of your proposals, the default would still
>> be to have the public schema world-writable and in the path.

> Noah's proposed change to initdb appears to involve removing CREATE
> permission by default, so I don't think this is true.

I assume that means removing *public* CREATE permissions, not the
owner's (which'd be the DB owner with the proposed changes).

> It's hard to predict how many users that might inconvenience, but I
> suppose it's probably a big number. On the other hand, the only
> alternative is to continue shipping a configuration that, by default,
> is potentially insecure. It's hard to decide which thing we should
> care more about.

Yeah.  The thing is, if we make it harder to create stuff in "public",
that's going to result in the path-of-least-resistance being to run
everything as the DB owner.  Which is better than running everything as
superuser (at least if DB owner != postgres), but still not exactly great.
Second least difficult thing is to re-grant public CREATE permissions,
putting things right back where they were.

I'm not sure how far we can expect to move things without creating a
bad on-boarding experience.  The folks who actually need cross-user
security already know what they have to do (or if not, that's a docs
problem not a code problem).  I'm inclined to think that first-time
users do not need that, though.

What potentially could move the needle is separate search paths for
relation lookup and function/operator lookup.  We have sort of stuck
our toe in that pond already by discriminating against pg_temp for
function/operator lookup, but we could make that more formalized and
controllable if there were distinct settings.  I'm not sure offhand
how much of a compatibility problem that produces.

            regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
> > <peter.eisentraut@2ndquadrant.com> wrote:
> >> I'm not convinced, however, that this would would really move the needle
> >> in terms of the general security-uneasiness about the public schema and
> >> search paths.  AFAICT, in any of your proposals, the default would still
> >> be to have the public schema world-writable and in the path.
>
> > Noah's proposed change to initdb appears to involve removing CREATE
> > permission by default, so I don't think this is true.

The original proposal didn't include that change (I don't think anyway,
the change you're referring to seems to have come after most of the
folks had voiced opinions..?), so I can understand someone being unclear
on this point.  Admittedly, I suspect most folks on this thread assumed,
as I did, that Noah was proposing to remove CREATE permission from
public on the public schema, and Peter was actually responding to the
email which included Noah's suggest initdb change, so it should have
been clear at that point anyway.  The only other relevant vote, I
believe, was from Magnus, so might be good to just make sure he's also
in favor of (b)(3)(X) with the understanding that it also involves
removing CREATE rights from the public schema from the public role.

(there are definitely days when I wish we didn't have a public schema,
simply because it would result in 'public' only ever meaning 'the
special role called public' ...)

> I assume that means removing *public* CREATE permissions, not the
> owner's (which'd be the DB owner with the proposed changes).

Yes, that's correct.

> > It's hard to predict how many users that might inconvenience, but I
> > suppose it's probably a big number. On the other hand, the only
> > alternative is to continue shipping a configuration that, by default,
> > is potentially insecure. It's hard to decide which thing we should
> > care more about.
>
> Yeah.  The thing is, if we make it harder to create stuff in "public",
> that's going to result in the path-of-least-resistance being to run
> everything as the DB owner.  Which is better than running everything as
> superuser (at least if DB owner != postgres), but still not exactly great.
> Second least difficult thing is to re-grant public CREATE permissions,
> putting things right back where they were.
>
> I'm not sure how far we can expect to move things without creating a
> bad on-boarding experience.  The folks who actually need cross-user
> security already know what they have to do (or if not, that's a docs
> problem not a code problem).  I'm inclined to think that first-time
> users do not need that, though.

This proposal strikes me as the right balance between having a decent
on-boarding experience for new users, who are likely to be using
superuser or DB owner from the start because they just want to get in
and look at things and play with PG, while still meaningfully moving us
away from having a world-writable schema in the default search path.

At least from seeing the users that start out with PG and then come to
the Slack or IRC channel asking questions, the on-boarding experience
today typically consists of 'apt install postgresql' and then complaints
that they aren't able to figure out how to log into PG (often asking
about what the default password is to log in as 'postgres', or why the
system is saying 'role "root" does not exist').  Once a user gets to the
point of understanding or wanting to create other roles in the system,
saying they need to create a schema for that role if they want it to be
able to create objects (just like a user needing a home directory)
doesn't seem likely to be all that unexpected.

Where we could possibly help in this regard might be to add some syntax
to CREATE ROLE to have it create a schema for the role too- this would
help in a couple of ways: we could give new users a single command to
get going with being able to create objects in a safe way, for their
user, and we would get information about schemas included in the
CREATE ROLE documentation, which doesn't say anything about schemas
currently.

> What potentially could move the needle is separate search paths for
> relation lookup and function/operator lookup.  We have sort of stuck
> our toe in that pond already by discriminating against pg_temp for
> function/operator lookup, but we could make that more formalized and
> controllable if there were distinct settings.  I'm not sure offhand
> how much of a compatibility problem that produces.

While I agree with the general idea of giving users more granularity
when it comes to what objects are allowed to be created by users, and
where, and how objects are looked up, I really don't think this would
end up being a sufficiently complete answer to a world-writable public
schema.  You don't have to be able to create functions or operators in
the public schema to make things dangerous for some other user poking
around at the tables or views that you are allowed to create there.

Thanks,

Stephen

On Mon, Nov 02, 2020 at 12:42:26PM -0500, Tom Lane wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
> > <peter.eisentraut@2ndquadrant.com> wrote:
> >> I'm not convinced, however, that this would would really move the needle
> >> in terms of the general security-uneasiness about the public schema and
> >> search paths.  AFAICT, in any of your proposals, the default would still
> >> be to have the public schema world-writable and in the path.
> 
> > Noah's proposed change to initdb appears to involve removing CREATE
> > permission by default, so I don't think this is true.
> 
> I assume that means removing *public* CREATE permissions, not the
> owner's (which'd be the DB owner with the proposed changes).

My plan is for the default to become:

  GRANT USAGE ON SCHEMA public TO PUBLIC;
  ALTER SCHEMA public OWNER TO DATABASE_OWNER;  -- new syntax

Hence, the dbowner can create objects in the schema or grant that ability to
others.  Anyone can e.g. SELECT/UPDATE tables in the schema or call functions
in the schema, subject to per-table/per-function ACLs.  ACK that this wasn't
explicit on the thread until a few days ago.  I kept universal USAGE because
the schema wouldn't be very "public" without that.

> > It's hard to predict how many users that might inconvenience, but I
> > suppose it's probably a big number. On the other hand, the only
> > alternative is to continue shipping a configuration that, by default,
> > is potentially insecure. It's hard to decide which thing we should
> > care more about.
> 
> Yeah.  The thing is, if we make it harder to create stuff in "public",
> that's going to result in the path-of-least-resistance being to run
> everything as the DB owner.  Which is better than running everything as
> superuser (at least if DB owner != postgres), but still not exactly great.
> Second least difficult thing is to re-grant public CREATE permissions,
> putting things right back where they were.

That is factual; whenever a strategy is easier to start than its alternatives,
folks will overconsume that strategy.  One can mitigate that by introducing
artificial obstacles to use of the discouraged strategy, but that will tend to
harm the onboarding experience.  Option (b)(2)(X) would have done that.

When folks end up creating all objects as the database owner, we still get the
win for roles that don't create permanent objects.  It's decently common to
have an app run as a user that queries existing permanent objects, not issuing
permanent-object DDL.  That works under both v13 and future defaults.

> What potentially could move the needle is separate search paths for
> relation lookup and function/operator lookup.  We have sort of stuck
> our toe in that pond already by discriminating against pg_temp for
> function/operator lookup, but we could make that more formalized and
> controllable if there were distinct settings.  I'm not sure offhand
> how much of a compatibility problem that produces.

Stephen raised a good point about this.  Separately, regarding compatibility,
suppose a v13 database has:

  CREATE FUNCTION f() RETURNS int LANGUAGE sql SECURITY DEFINER
  AS 'SELECT inner_f()' SET search_path = a, b;

For compatibility, no value of the function-search-path setting should break
this function's ability to find a.inner_f(void).  Which definition of
function-search-path achieves this?

On Mon, Nov 2, 2020 at 1:41 PM Stephen Frost <sfrost@snowman.net> wrote:
> > What potentially could move the needle is separate search paths for
> > relation lookup and function/operator lookup.  We have sort of stuck
> > our toe in that pond already by discriminating against pg_temp for
> > function/operator lookup, but we could make that more formalized and
> > controllable if there were distinct settings.  I'm not sure offhand
> > how much of a compatibility problem that produces.
>
> While I agree with the general idea of giving users more granularity
> when it comes to what objects are allowed to be created by users, and
> where, and how objects are looked up, I really don't think this would
> end up being a sufficiently complete answer to a world-writable public
> schema.  You don't have to be able to create functions or operators in
> the public schema to make things dangerous for some other user poking
> around at the tables or views that you are allowed to create there.

I agree. Everything that can execute code is a risk, which also
includes things like triggers and RLS policies. Noah's certainly right
about the compatibility hazard, too.

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

On Mon, Nov  2, 2020 at 11:05:15PM -0800, Noah Misch wrote:
> On Mon, Nov 02, 2020 at 12:42:26PM -0500, Tom Lane wrote:
> > Robert Haas <robertmhaas@gmail.com> writes:
> > > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
> > > <peter.eisentraut@2ndquadrant.com> wrote:
> > >> I'm not convinced, however, that this would would really move the needle
> > >> in terms of the general security-uneasiness about the public schema and
> > >> search paths.  AFAICT, in any of your proposals, the default would still
> > >> be to have the public schema world-writable and in the path.
> > 
> > > Noah's proposed change to initdb appears to involve removing CREATE
> > > permission by default, so I don't think this is true.
> > 
> > I assume that means removing *public* CREATE permissions, not the
> > owner's (which'd be the DB owner with the proposed changes).
> 
> My plan is for the default to become:
> 
>   GRANT USAGE ON SCHEMA public TO PUBLIC;
>   ALTER SCHEMA public OWNER TO DATABASE_OWNER;  -- new syntax

Seems it would be better to create a predefined role that owns the
public schema, or at least has create permission for the public schema
--- that way, when you are creating a role, you can decide if the role
should have creation permissions in the public schema, rather than
having people always using the database owner for this purpose.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On Mon, Nov  2, 2020 at 01:41:09PM -0500, Stephen Frost wrote:
> At least from seeing the users that start out with PG and then come to
> the Slack or IRC channel asking questions, the on-boarding experience
> today typically consists of 'apt install postgresql' and then complaints
> that they aren't able to figure out how to log into PG (often asking
> about what the default password is to log in as 'postgres', or why the
> system is saying 'role "root" does not exist').  Once a user gets to the
> point of understanding or wanting to create other roles in the system,
> saying they need to create a schema for that role if they want it to be
> able to create objects (just like a user needing a home directory)
> doesn't seem likely to be all that unexpected.

It is a good point that the user has to create another user before this
becomes a usability issue.  It seems at the time the first user is
created (non-postgres), the admin needs to decide how the public schema
should behave.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

On Mon, Nov 09, 2020 at 02:56:53PM -0500, Bruce Momjian wrote:
> On Mon, Nov  2, 2020 at 11:05:15PM -0800, Noah Misch wrote:
> > My plan is for the default to become:
> > 
> >   GRANT USAGE ON SCHEMA public TO PUBLIC;
> >   ALTER SCHEMA public OWNER TO DATABASE_OWNER;  -- new syntax
> 
> Seems it would be better to create a predefined role that owns the
> public schema, or at least has create permission for the public schema
> --- that way, when you are creating a role, you can decide if the role
> should have creation permissions in the public schema, rather than
> having people always using the database owner for this purpose.

Defaulting to a specific predefined role empowers the role's members in all
databases simultaneously.  Folks who want it like that can create a role and
issue "ALTER SCHEMA public OWNER TO that_role" in template1.  What's the
better default?  I think that depends on whether you regard this schema as a
per-database phenomenon or a per-cluster phenomenon.

On Thu, Nov 12, 2020 at 06:36:39PM -0800, Noah Misch wrote:
> On Mon, Nov 09, 2020 at 02:56:53PM -0500, Bruce Momjian wrote:
> > On Mon, Nov  2, 2020 at 11:05:15PM -0800, Noah Misch wrote:
> > > My plan is for the default to become:
> > > 
> > >   GRANT USAGE ON SCHEMA public TO PUBLIC;
> > >   ALTER SCHEMA public OWNER TO DATABASE_OWNER;  -- new syntax
> > 
> > Seems it would be better to create a predefined role that owns the
> > public schema, or at least has create permission for the public schema
> > --- that way, when you are creating a role, you can decide if the role
> > should have creation permissions in the public schema, rather than
> > having people always using the database owner for this purpose.
> 
> Defaulting to a specific predefined role empowers the role's members in all
> databases simultaneously.  Folks who want it like that can create a role and
> issue "ALTER SCHEMA public OWNER TO that_role" in template1.  What's the
> better default?  I think that depends on whether you regard this schema as a
> per-database phenomenon or a per-cluster phenomenon.

Ah, I see your point.  I was just thinking we don't want everyone
logging in as the db user, or given super-user permissions, so haveing a
non-login role would help, but we can just document how to do it.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee

I'm attaching the patch for $SUBJECT, which applies atop the four patches from
the two other threads below.  For convenience of testing, I've included a
rollup patch, equivalent to applying all five patches.

On Sat, Oct 31, 2020 at 09:35:18AM -0700, Noah Misch wrote:
> More details on the semantics I'll use:
> 
> 1. initdb will change like this:
>    @@ -1721 +1721 @@ setup_privileges(FILE *cmdfd)
>    -        "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
>    +        "GRANT USAGE ON SCHEMA public TO PUBLIC;\n\n",
>    +        "ALTER SCHEMA public OWNER TO DATABASE_OWNER;\n\n",

(I ended up assigning the ownership via pg_namespace.dat, not here.)

> 2. If schema public does not exist, pg_dump will emit nothing about it.  This
>    is what happens today.  (I suspect it would be better for pg_dump to emit
>    DROP SCHEMA public RESTRICT, but that is drifting offtopic for $SUBJECT.)
>    Otherwise, when dumping from v13 or earlier, pg_dump will always emit
>    REVOKE and/or GRANT statements to reproduce the old ACL.

More precisely, it diffs the source database ownership and ACL to the v14
defaults, then emits ALTER, GRANT, and/or REVOKE as needed.  That yields no
GRANT or REVOKE if the source database is an early adopter of the new default.

>    When dumping from
>    v14 or later, pg_dump will use pg_init_privs to compute GRANT and REVOKE
>    statements, as it does today.

(It doesn't actually use pg_init_privs, but the effect is similar.)

>    (This may interfere with cross-version
>    pg_upgrade testing.  I haven't looked at how best to fix that.  Perhaps add
>    more fix_sql in test.sh.)

src/bin/pg_upgrade/test.sh doesn't need changes.  Upgrades from 9.6 (the first
version having pg_init_privs) or later get no new diffs.  Upgrades from v8.4
or v9.5 to v14 have a relevant diff before or after this change.  In master:

-REVOKE ALL ON SCHEMA public FROM PUBLIC;
-REVOKE ALL ON SCHEMA public FROM nm;
-GRANT ALL ON SCHEMA public TO nm;
-GRANT ALL ON SCHEMA public TO PUBLIC;

After $SUBJECT:

-REVOKE ALL ON SCHEMA public FROM PUBLIC;
-REVOKE ALL ON SCHEMA public FROM nm;
-GRANT ALL ON SCHEMA public TO nm;
+REVOKE USAGE ON SCHEMA public FROM PUBLIC;
 GRANT ALL ON SCHEMA public TO PUBLIC;

> 3. pg_upgrade from v13 to later versions will transfer template1's ACL for
>    schema public, even if that ACL was unchanged since v13 initdb.  (This is
>    purely a consequence of the pg_dump behavior decision.)  template0 will
>    keep the new default.
> 
> 4. OWNER TO DATABASE_OWNER will likely be available for schemas only, though I
>    might propose it for all object classes if class-specific complexity proves
>    negligible.

Class-specific complexity was negligible, so I made it available for all
objects.  The syntax is "OWNER TO pg_database_owner", because it's a special
predefined role.  That patch has its own thread:
https://postgr.es/m/20201228043148.GA1053024@rfd.leadboat.com

> 5. ALTER DATABASE OWNER TO changes access control decisions involving
>    nspowner==DATABASE_OWNER.  Speed of nspacl checks is more important than
>    reacting swiftly to ALTER DATABASE OWNER TO.  Sessions running concurrently
>    will be eventually-consistent with respect to the ALTER DATABASE.
>    (Existing access control decisions, too, allow this sort of anomaly.)
> 
> 6. pg_dump hasn't been reproducing ALTER SCHEMA public OWNER TO.  That's a
>    mild defect today, but it wouldn't be mild anymore.  We'll need pg_dump of
>    v13 databases to emit "ALTER SCHEMA public OWNER TO postgres" and for a v14
>    => v15 upgrade to propagate that.  This project can stand by itself; would
>    anyone else like to own it?

That patch has its own thread:
https://postgr.es/m/20201229134924.GA1431748@rfd.leadboat.com


Changing this ACL caused 13 of 202 tests to fail in "make check".  I first
intended to modify tests as needed for that suite to keep the default ACL.
For complicated cases, my strategy was to make a test create a schema and
change search_path.  However, that created large expected output diffs
(e.g. ~120 lines in updatable_views.out), mostly in EXPLAIN and \d output
bearing the schema name.  I didn't want that kind of obstacle to future
back-patched test updates, so I did make the first test install the old ACL.
All other in-tree suites do test the new default.

Thanks,
nm

On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
> I'm attaching the patch for $SUBJECT, which applies atop the four patches from
> the two other threads below.  For convenience of testing, I've included a
> rollup patch, equivalent to applying all five patches.

I committed prerequisites from one thread, so here's a rebased rollup patch.

On Sat, 2021-03-27 at 00:50 -0700, Noah Misch wrote:
> On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
> > I'm attaching the patch for $SUBJECT, which applies atop the four patches from
> > the two other threads below.  For convenience of testing, I've included a
> > rollup patch, equivalent to applying all five patches.
> 
> I committed prerequisites from one thread, so here's a rebased rollup patch.

I am happy to see this problem tackled!

Yours,
Laurenz Albe

On Sat, Mar 27, 2021 at 11:41:07AM +0100, Laurenz Albe wrote:
> On Sat, 2021-03-27 at 00:50 -0700, Noah Misch wrote:
> > On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
> > > I'm attaching the patch for $SUBJECT, which applies atop the four patches from
> > > the two other threads below.  For convenience of testing, I've included a
> > > rollup patch, equivalent to applying all five patches.
> > 
> > I committed prerequisites from one thread, so here's a rebased rollup patch.
> 
> I am happy to see this problem tackled!

Rebased.  I've pushed all prerequisites, so there's no longer a distinct
rollup patch.

On 30.06.21 03:37, Noah Misch wrote:
> On Sat, Mar 27, 2021 at 11:41:07AM +0100, Laurenz Albe wrote:
>> On Sat, 2021-03-27 at 00:50 -0700, Noah Misch wrote:
>>> On Sat, Feb 13, 2021 at 04:56:29AM -0800, Noah Misch wrote:
>>>> I'm attaching the patch for $SUBJECT, which applies atop the four patches from
>>>> the two other threads below.  For convenience of testing, I've included a
>>>> rollup patch, equivalent to applying all five patches.
>>>
>>> I committed prerequisites from one thread, so here's a rebased rollup patch.
>>
>> I am happy to see this problem tackled!
> 
> Rebased.  I've pushed all prerequisites, so there's no longer a distinct
> rollup patch.

I think this patch represents the consensus.

The documentation looks okay.  Some places still refer to PostgreSQL 13, 
which should now be changed to 14.

I tried a couple of upgrade scenarios and it appeared to do the right thing.

This patch is actually two separate changes: First, change the owner of 
the public schema to "pg_database_owner"; second, change the default 
privileges set on the public schema by initdb.  I was a bit surprised 
that the former hadn't already be done in PG14.  In any case, if there 
is still any doubt about the latter part, the former can surely go ahead 
separately if needed.

On Thu, Sep 02, 2021 at 12:36:51PM +0200, Peter Eisentraut wrote:
> I think this patch represents the consensus.
> 
> The documentation looks okay.  Some places still refer to PostgreSQL 13,
> which should now be changed to 14.

Thanks.  I'll update s/13/14/ and/or s/14/15/ before the next step.

> I tried a couple of upgrade scenarios and it appeared to do the right thing.
> 
> This patch is actually two separate changes: First, change the owner of the
> public schema to "pg_database_owner"; second, change the default privileges
> set on the public schema by initdb.  I was a bit surprised that the former
> hadn't already be done in PG14.

Interesting.  That change requires a7a7be1, which is also not in v14.

Do you plan to change the CF entry, or should it remain in Needs Review with
no assigned reviewer?

On 04.09.21 18:18, Noah Misch wrote:
>> I tried a couple of upgrade scenarios and it appeared to do the right thing.
>>
>> This patch is actually two separate changes: First, change the owner of the
>> public schema to "pg_database_owner"; second, change the default privileges
>> set on the public schema by initdb.  I was a bit surprised that the former
>> hadn't already be done in PG14.
> 
> Interesting.  That change requires a7a7be1, which is also not in v14.
> 
> Do you plan to change the CF entry, or should it remain in Needs Review with
> no assigned reviewer?

I've set it to ready for committer now.