Mercurial > gemma
comparison schema/auth.sql @ 263:13ad969a9138
Enable listing of users for all roles with appropriate filters
Waterway users should see their own account data and their should be a
single interface for account data (i.e. users.list_users). Therefore, also
the RLS policy on user_profiles for waterway_admin is translated to the
view.
current_user_country() moved because it's needed earlier during database
setup, now.
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 27 Jul 2018 19:03:56 +0200 |
| parents | 946baea3d280 |
| children | 72062ca52746 |
comparison
equal
deleted
inserted
replaced
| 262:92470caf81fd | 263:13ad969a9138 |
|---|---|
| 77 | 77 |
| 78 -- | 78 -- |
| 79 -- RLS policies for waterway_admin | 79 -- RLS policies for waterway_admin |
| 80 -- | 80 -- |
| 81 | 81 |
| 82 -- Security-definer function to get current users country, which allows to | |
| 83 -- restrict the view on user_profiles by country without infinite recursion | |
| 84 CREATE FUNCTION current_user_country() | |
| 85 RETURNS users.user_profiles.country%TYPE | |
| 86 AS $$ | |
| 87 SELECT country FROM users.user_profiles WHERE username = session_user | |
| 88 $$ | |
| 89 LANGUAGE SQL | |
| 90 SECURITY DEFINER | |
| 91 STABLE PARALLEL SAFE; | |
| 92 | |
| 93 -- Staging area | 82 -- Staging area |
| 94 -- TODO: add all relevant tables here | 83 -- TODO: add all relevant tables here |
| 95 CREATE POLICY responsibility_area ON waterway.bottlenecks | 84 CREATE POLICY responsibility_area ON waterway.bottlenecks |
| 96 FOR ALL TO waterway_admin | 85 FOR ALL TO waterway_admin |
| 97 USING (ST_Within(area, (SELECT area FROM users.responsibility_areas | 86 USING (ST_Within(area, (SELECT area FROM users.responsibility_areas |