postgres.git / summary / log / commit / refs
commit 969bbd0fafc0f4d9ef504ca98a127c8d945f71a0
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat Dec 14 21:07:18 2024 +0000
First released in: 18.0, 16.7, 17.3
contrib/earthdistance: Use SQL-standard function bodies.
The @extschema:name@ feature added by 72a5b1fc8 allows 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 7eeb1d986; 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 of 2af07e2f7 having 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/6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org
contrib/earthdistance/Makefile | 3 +-
contrib/earthdistance/earthdistance--1.1--1.2.sql | 73 +++++++++++++++++++++++
contrib/earthdistance/earthdistance--1.1.sql | 8 +--
contrib/earthdistance/earthdistance.control | 2 +-
contrib/earthdistance/meson.build | 1 +
5 files changed, 81 insertions(+), 6 deletions(-)
diff --git a/contrib/earthdistance/Makefile b/contrib/earthdistance/Makefile
index f93b7a925a2..0cf3fa379a2 100644
--- a/contrib/earthdistance/Makefile
+++ b/contrib/earthdistance/Makefile
@@ -3,7 +3,8 @@
MODULES = earthdistance
EXTENSION = earthdistance
-DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql
+DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql \
+ earthdistance--1.1--1.2.sql
PGFILEDESC = "earthdistance - calculate distances on the surface of the Earth"
REGRESS = earthdistance
diff --git a/contrib/earthdistance/earthdistance--1.1--1.2.sql b/contrib/earthdistance/earthdistance--1.1--1.2.sql
new file mode 100644
index 00000000000..40a0ce233d1
--- /dev/null
+++ b/contrib/earthdistance/earthdistance--1.1--1.2.sql
@@ -0,0 +1,73 @@
+/* contrib/earthdistance/earthdistance--1.1--1.2.sql */
+
+-- complain if script is sourced in psql, rather than via ALTER EXTENSION
+\echo Use "ALTER EXTENSION earthdistance UPDATE TO '1.2'" to load this file. \quit
+
+CREATE OR REPLACE FUNCTION earth() RETURNS float8
+LANGUAGE SQL IMMUTABLE PARALLEL SAFE
+RETURN '6378168'::float8;
+
+CREATE OR REPLACE FUNCTION sec_to_gc(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE
+ WHEN $1 < '0'::float8 THEN '0'::float8
+ WHEN $1 / ('2'::float8 * earth()) > '1'::float8 THEN pi() * earth()
+ ELSE '2'::float8 * earth() * asin($1 / ('2'::float8 * earth()))
+END;
+
+CREATE OR REPLACE FUNCTION gc_to_sec(float8)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE
+ WHEN $1 < '0'::float8 THEN '0'::float8
+ WHEN $1 / earth() > pi() THEN '2'::float8 * earth()
+ ELSE '2'::float8 * earth() * sin($1 / ('2'::float8 * earth()))
+END;
+
+CREATE OR REPLACE FUNCTION ll_to_earth(float8, float8)
+RETURNS earth
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(
+ earth() * cos(radians($1)) * cos(radians($2))),
+ earth() * cos(radians($1)) * sin(radians($2))),
+ earth() * sin(radians($1)))::earth;
+
+CREATE OR REPLACE FUNCTION latitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN CASE
+ WHEN @extschema:cube@.cube_ll_coord($1, 3) / earth() < '-1'::float8 THEN '-90'::float8
+ WHEN @extschema:cube@.cube_ll_coord($1, 3) / earth() > '1'::float8 THEN '90'::float8
+ ELSE degrees(asin(@extschema:cube@.cube_ll_coord($1, 3) / earth()))
+END;
+
+CREATE OR REPLACE FUNCTION longitude(earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN degrees(atan2(@extschema:cube@.cube_ll_coord($1, 2),
+ @extschema:cube@.cube_ll_coord($1, 1)));
+
+CREATE OR REPLACE FUNCTION earth_distance(earth, earth)
+RETURNS float8
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
+
+CREATE OR REPLACE FUNCTION earth_box(earth, float8)
+RETURNS @extschema:cube@.cube
+LANGUAGE SQL
+IMMUTABLE STRICT
+PARALLEL SAFE
+RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2), 3);
diff --git a/contrib/earthdistance/earthdistance--1.1.sql b/contrib/earthdistance/earthdistance--1.1.sql
index 9ef20ab848c..4799f03e3ea 100644
--- a/contrib/earthdistance/earthdistance--1.1.sql
+++ b/contrib/earthdistance/earthdistance--1.1.sql
@@ -27,10 +27,10 @@ AS 'SELECT ''6378168''::float8';
-- 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 cube
- CONSTRAINT not_point check(cube_is_point(value))
- CONSTRAINT not_3d check(cube_dim(value) <= 3)
- CONSTRAINT on_surface check(abs(cube_distance(value, '(0)'::cube) /
+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)
diff --git a/contrib/earthdistance/earthdistance.control b/contrib/earthdistance/earthdistance.control
index 5816d22cdd9..de2465d487e 100644
--- a/contrib/earthdistance/earthdistance.control
+++ b/contrib/earthdistance/earthdistance.control
@@ -1,6 +1,6 @@
# earthdistance extension
comment = 'calculate great-circle distances on the surface of the Earth'
-default_version = '1.1'
+default_version = '1.2'
module_pathname = '$libdir/earthdistance'
relocatable = true
requires = 'cube'
diff --git a/contrib/earthdistance/meson.build b/contrib/earthdistance/meson.build
index 4e3c538f7aa..be63bf577c3 100644
--- a/contrib/earthdistance/meson.build
+++ b/contrib/earthdistance/meson.build
@@ -20,6 +20,7 @@ install_data(
'earthdistance.control',
'earthdistance--1.0--1.1.sql',
'earthdistance--1.1.sql',
+ 'earthdistance--1.1--1.2.sql',
kwargs: contrib_data_args,
)
[parent: 62b7a9a7784e]