On Sun, Aug 2, 2020 at 11:30 PM Noah Misch <n...@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.
