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.
