Re: Facility for detecting insecure object naming

On Wed, Aug 15, 2018 at 12:04:53PM -0400, Robert Haas wrote:
> On Tue, Aug 14, 2018 at 10:44 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> > Right. For what it's worth, the example I permuted upthread might look like
> > this in a lexical search path world:
> >
> > -- Always secure, even if schema usage does not conform to ddl-schemas-patterns
> > -- and function trust is disabled or unavailable.
> > --
> > -- At CREATE EXTENSION time only, subject to denial of service from anyone able
> > -- to CREATE in cube schema or earthdistance schema.
> > --
> > -- Objects in @cube_schema@ are qualified so objects existing in @extschema@ at
> > -- CREATE EXTENSION time cannot mask them.
> > 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)
> > /
> > earth() < -1 THEN -90::float8
> > WHEN @cube_schema(at)(dot)cube_ll_coord($1::@cube_schema(at)(dot)cube, 3)
> > /
> > earth() > 1 THEN 90::float8
> > ELSE degrees(asin(@cube_schema(at)(dot)cube_ll_coord(
> > $1::@cube_schema(at)(dot)cube, 3) / earth()))
> > END$$;
>
> Could we get rid of the remaining qualification by setting the lexical
> search path to @cube_schema@, @extschema@, public?

That would make the @cube_schema@ qualifications superfluous, but you would do
s/earth()/@extschema(at)(dot)earth()/ to avoid a @cube_schema(at)(dot)earth() winning. In
general, when a query needs to find objects in N maybe-untrusted schemas,
you'll schema-qualify objects of at least N-1 of the schemas.