From: | Noah Misch <noah(at)leadboat(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Robert Haas <robertmhaas(at)gmail(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Facility for detecting insecure object naming |
Date: | 2018-08-11 19:47:05 |
Message-ID: | 20180811194705.GE2279274@rfd.leadboat.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Aug 08, 2018 at 09:58:38AM -0400, Tom Lane wrote:
> I'm not sold on #2 either. That path leads to, for example,
> s/=/OPERATOR(pg_catalog.=)/g everywhere, which is utterly catastrophic
> to both readability and portability of your SQL code. We *must* find
> a way to do better, not tell people that's what to do.
>
> 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:
-- (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$$;
From | Date | Subject | |
---|---|---|---|
Next Message | Nico Williams | 2018-08-11 20:32:23 | Re: Facility for detecting insecure object naming |
Previous Message | Tom Lane | 2018-08-11 19:15:25 | Re: [sqlsmith] ERROR: plan should not reference subplan's variable |