Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit3652de3

Browse files
committed
contrib/earthdistance: Use SQL-standard function bodies.
The @extschema:name@ feature added by72a5b1f allows us tomake earthdistance's references to the cube extension fullysearch-path-secure, so long as all those references areresolved at extension installation time not runtime.To do that, we must convert earthdistance's SQL functions tothe new SQL-standard style; but we wanted to do that anyway.The functions can be updated in our customary style by runningCREATE 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 capturedby hostile objects in earthdistance's installation schema,if that's not where the cube extension is. Worse, the referenceto the cube type itself as the domain's base could be captured,and that's not something we could fix after-the-fact in theupdate 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 thinkit'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 scriptsproof against search-path-based attacks. We have to make sure thatall the function and operator invocations have exact argument-typematches, to forestall attacks based on supplying a better match.Fortunately earthdistance isn't very big, so I've just gone throughit and inspected each call to be sure of that. The only actual codechanges needed were to spell all floating-point constants in the style'-1'::float8, rather than depending on runtime type conversions and/ornegations. (I'm not sure that the shortcuts previously used wereattackable, but removing run-time effort is a good thing anyway.)I believe that this fixes earthdistance enough that we couldmark it trusted and remove the warnings about it that wereadded by7eeb1d9; but I've not done that here.The primary reason for dealing with this now is that we'vereceived reports of pg_upgrade failing for databases that useearthdistance functions in contexts like generated columns.That's a consequence of2af07e2 having restricted the search_pathused while evaluating such expressions. The only way to fix thatis to make the earthdistance functions independent of run-timesearch_path. This patch is very much nicer than the alternative ofattaching "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 withoutmanual intervention in the dump/restore process.Tom Lane and Ronan DunklauDiscussion:https://postgr.es/m/3316564.aeNJFYEL58@aivenlaptopDiscussion:https://postgr.es/m/6a6439f1-8039-44e2-8fb9-59028f7f2014@mailbox.org
1 parentad950ea commit3652de3

File tree

5 files changed

+81
-6
lines changed

5 files changed

+81
-6
lines changed

‎contrib/earthdistance/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,8 @@
33
MODULES = earthdistance
44

55
EXTENSION = earthdistance
6-
DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql
6+
DATA = earthdistance--1.1.sql earthdistance--1.0--1.1.sql\
7+
earthdistance--1.1--1.2.sql
78
PGFILEDESC = "earthdistance - calculate distances on the surface of the Earth"
89

