Re: public schema default ACL

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