Re: public schema default ACL

On Sun, Aug 2, 2020 at 11:30 PM Noah Misch <noah@leadboat.com> wrote:

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

Both, as well as a reconsideration of not providing an escape hatch to the
search_path change as part of dump/restore in response to a number of
emails to these lists.

I like an option 2 that simply and quickly allows a DBA to setup a system
with zero-trust and have all grants be made explicitly.  This would go
beyond just the public schema and basically remove the concept of grants to
the built-in PUBLIC group.

I like option 3 for the user-friendly default option that has as few
compatibility issues compared to today as possible.

David J.