Re: public schema default ACL

On Thu, Aug 06, 2020 at 12:48:17PM +0200, Magnus Hagander wrote:
> On Mon, Aug 3, 2020 at 5:26 PM Stephen Frost <sfrost(at)snowman(dot)net> wrote:
> > * Noah Misch (noah(at)leadboat(dot)com) wrote:
> > > 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?
> >
> > Having the database owner own the public schema makes the most sense to
> > me- that this doesn't happen today has always seemed a bit odd to me as,
> > notionally, you'd imagine the "owner" of a database as, well, owning the
> > objects in that database (clearly they shouldn't actually own system
> > catalogs or functions or such, but the public schema isn't some internal
> > thing like the system catalogs and such).  Having the database owner not
> > have to jump through hoops to create objects immediately upon connection
> > to a new database also seems like it reduces the compatibility impact
> > that this will have.
> >
>
> +1. This feels mostly like a weird quirk in the current system. Having the database owner own it would feel a lot more logical.
>
> > In general, I'm still in favor of the overall change and moving to
> > better and more secure defaults.
>
> +<many>.

(b)(2)(X) got no votes.

(b)(3)(X) got votes from Stephen Frost and Magnus Hagander. I'll pick it,
too. Peter Eisentraut did not vote, but I'm counting him as +0.2 for it in
light of this comment:

On Mon, Aug 03, 2020 at 07:46:02PM +0200, Peter Eisentraut wrote:
> The important things in my mind are that you keep an easy onboarding
> experience (you can do SQL things without having to create and unlock a
> bunch of things first) and that advanced users can do the things they want
> to do *somehow*.

Robert Haas did not vote, but this seems more consistent with a request to
wait for better ideas and change nothing for now:

On Mon, Aug 03, 2020 at 09:46:23AM -0400, Robert Haas wrote:
> I don't think we have any options here that are secure but do not
> break backward compatibility.

Overall, that's 3.2 votes for (b)(3)(X) and 0.0 to 1.0 votes for changing
nothing. That suffices to proceed with (b)(3)(X). However, given the few
votes and the conspicuous non-responses, work in this area has a high risk of
failure. Hence, I will place it at a low-priority position in my queue.
Would anyone else would like to take over implementation?

More details on the semantics I'll use:

1. initdb will change like this:
@@ -1721 +1721 @@ setup_privileges(FILE *cmdfd)
- "GRANT CREATE, USAGE ON SCHEMA public TO PUBLIC;\n\n",
+ "GRANT USAGE ON SCHEMA public TO PUBLIC;\n\n",
+ "ALTER SCHEMA public OWNER TO DATABASE_OWNER;\n\n",

2. If schema public does not exist, pg_dump will emit nothing about it. This
is what happens today. (I suspect it would be better for pg_dump to emit
DROP SCHEMA public RESTRICT, but that is drifting offtopic for $SUBJECT.)
Otherwise, when dumping from v13 or earlier, pg_dump will always emit
REVOKE and/or GRANT statements to reproduce the old ACL. When dumping from
v14 or later, pg_dump will use pg_init_privs to compute GRANT and REVOKE
statements, as it does today. (This may interfere with cross-version
pg_upgrade testing. I haven't looked at how best to fix that. Perhaps add
more fix_sql in test.sh.)

3. pg_upgrade from v13 to later versions will transfer template1's ACL for
schema public, even if that ACL was unchanged since v13 initdb. (This is
purely a consequence of the pg_dump behavior decision.) template0 will
keep the new default.

4. OWNER TO DATABASE_OWNER will likely be available for schemas only, though I
might propose it for all object classes if class-specific complexity proves
negligible.

5. ALTER DATABASE OWNER TO changes access control decisions involving
nspowner==DATABASE_OWNER. Speed of nspacl checks is more important than
reacting swiftly to ALTER DATABASE OWNER TO. Sessions running concurrently
will be eventually-consistent with respect to the ALTER DATABASE.
(Existing access control decisions, too, allow this sort of anomaly.)

6. pg_dump hasn't been reproducing ALTER SCHEMA public OWNER TO. That's a
mild defect today, but it wouldn't be mild anymore. We'll need pg_dump of
v13 databases to emit "ALTER SCHEMA public OWNER TO postgres" and for a v14
=> v15 upgrade to propagate that. This project can stand by itself; would
anyone else like to own it?

Thanks,
nm