Mercurial > gemma
comparison schema/auth.sql @ 5013:7dff1015283d
Add row level security policies for waterway axis
Enforcing the area of responsibility this way instead of leaving it
up to the importer implementation will also reduce complexity of
statements needed to implement keeping of historic axis data.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Thu, 12 Mar 2020 14:49:19 +0100 |
| parents | d410e7ab03fe |
| children | 388947a3050d |
comparison
equal
deleted
inserted
replaced
| 5012:ae3a1392f9d0 | 5013:7dff1015283d |
|---|---|
| 104 the_table); | 104 the_table); |
| 105 END LOOP; | 105 END LOOP; |
| 106 END; | 106 END; |
| 107 $$; | 107 $$; |
| 108 | 108 |
| 109 -- Tables without staging area | |
| 110 CREATE POLICY hide_nothing ON waterway.waterway_axis | |
| 111 FOR SELECT TO waterway_user USING (true); | |
| 112 CREATE POLICY sys_admin ON waterway.waterway_axis | |
| 113 FOR ALL TO sys_admin USING (true); | |
| 114 ALTER TABLE waterway.waterway_axis ENABLE ROW LEVEL SECURITY; | |
| 115 | |
| 116 | |
| 109 -- | 117 -- |
| 110 -- RLS policies for templates | 118 -- RLS policies for templates |
| 111 -- | 119 -- |
| 112 CREATE POLICY select_templates ON users.templates FOR SELECT TO waterway_user | 120 CREATE POLICY select_templates ON users.templates FOR SELECT TO waterway_user |
| 113 USING (country IS NULL OR country = (SELECT country FROM users.list_users WHERE username = current_user)); | 121 USING (country IS NULL OR country = (SELECT country FROM users.list_users WHERE username = current_user)); |
| 174 SELECT country FROM users.list_users WHERE username = current_user)); | 182 SELECT country FROM users.list_users WHERE username = current_user)); |
| 175 | 183 |
| 176 CREATE POLICY sys_admin ON users.stretches | 184 CREATE POLICY sys_admin ON users.stretches |
| 177 FOR ALL TO sys_admin | 185 FOR ALL TO sys_admin |
| 178 USING (true); | 186 USING (true); |
| 187 | |
| 188 -- | |
| 189 -- Tables without staging area | |
| 190 -- | |
| 191 -- Use three policies instead of one FOR ALL to avoid costly expressions | |
| 192 -- being added in SELECT queries. | |
| 193 CREATE POLICY responsibility_area_insert ON waterway.waterway_axis | |
| 194 FOR INSERT TO waterway_admin | |
| 195 WITH CHECK (users.utm_covers(wtwaxs)); | |
| 196 CREATE POLICY responsibility_area_update ON waterway.waterway_axis | |
| 197 FOR UPDATE TO waterway_admin | |
| 198 USING (users.utm_covers(wtwaxs)); | |
| 199 CREATE POLICY responsibility_area_delete ON waterway.waterway_axis | |
| 200 FOR DELETE TO waterway_admin | |
| 201 USING (users.utm_covers(wtwaxs)); | |
| 179 | 202 |
| 180 -- | 203 -- |
| 181 -- RLS policies for imports and import config | 204 -- RLS policies for imports and import config |
| 182 -- | 205 -- |
| 183 | 206 |