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@leadboat.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@.cube_ll_coord($1::@cube_schema@.cube, 3)
> >     /
> >     earth() < -1 THEN -90::float8
> >   WHEN @cube_schema@.cube_ll_coord($1::@cube_schema@.cube, 3)
> >     /
> >     earth() >  1 THEN  90::float8
> >   ELSE degrees(asin(@cube_schema@.cube_ll_coord(
> >     $1::@cube_schema@.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@.earth()/ to avoid a @cube_schema@.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.