Re: Facility for detecting insecure object naming
От | Noah Misch |
---|---|
Тема | Re: Facility for detecting insecure object naming |
Дата | |
Msg-id | 20180811194705.GE2279274@rfd.leadboat.com обсуждение исходный текст |
Ответ на | Re: Facility for detecting insecure object naming (Tom Lane <tgl@sss.pgh.pa.us>) |
Ответы |
Re: Facility for detecting insecure object naming
(Nico Williams <nico@cryptonector.com>)
Re: Facility for detecting insecure object naming (Noah Misch <noah@leadboat.com>) |
Список | 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@.cube_ll_coord($1::@cube_schema@.cube, 3) OPERATOR(pg_catalog./) @extschema@.earth() OPERATOR(pg_catalog.<) -1 THEN -90::pg_catalog.float8 WHEN @cube_schema@.cube_ll_coord($1::@cube_schema@.cube, 3) OPERATOR(pg_catalog./) @extschema@.earth() OPERATOR(pg_catalog.>) 1 THEN 90::pg_catalog.float8 ELSE pg_catalog.degrees(pg_catalog.asin(@cube_schema@.cube_ll_coord( $1::@cube_schema@.cube, 3) OPERATOR(pg_catalog./) @extschema@.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@.cube_ll_coord($1, 3) / @extschema@.earth() < -1 THEN -90::float8 WHEN @cube_schema@.cube_ll_coord($1, 3) / @extschema@.earth() > 1 THEN 90::float8 ELSE degrees(asin(@cube_schema@.cube_ll_coord($1, 3) / @extschema@.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$$;
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Tom LaneДата:
Сообщение: Re: [sqlsmith] ERROR: plan should not reference subplan's variable