Re: public schema default ACL

On Thu, Aug 6, 2020 at 3:34 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> Greetings,
>
> * Magnus Hagander (magnus(at)hagander(dot)net) wrote:
> > On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > > * Noah Misch (noah(at)leadboat(dot)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.
>

Agreed. But I think it would get extra value from also being run on every
pg_dump at least to throw "important warnings".

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

That'd certainly be useful, but we'd have to be careful about the potential
for feature creep :) In theory there is no limitation at all on what such a
tool would do :) But for example limiting it to explicitly the things that
we have covered in release notes or side-effects of upgrades would be a
reasonable limitation. In which case you might not need the switches?

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

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

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>