Mercurial > gemma

comparison schema/auth.sql @ 1229:d395b2940a82

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improved performance of row level security check on users responsibility areas by projecting the geographies into a good fitting UTM zone first before doing the coverage test.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 20 Nov 2018 00:51:33 +0100
parents 930fdd8b474f
children 6590208e3ee1
comparison
equal deleted inserted replaced
1228:17131f0f9fcb 1229:d395b2940a82
87 87
88 -- 88 --
89 -- RLS policies for waterway_admin 89 -- RLS policies for waterway_admin
90 -- 90 --
91 91
92 CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS
93 $$
94 DECLARE
95 center geometry;
96 BEGIN
97 SELECT ST_Centroid(g) INTO center;
98 RETURN
99 CASE WHEN ST_Y(center) > 0 THEN
100 32600
101 ELSE
102 32700
103 END + floor((ST_X(center)+180)/6)::int + 1;
104 END;
105 $$
106 LANGUAGE plpgsql
107 IMMUTABLE;
108
109 CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS
110 $$
111 DECLARE
112 user_area geometry;
113 utm integer;
114 BEGIN
115 SELECT area::geometry FROM users.responsibility_areas INTO user_area
116 WHERE country = users.current_user_country();
117 SELECT best_utm(user_area) INTO utm;
118 RETURN ST_Covers(
119 ST_Transform(user_area, utm),
120 ST_Transform(g::geometry, utm));
121 END;
122 $$
123 LANGUAGE plpgsql
124 STABLE;
125
92 -- Staging area 126 -- Staging area
93 -- TODO: add all relevant tables here 127 -- TODO: add all relevant tables here
128
94 CREATE POLICY responsibility_area ON waterway.bottlenecks 129 CREATE POLICY responsibility_area ON waterway.bottlenecks
95 FOR ALL TO waterway_admin 130 FOR ALL TO waterway_admin
96 USING (ST_CoveredBy(area, (SELECT area FROM users.responsibility_areas 131 USING (utm_covers(area));
97 WHERE country = users.current_user_country()))); 132
98 CREATE POLICY responsibility_area ON waterway.sounding_results 133 CREATE POLICY responsibility_area ON waterway.sounding_results
99 FOR ALL TO waterway_admin 134 FOR ALL TO waterway_admin
100 USING (ST_CoveredBy(area, (SELECT area FROM users.responsibility_areas 135 USING (utm_covers(area));
101 WHERE country = users.current_user_country())));
102 136
103 COMMIT; 137 COMMIT;