910
REGRESS = earthdistance
Lines changed: 73 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,73 @@
1+
/* contrib/earthdistance/earthdistance--1.1--1.2.sql*/
2+
3+
-- complain if script is sourced in psql, rather than via ALTER EXTENSION
4+
\echo Use"ALTER EXTENSION earthdistance UPDATE TO '1.2'" to load this file. \quit
5+
6+
CREATE OR REPLACEFUNCTIONearth() RETURNS float8
7+
LANGUAGE SQL IMMUTABLE PARALLEL SAFE
8+
RETURN'6378168'::float8;
9+
10+
CREATE OR REPLACEFUNCTIONsec_to_gc(float8)
11+
RETURNS float8
12+
LANGUAGE SQL
13+
IMMUTABLE STRICT
14+
PARALLEL SAFE
15+
RETURN CASE
16+
WHEN $1<'0'::float8 THEN'0'::float8
17+
WHEN $1/ ('2'::float8* earth())>'1'::float8 THEN pi()* earth()
18+
ELSE'2'::float8* earth()* asin($1/ ('2'::float8* earth()))
19+
END;
20+
21+
CREATE OR REPLACEFUNCTIONgc_to_sec(float8)
22+
RETURNS float8
23+
LANGUAGE SQL
24+
IMMUTABLE STRICT
25+
PARALLEL SAFE
26+
RETURN CASE
27+
WHEN $1<'0'::float8 THEN'0'::float8
28+
WHEN $1/ earth()> pi() THEN'2'::float8* earth()
29+
ELSE'2'::float8* earth()* sin($1/ ('2'::float8* earth()))
30+
END;
31+
32+
CREATE OR REPLACEFUNCTIONll_to_earth(float8, float8)
33+
RETURNS earth
34+
LANGUAGE SQL
35+
IMMUTABLE STRICT
36+
PARALLEL SAFE
37+
RETURN @extschema:cube@.cube(@extschema:cube@.cube(@extschema:cube@.cube(
38+
earth()* cos(radians($1))* cos(radians($2))),
39+
earth()* cos(radians($1))* sin(radians($2))),
40+
earth()* sin(radians($1)))::earth;
41+
42+
CREATE OR REPLACEFUNCTIONlatitude(earth)
43+
RETURNS float8
44+
LANGUAGE SQL
45+
IMMUTABLE STRICT
46+
PARALLEL SAFE
47+
RETURN CASE
48+
WHEN @extschema:cube@.cube_ll_coord($1,3)/ earth()<'-1'::float8 THEN'-90'::float8
49+
WHEN @extschema:cube@.cube_ll_coord($1,3)/ earth()>'1'::float8 THEN'90'::float8
50+
ELSE degrees(asin(@extschema:cube@.cube_ll_coord($1,3)/ earth()))
51+
END;
52+
53+
CREATE OR REPLACEFUNCTIONlongitude(earth)
54+
RETURNS float8
55+
LANGUAGE SQL
56+
IMMUTABLE STRICT
57+
PARALLEL SAFE
58+
RETURN degrees(atan2(@extschema:cube@.cube_ll_coord($1,2),
59+
@extschema:cube@.cube_ll_coord($1,1)));
60+
61+
CREATE OR REPLACEFUNCTIONearth_distance(earth, earth)
62+
RETURNS float8
63+
LANGUAGE SQL
64+
IMMUTABLE STRICT
65+
PARALLEL SAFE
66+
RETURN sec_to_gc(@extschema:cube@.cube_distance($1, $2));
67+
68+
CREATE OR REPLACEFUNCTIONearth_box(earth, float8)
69+
RETURNS @extschema:cube@.cube
70+
LANGUAGE SQL
71+
IMMUTABLE STRICT
72+
PARALLEL SAFE
73+
RETURN @extschema:cube@.cube_enlarge($1, gc_to_sec($2),3);

‎contrib/earthdistance/earthdistance--1.1.sql

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -27,10 +27,10 @@ AS 'SELECT ''6378168''::float8';
2727
-- and that the point must be very near the surface of the sphere
2828
-- centered about the origin with the radius of the earth.
2929

30-
CREATEDOMAINearthAS cube
31-
CONSTRAINT not_pointcheck(cube_is_point(value))
32-
CONSTRAINT not_3dcheck(cube_dim(value)<=3)
33-
CONSTRAINT on_surfacecheck(abs(cube_distance(value,'(0)'::cube)/
30+
CREATEDOMAINearthAS@extschema:cube@.cube
31+
CONSTRAINT not_pointCHECK(@extschema:cube@.cube_is_point(VALUE))
32+
CONSTRAINT not_3dCHECK(@extschema:cube@.cube_dim(VALUE)<=3)
33+
CONSTRAINT on_surfaceCHECK(abs(@extschema:cube@.cube_distance(VALUE,'(0)'::@extschema:cube@.cube)/
3434
earth()-'1'::float8)<'10e-7'::float8);
3535

3636
CREATEFUNCTIONsec_to_gc(float8)
Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
# earthdistance extension
22
comment = 'calculate great-circle distances on the surface of the Earth'
3-
default_version = '1.1'
3+
default_version = '1.2'
44
module_pathname = '$libdir/earthdistance'
55
relocatable = true
66
requires = 'cube'

‎contrib/earthdistance/meson.build

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@ install_data(
2020
'earthdistance.control',
2121
'earthdistance--1.0--1.1.sql',
2222
'earthdistance--1.1.sql',
23+
'earthdistance--1.1--1.2.sql',
2324
kwargs: contrib_data_args,
2425
)
2526

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp