mirror of
https://github.com/hanzoai/postgres.git
synced 2026-10-30 18:33:07 +00:00
Tom Lane
fa3a99caa6
The @extschema:name@ feature added by8ee4d97fdallows us to make earthdistance's references to the cube extension fully search-path-secure, so long as all those references are resolved at extension installation time not runtime. To do that, we must convert earthdistance's SQL functions to the new SQL-standard style; but we wanted to do that anyway. The functions can be updated in our customary style by running CREATE OR REPLACE FUNCTION in an extension update script. However, there's still problems in the "CREATE DOMAIN earth" command: its references to cube functions could be captured by hostile objects in earthdistance's installation schema, if that's not where the cube extension is. Worse, the reference to the cube type itself as the domain's base could be captured, and that's not something we could fix after-the-fact in the update script. What I've done about that is to change the "CREATE DOMAIN earth" command in the base script earthdistance--1.1.sql. Ordinarily, changing a released extension script is forbidden; but I think it's okay here since the results of successful (non-trojaned) script execution will be identical to before. A good deal of care is still needed to make the extension's scripts proof against search-path-based attacks. We have to make sure that all the function and operator invocations have exact argument-type matches, to forestall attacks based on supplying a better match. Fortunately earthdistance isn't very big, so I've just gone through it and inspected each call to be sure of that. The only actual code changes needed were to spell all floating-point constants in the style '-1'::float8, rather than depending on runtime type conversions and/or negations. (I'm not sure that the shortcuts previously used were attackable, but removing run-time effort is a good thing anyway.) I believe that this fixes earthdistance enough that we could mark it trusted and remove the warnings about it that were added by 2a686cb86; but I've not done that here. The primary reason for dealing with this now is that we've received reports of pg_upgrade failing for databases that use earthdistance functions in contexts like generated columns. That's a consequence of69f0a08cahaving restricted the search_path used while evaluating such expressions. The only way to fix that is to make the earthdistance functions independent of run-time search_path. This patch is very much nicer than the alternative of attaching "SET search_path" clauses to earthdistance's functions: it is more secure and doesn't create a run-time penalty. Therefore, I've chosen to back-patch this to v16 where @extschema:name@ was added. It won't help unless users update to 16.7 and issue "ALTER EXTENSION earthdistance UPDATE" before upgrading to 17, but at least there's now a way to deal with the problem without manual intervention in the dump/restore process. Tom Lane and Ronan Dunklau Discussion: https://postgr.es/m/3316564.aeNJFYEL58@aivenlaptop Discussion: https://postgr.es/m/[email protected]
99 lines
3.2 KiB
SQL
99 lines
3.2 KiB
SQL
/* contrib/earthdistance/earthdistance--1.1.sql */
|
|
|
|
-- complain if script is sourced in psql, rather than via CREATE EXTENSION
|
|
\echo Use "CREATE EXTENSION earthdistance" to load this file. \quit
|
|
|
|
-- earth() returns the radius of the earth in meters. This is the only
|
|
-- place you need to change things for the cube base distance functions
|
|
-- in order to use different units (or a better value for the Earth's radius).
|
|
|
|
CREATE FUNCTION earth() RETURNS float8
|
|
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
|
|
AS 'SELECT ''6378168''::float8';
|
|
|
|
-- Astronomers may want to change the earth function so that distances will be
|
|
-- returned in degrees. To do this comment out the above definition and
|
|
-- uncomment the one below. Note that doing this will break the regression
|
|
-- tests.
|
|
--
|
|
-- CREATE FUNCTION earth() RETURNS float8
|
|
-- LANGUAGE SQL IMMUTABLE
|
|
-- AS 'SELECT 180/pi()';
|
|
|
|
-- Define domain for locations on the surface of the earth using a cube
|
|
-- datatype with constraints. cube provides 3D indexing.
|
|
-- The cube is restricted to be a point, no more than 3 dimensions
|
|
-- (for less than 3 dimensions 0 is assumed for the missing coordinates)
|
|
-- and that the point must be very near the surface of the sphere
|
|
-- centered about the origin with the radius of the earth.
|
|
|
|
CREATE DOMAIN earth AS @extschema:cube@.cube
|
|
CONSTRAINT not_point CHECK(@extschema:cube@.cube_is_point(VALUE))
|
|
CONSTRAINT not_3d CHECK(@extschema:cube@.cube_dim(VALUE) <= 3)
|
|
CONSTRAINT on_surface CHECK(abs(@extschema:cube@.cube_distance(VALUE, '(0)'::@extschema:cube@.cube) /
|
|
earth() - '1'::float8) < '10e-7'::float8);
|
|
|
|
CREATE FUNCTION sec_to_gc(float8)
|
|
RETURNS float8
|
|
LANGUAGE SQL
|
|
IMMUTABLE STRICT
|
|
PARALLEL SAFE
|
|
AS 'SELECT CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/(2*earth()) > 1 THEN pi()*earth() ELSE 2*earth()*asin($1/(2*earth())) END';
|
|
|
|
CREATE FUNCTION gc_to_sec(float8)
|
|
RETURNS float8
|
|
LANGUAGE SQL
|
|
IMMUTABLE STRICT
|
|
PARALLEL SAFE
|
|
AS 'SELECT CASE WHEN $1 < 0 THEN 0::float8 WHEN $1/earth() > pi() THEN 2*earth() ELSE 2*earth()*sin($1/(2*earth())) END';
|
|
|
|
CREATE FUNCTION ll_to_earth(float8, float8)
|
|
RETURNS earth
|
|
LANGUAGE SQL
|
|
IMMUTABLE STRICT
|
|
PARALLEL SAFE
|
|
AS 'SELECT cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth';
|
|
|
|
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';
|
|
|
|
CREATE FUNCTION longitude(earth)
|
|
RETURNS float8
|
|
LANGUAGE SQL
|
|
IMMUTABLE STRICT
|
|
PARALLEL SAFE
|
|
AS 'SELECT degrees(atan2(cube_ll_coord($1, 2), cube_ll_coord($1, 1)))';
|
|
|
|
CREATE FUNCTION earth_distance(earth, earth)
|
|
RETURNS float8
|
|
LANGUAGE SQL
|
|
IMMUTABLE STRICT
|
|
PARALLEL SAFE
|
|
AS 'SELECT sec_to_gc(cube_distance($1, $2))';
|
|
|
|
CREATE FUNCTION earth_box(earth, float8)
|
|
RETURNS cube
|
|
LANGUAGE SQL
|
|
IMMUTABLE STRICT
|
|
PARALLEL SAFE
|
|
AS 'SELECT cube_enlarge($1, gc_to_sec($2), 3)';
|
|
|
|
--------------- geo_distance
|
|
|
|
CREATE FUNCTION geo_distance (point, point)
|
|
RETURNS float8
|
|
LANGUAGE C IMMUTABLE STRICT PARALLEL SAFE AS 'MODULE_PATHNAME';
|
|
|
|
--------------- geo_distance as operator <@>
|
|
|
|
CREATE OPERATOR <@> (
|
|
LEFTARG = point,
|
|
RIGHTARG = point,
|
|
PROCEDURE = geo_distance,
|
|
COMMUTATOR = <@>
|
|
);
|