Re: public schema default ACL

On Sun, Aug 2, 2020 at 11:30 PM Noah Misch <noah(at)leadboat(dot)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.