Re: public schema default ACL

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