Mercurial > gemma
view 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 |
line wrap: on
line source
BEGIN; -- -- Roles, privileges and policies for the GEMMA database -- -- We do not want any users to be able to create any objects REVOKE CREATE ON SCHEMA public FROM PUBLIC; -- -- Privileges for waterway_user -- GRANT USAGE ON SCHEMA users, waterway TO waterway_user; GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user; -- -- Extended privileges for waterway_admin -- GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA waterway TO waterway_admin; -- TODO: will there ever be UPDATEs or can we drop that due to historicisation? GRANT INSERT, UPDATE, DELETE ON users.templates, users.user_templates TO waterway_admin; -- -- Extended privileges for sys_admin -- GRANT INSERT, UPDATE, DELETE ON users.responsibility_areas TO sys_admin; GRANT USAGE ON SCHEMA sys_admin TO sys_admin; GRANT SELECT ON ALL TABLES IN SCHEMA sys_admin TO sys_admin; GRANT UPDATE ON sys_admin.system_config TO sys_admin; -- -- RLS policies for waterway_user -- -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing -- data modifications generally. -- Sometimes using 'username IN(SELECT username FROM user_profiles)' instead -- of 'username = current_user', because waterway_admin is intentionally -- allowed more with these policies (note that the subselect implies different -- policies on user_profiles depending on current_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 waterway.%I ' 'FOR SELECT TO waterway_user USING (staging_done)', the_table); EXECUTE format('ALTER TABLE waterway.%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 users.user_profiles FOR SELECT TO waterway_user USING (username = current_user); ALTER TABLE users.user_profiles ENABLE ROW LEVEL SECURITY; CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user USING (username IN(SELECT username FROM users.user_profiles)); ALTER TABLE users.user_templates ENABLE ROW LEVEL SECURITY; CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_user USING (template_name IN(SELECT template_name FROM users.user_templates)) WITH CHECK (true); ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY; -- -- RLS policies for waterway_admin -- -- Staging area -- TODO: add all relevant tables here CREATE POLICY responsibility_area ON waterway.bottlenecks FOR ALL TO waterway_admin USING (ST_Within(area, (SELECT area FROM users.responsibility_areas WHERE country = current_user_country()))); CREATE POLICY responsibility_area ON waterway.sounding_results FOR ALL TO waterway_admin USING (ST_Within(area, (SELECT area FROM users.responsibility_areas WHERE country = current_user_country()))); CREATE POLICY country_profiles ON users.user_profiles FOR SELECT TO waterway_admin USING (country = current_user_country()); COMMIT;