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-7a8488ea1...@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
