Re: Facility for detecting insecure object naming

On Sat, Aug 11, 2018 at 12:47:05PM -0700, Noah Misch wrote:
> On Wed, Aug 08, 2018 at 09:58:38AM -0400, Tom Lane wrote:
> > When the security team was discussing this issue before, we speculated
> > about ideas like inventing a function trust mechanism, so that attacks
> > based on search path manipulations would fail even if they managed to
> > capture an operator reference. I'd rather go down that path than
> > encourage people to do more schema qualification.
>
> Interesting. If we got a function trust mechanism, how much qualification
> would you then like? Here are the levels I know about, along with their
> implications:

Any preferences among these options and the fifth option I gave in
https://postgr.es/m/20180815024429.GA3535710@rfd.leadboat.com? I don't want
to leave earthdistance broken. So far, though, it seems no two people accept
any one fix.

> -- (1) Use qualified references and exact match for all objects.
> --
> -- Always secure, even if schema usage does not conform to ddl-schemas-patterns
> -- and function trust is disabled.
> --
> -- Subject to denial of service from anyone able to CREATE in cube schema or
> -- earthdistance schema.
> CREATE FUNCTION latitude(earth)
> RETURNS float8
> LANGUAGE SQL
> IMMUTABLE STRICT
> PARALLEL SAFE
> AS $$SELECT CASE
> WHEN @cube_schema(at)(dot)cube_ll_coord($1::@cube_schema(at)(dot)cube, 3)
> OPERATOR(pg_catalog./)
> @extschema(at)(dot)earth() OPERATOR(pg_catalog.<) -1 THEN -90::pg_catalog.float8
> WHEN @cube_schema(at)(dot)cube_ll_coord($1::@cube_schema(at)(dot)cube, 3)
> OPERATOR(pg_catalog./)
> @extschema(at)(dot)earth() OPERATOR(pg_catalog.>) 1 THEN 90::pg_catalog.float8
> ELSE pg_catalog.degrees(pg_catalog.asin(@cube_schema(at)(dot)cube_ll_coord(
> $1::@cube_schema(at)(dot)cube, 3) OPERATOR(pg_catalog./) @extschema(at)(dot)earth()))
> END$$;
>
>
> -- (2) Use qualified references for objects outside pg_catalog.
> --
> -- With function trust disabled, this would be subject to privilege escalation
> -- from anyone able to CREATE in cube schema.
> --
> -- Subject to denial of service from anyone able to CREATE in cube schema or
> -- earthdistance schema.
> CREATE FUNCTION latitude(earth)
> RETURNS float8
> LANGUAGE SQL
> IMMUTABLE STRICT
> PARALLEL SAFE
> AS $$SELECT CASE
> WHEN @cube_schema(at)(dot)cube_ll_coord($1, 3)
> /
> @extschema(at)(dot)earth() < -1 THEN -90::float8
> WHEN @cube_schema(at)(dot)cube_ll_coord($1, 3)
> /
> @extschema(at)(dot)earth() > 1 THEN 90::float8
> ELSE degrees(asin(@cube_schema(at)(dot)cube_ll_coord($1, 3) / @extschema(at)(dot)earth()))
> END$$;
>
>
> -- (3) "SET search_path" with today's code.
> --
> -- Security and reliability considerations are the same as (2). Today, this
> -- reduces performance by suppressing optimizations like inlining.
> CREATE FUNCTION latitude(earth)
> RETURNS float8
> LANGUAGE SQL
> IMMUTABLE STRICT
> PARALLEL SAFE
> SET search_path FROM CURRENT
> AS $$SELECT CASE
> WHEN cube_ll_coord($1, 3)
> /
> earth() < -1 THEN -90::float8
> WHEN cube_ll_coord($1, 3)
> /
> earth() > 1 THEN 90::float8
> ELSE degrees(asin(cube_ll_coord($1, 3) / earth()))
> END$$;
>
>
> -- (4) Today's code (reformatted).
> --
> -- Always secure if schema usage conforms to ddl-schemas-patterns, even if
> -- function trust is disabled. If cube schema or earthdistance schema is not in
> -- search_path, function doesn't work.
> CREATE FUNCTION latitude(earth)
> RETURNS float8
> LANGUAGE SQL
> IMMUTABLE STRICT
> PARALLEL SAFE
> AS $$SELECT CASE
> WHEN cube_ll_coord($1, 3)
> /
> earth() < -1 THEN -90::float8
> WHEN cube_ll_coord($1, 3)
> /
> earth() > 1 THEN 90::float8
> ELSE degrees(asin(cube_ll_coord($1, 3) / earth()))
> END$$;