Mercurial > gemma
view auth.sql @ 97:9c2b796d506f
Fix Docker build.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 08 Jun 2018 12:43:57 +0200 |
parents | d036e1bd5f00 |
children | 81a2b26bf16b |
line wrap: on
line source
BEGIN; -- -- Roles, privileges and policies for the WAMOS database -- SET search_path TO public, wamos, wamos_waterway, wamos_fairway; -- We do not want any users to be able to create any objects REVOKE CREATE ON SCHEMA public FROM PUBLIC; -- -- Primary WAMOS roles (SRS table 3) -- CREATE ROLE waterway_user; CREATE ROLE waterway_admin IN ROLE waterway_user; CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin; -- -- Privileges for waterway_user -- GRANT USAGE ON SCHEMA wamos_waterway, wamos_fairway TO waterway_user; GRANT SELECT ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway TO waterway_user; GRANT SELECT ON templates, user_profiles TO waterway_user; -- -- Extended privileges for waterway_admin -- GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway TO waterway_admin; -- TODO: will there ever be UPDATEs or can we drop that due to historicisation? GRANT INSERT, UPDATE ON templates, user_templates TO waterway_admin; -- -- Extended privileges for sys_admin -- GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA wamos TO sys_admin; -- -- RLS policies for waterway_user -- -- Staging area CREATE FUNCTION create_hide_staging_policy() RETURNS void AS $$ DECLARE the_table varchar; BEGIN FOREACH the_table IN ARRAY ARRAY[ 'bottlenecks', 'sounding_results'] -- TODO: add all relevant tables here LOOP EXECUTE format('CREATE POLICY hide_staging ON %I ' 'FOR SELECT TO waterway_user USING (staging_done)', the_table); EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table); END LOOP; END; $$ LANGUAGE plpgsql; SELECT create_hide_staging_policy(); DROP FUNCTION create_hide_staging_policy; CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user USING (username = current_user); ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY; CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user USING (id IN(SELECT t.id FROM templates t JOIN user_templates ut ON t.id = ut.template_id JOIN user_profiles p ON ut.username = p.username WHERE p.username = current_user)); ALTER TABLE templates ENABLE ROW LEVEL SECURITY; -- -- RLS policies for waterway_admin -- -- Staging area -- TODO: add all relevant tables here CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin USING (ST_Within(area, (SELECT area FROM responsibility_areas WHERE country = (SELECT country FROM user_profiles WHERE username = current_user)))); CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin USING (ST_Within(area, (SELECT area FROM responsibility_areas WHERE country = (SELECT country FROM user_profiles WHERE username = current_user)))); CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin USING (id IN(SELECT t.id FROM templates t JOIN user_templates ut ON t.id = ut.template_id JOIN user_profiles p ON ut.username = p.username WHERE p.country = (SELECT country FROM user_profiles WHERE username = current_user))); COMMIT;