Re: public schema default ACL

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)

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

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

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)

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

+1. This feels mostly like a weird quirk in the current system. Having the
database owner own it would feel a lot more logical.

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

+<many>.

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