Re: Facility for detecting insecure object naming

On Tue, Aug 14, 2018 at 04:42:43PM -0400, Bruce Momjian wrote:
> On Tue, Aug 14, 2018 at 04:23:33PM -0400, Robert Haas wrote:
> > On Tue, Aug 14, 2018 at 3:31 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > I am unclear how lexical scoping helps with this, or with Postgres.
> >
> > Well, if you say sqrt(x) in C, and you don't have sqrt or x defined in
> > the current function, you know that you're going to call a global
> > function called sqrt and pass to it a global variable called x.
> > You're not going to latch onto a local variable called x in the
> > function that called your function, and if the calling function has a
> > local variable called sqrt, that doesn't matter either. The linker
> > can point every called to sqrt() in the program at a different place
> > than expected, but you can't monkey with the behavior of an individual
> > call by having the calling function declare identifiers that mask the
> > ones the function intended to reference.
>
> What we are doing is more like C++ virtual functions, where the class
> calling it can replace function calls in subfunctions:
>
> https://www.geeksforgeeks.org/virtual-function-cpp/
>
> > On the other hand, when you call an SQL-language function, or even to
> > some extent a plpgsql-language function, from PostgreSQL, you can in
> > fact change the meanings of every identifier that appears in that
> > function body unless those references are all schema-qualified or the
> > function sets the search path. If an SQL-language function calls
>
> I think we decide that search path alone for functions is insufficient
> because of data type matching, unless the schema is secure.

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$$;