Mercurial > gemma
view schema/auth.sql @ 234:55dce2f649bc
Allow using the same email-address for multiple users
This can be useful at least in testing scenarios, where one
person wants to have multiple accounts with different roles
and there is no other reason to enforce uniqueness.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 26 Jul 2018 15:10:22 +0200 |
parents | a0e2c6bb3cb3 |
children | 946baea3d280 |
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 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 -- -- Security-definer function to get current users country, which allows to -- restrict the view on user_profiles by country without infinite recursion CREATE FUNCTION current_user_country() RETURNS users.user_profiles.country%TYPE AS $$ SELECT country FROM users.user_profiles WHERE username = session_user $$ LANGUAGE SQL SECURITY DEFINER STABLE PARALLEL SAFE; -- 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;