Mercurial > gemma
view schema/auth.sql @ 5654:358130887c72
Added tag v5.7.1 for changeset 9b3779f649c2
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Tue, 08 Aug 2023 15:07:00 +0200 |
parents | 5e3e3d9e2c23 |
children |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottried <tom@intevation.de> -- * Sascha Wilde <sascha.wilde@intevation.de> -- * Sascha L. Teichmann <sascha.teichmann@intevation.de> BEGIN; -- -- Roles, privileges and policies for the GEMMA database -- -- We do not want any users to be able to create any objects REVOKE ALL ON SCHEMA public FROM PUBLIC; -- -- Privileges for waterway_user -- GRANT USAGE ON SCHEMA public, users, waterway, sys_admin, caching TO waterway_user; GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user; GRANT SELECT, UPDATE, DELETE, INSERT ON ALL TABLES IN SCHEMA caching TO waterway_user; GRANT SELECT ON sys_admin.system_config TO waterway_user; GRANT UPDATE (pw, map_extent, email_address) ON users.list_users TO waterway_user; -- -- Extended privileges for waterway_admin -- GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway TO waterway_admin; GRANT INSERT, UPDATE, DELETE ON users.templates TO waterway_admin; GRANT USAGE ON SCHEMA import TO waterway_admin; GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA import TO waterway_admin; GRANT UPDATE ON import.imports, import.import_configuration, import.import_configuration_attributes TO waterway_admin; GRANT DELETE ON import.track_imports, import.import_configuration, import.import_configuration_attributes TO waterway_admin; -- -- Extended privileges for sys_admin -- GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA users 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 INSERT, UPDATE ON sys_admin.system_config TO sys_admin; GRANT UPDATE ON sys_admin.published_services TO sys_admin; GRANT INSERT, DELETE, UPDATE ON sys_admin.password_reset_requests TO sys_admin; GRANT DELETE ON import.imports, import.import_logs TO sys_admin; GRANT SELECT, INSERT, DELETE, UPDATE ON sys_admin.stats_updates TO sys_admin; -- -- Privileges assigned directly to metamorph -- -- Needed for GeoServer's system inspection run before session startup SQL GRANT USAGE ON SCHEMA public TO metamorph; -- -- RLS policies for waterway_user -- -- Sometimes using FOR ALL because we rely on GRANTed privileges for allowing -- data modifications generally. -- Sometimes using 'EXISTS(SELECT 1 FROM list_users WHERE username = ...)' -- instead of 'username = current_user', since waterway_admin is intentionally -- allowed more with these policies (note that the subselect implies different -- filtering on list_users depending on current_user). -- -- Staging area CREATE POLICY hide_staging ON users.stretches FOR SELECT TO waterway_user USING (staging_done); ALTER TABLE users.stretches ENABLE ROW LEVEL SECURITY; DO LANGUAGE plpgsql $$ DECLARE the_table varchar; BEGIN FOREACH the_table IN ARRAY ARRAY[ 'gauge_measurements', 'waterway_profiles', 'fairway_dimensions', 'sections', 'bottlenecks', 'sounding_results'] LOOP EXECUTE format('CREATE POLICY hide_staging ON waterway.%I ' 'FOR SELECT TO waterway_user USING (staging_done)', the_table); EXECUTE format('CREATE POLICY sys_admin ON waterway.%I ' 'FOR ALL TO sys_admin USING (true)', the_table); EXECUTE format('ALTER TABLE waterway.%I ENABLE ROW LEVEL SECURITY', the_table); END LOOP; END; $$; -- Tables without staging area CREATE POLICY hide_nothing ON waterway.waterway_axis FOR SELECT TO waterway_user USING (true); CREATE POLICY sys_admin ON waterway.waterway_axis FOR ALL TO sys_admin USING (true); ALTER TABLE waterway.waterway_axis ENABLE ROW LEVEL SECURITY; -- -- RLS policies for templates -- CREATE POLICY select_templates ON users.templates FOR SELECT TO waterway_user USING (country IS NULL OR country = (SELECT country FROM users.list_users WHERE username = current_user)); CREATE POLICY user_templates ON users.templates FOR ALL TO waterway_admin USING (country = (SELECT country FROM users.list_users WHERE username = current_user)); CREATE POLICY admin_templates ON users.templates FOR ALL TO sys_admin USING (true); ALTER TABLE users.templates ENABLE ROW LEVEL SECURITY; -- -- RLS policies for waterway_admin -- -- Staging area DO LANGUAGE plpgsql $do$ DECLARE the_table varchar; condition CONSTANT text = $$ (location).country_code = (SELECT country FROM users.list_users WHERE username = current_user) $$; BEGIN FOREACH the_table IN ARRAY ARRAY[ 'gauge_measurements', 'waterway_profiles'] LOOP EXECUTE format($$ CREATE POLICY same_country_insert ON waterway.%I FOR INSERT TO waterway_admin WITH CHECK (%s) $$, the_table, condition); -- In many cases it is more efficient to check for "staging_done" to -- prevent the more expensive checks for read only access (which is -- allowed for all users, when staging is done). EXECUTE format($$ CREATE POLICY same_country_select ON waterway.%I FOR SELECT TO waterway_admin USING (staging_done OR %s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY same_country_update ON waterway.%I FOR UPDATE TO waterway_admin USING (%s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY same_country_delete ON waterway.%I FOR DELETE TO waterway_admin USING (%s) $$, the_table, condition); END LOOP; END; $do$; DO LANGUAGE plpgsql $do$ DECLARE the_table varchar; condition CONSTANT text = $$ (SELECT ST_Covers(a, ST_Transform(CAST(area AS geometry), ST_SRID(a))) FROM users.current_user_area_utm() AS a (a)) $$; BEGIN FOREACH the_table IN ARRAY ARRAY[ 'fairway_dimensions', 'bottlenecks', 'sounding_results'] LOOP EXECUTE format($$ CREATE POLICY responsibility_area_insert ON waterway.%I FOR INSERT TO waterway_admin WITH CHECK (%s) $$, the_table, condition); -- In many cases it is more efficient to check for "staging_done" to -- prevent the more expensive checks for read only access (which is -- allowed for all users, when staging is done). EXECUTE format($$ CREATE POLICY responsibility_area_select ON waterway.%I FOR SELECT TO waterway_admin USING (staging_done OR %s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY responsibility_area_update ON waterway.%I FOR UPDATE TO waterway_admin USING (%s) $$, the_table, condition); EXECUTE format($$ CREATE POLICY responsibility_area_delete ON waterway.%I FOR DELETE TO waterway_admin USING (%s) $$, the_table, condition); END LOOP; END; $do$; -- In the case of sections differentiating between read and write -- access is not neccessary: the country code based access check is -- quiet cheap in this case and there are only (relatively) few -- sections in the system anyway. CREATE POLICY same_country ON waterway.sections FOR ALL TO waterway_admin USING (country = ( SELECT country FROM users.list_users WHERE username = current_user)); CREATE POLICY sys_admin ON users.stretches FOR ALL TO sys_admin USING (true); -- -- Tables without staging area -- -- Use three policies instead of one FOR ALL to avoid costly expressions -- being added in SELECT queries. CREATE POLICY responsibility_area_insert ON waterway.waterway_axis FOR INSERT TO waterway_admin WITH CHECK ((SELECT ST_Covers(a, ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a))) FROM users.current_user_area_utm() AS a (a))); CREATE POLICY responsibility_area_update ON waterway.waterway_axis FOR UPDATE TO waterway_admin USING ((SELECT ST_Covers(a, ST_Transform(CAST(wtwaxs AS geometry), ST_SRID(a))) FROM users.current_user_area_utm() AS a (a))); -- -- RLS policies for imports and import config -- CREATE POLICY same_country ON import.imports FOR ALL TO waterway_admin -- Relies on a user seeing only users from his own country: USING (EXISTS(SELECT 1 FROM users.list_users lu WHERE lu.username = imports.username)); ALTER table import.imports ENABLE ROW LEVEL SECURITY; -- The job running the import queue is running as sys_admin and login users -- with that role should be able to run imports without restrictions anyhow CREATE POLICY import_all ON import.imports FOR ALL TO sys_admin USING (true); -- For the given table, check whether the given value is used as primary key, -- bypassing row level security. CREATE OR REPLACE FUNCTION import.is_new_key( tablename varchar, kv anyelement) RETURNS boolean AS $$ DECLARE columnname varchar; DECLARE ret boolean; BEGIN columnname = (SELECT column_name FROM information_schema.constraint_column_usage k JOIN information_schema.table_constraints USING (constraint_name) WHERE k.table_name = tablename and constraint_type = 'PRIMARY KEY'); EXECUTE format('SELECT NOT EXISTS(SELECT 1 FROM import.%I WHERE %I = $1)', tablename, columnname) INTO ret USING kv; RETURN ret; END; $$ LANGUAGE plpgsql SECURITY DEFINER STABLE PARALLEL SAFE; CREATE POLICY parent_allowed ON import.import_logs FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)) WITH CHECK (import.is_new_key('imports', import_id) OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)); ALTER table import.import_logs ENABLE ROW LEVEL SECURITY; CREATE POLICY parent_allowed ON import.track_imports FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)) WITH CHECK (import.is_new_key('imports', import_id) OR EXISTS(SELECT 1 FROM import.imports WHERE id = import_id)); ALTER table import.track_imports ENABLE ROW LEVEL SECURITY; CREATE POLICY import_configuration_policy ON import.import_configuration FOR ALL TO waterway_admin -- Relies on a user seeing only users from his own country: USING (EXISTS(SELECT 1 FROM users.list_users lu WHERE lu.username = import_configuration.username)); CREATE POLICY import_configuration_policy_sys_admin ON import.import_configuration FOR ALL TO sys_admin USING (true); ALTER table import.import_configuration ENABLE ROW LEVEL SECURITY; CREATE POLICY parent_allowed ON import.import_configuration_attributes FOR ALL TO waterway_admin USING (EXISTS(SELECT 1 FROM import.import_configuration WHERE id = import_configuration_id)) WITH CHECK ( import.is_new_key('import_configuration', import_configuration_id) OR EXISTS(SELECT 1 FROM import.import_configuration WHERE id = import_configuration_id)); ALTER table import.import_configuration_attributes ENABLE ROW LEVEL SECURITY; COMMIT;