Re: public schema default ACL

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