Re: public schema default ACL

On Mon, Nov 02, 2020 at 12:42:26PM -0500, Tom Lane wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Mon, Nov 2, 2020 at 5:51 AM Peter Eisentraut
> > <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> >> I'm not convinced, however, that this would would really move the needle
> >> in terms of the general security-uneasiness about the public schema and
> >> search paths. AFAICT, in any of your proposals, the default would still
> >> be to have the public schema world-writable and in the path.
>
> > Noah's proposed change to initdb appears to involve removing CREATE
> > permission by default, so I don't think this is true.
>
> I assume that means removing *public* CREATE permissions, not the
> owner's (which'd be the DB owner with the proposed changes).

My plan is for the default to become:

GRANT USAGE ON SCHEMA public TO PUBLIC;
ALTER SCHEMA public OWNER TO DATABASE_OWNER; -- new syntax

Hence, the dbowner can create objects in the schema or grant that ability to
others. Anyone can e.g. SELECT/UPDATE tables in the schema or call functions
in the schema, subject to per-table/per-function ACLs. ACK that this wasn't
explicit on the thread until a few days ago. I kept universal USAGE because
the schema wouldn't be very "public" without that.

> > It's hard to predict how many users that might inconvenience, but I
> > suppose it's probably a big number. On the other hand, the only
> > alternative is to continue shipping a configuration that, by default,
> > is potentially insecure. It's hard to decide which thing we should
> > care more about.
>
> Yeah. The thing is, if we make it harder to create stuff in "public",
> that's going to result in the path-of-least-resistance being to run
> everything as the DB owner. Which is better than running everything as
> superuser (at least if DB owner != postgres), but still not exactly great.
> Second least difficult thing is to re-grant public CREATE permissions,
> putting things right back where they were.

That is factual; whenever a strategy is easier to start than its alternatives,
folks will overconsume that strategy. One can mitigate that by introducing
artificial obstacles to use of the discouraged strategy, but that will tend to
harm the onboarding experience. Option (b)(2)(X) would have done that.

When folks end up creating all objects as the database owner, we still get the
win for roles that don't create permanent objects. It's decently common to
have an app run as a user that queries existing permanent objects, not issuing
permanent-object DDL. That works under both v13 and future defaults.

> What potentially could move the needle is separate search paths for
> relation lookup and function/operator lookup. We have sort of stuck
> our toe in that pond already by discriminating against pg_temp for
> function/operator lookup, but we could make that more formalized and
> controllable if there were distinct settings. I'm not sure offhand
> how much of a compatibility problem that produces.

Stephen raised a good point about this. Separately, regarding compatibility,
suppose a v13 database has:

CREATE FUNCTION f() RETURNS int LANGUAGE sql SECURITY DEFINER
AS 'SELECT inner_f()' SET search_path = a, b;

For compatibility, no value of the function-search-path setting should break
this function's ability to find a.inner_f(void). Which definition of
function-search-path achieves this?