Mercurial > gemma
view schema/auth.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
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;