Mercurial > gemma
view schema/auth.sql @ 1814:bda5cf9eb2e0
Translated using Weblate (Austrian German)
Currently translated at 98.4% (121 of 123 strings)
Translation: Gemma/client
Translate-URL: https://hosted.weblate.org/projects/gemma/client/de_AT/
author | Bernhard E. Reiter <bernhard.reiter@intevation.de> |
---|---|
date | Wed, 16 Jan 2019 15:21:59 +0000 |
parents | 40cbfd268aa9 |
children | 661597546ed9 |
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 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, systemconf TO waterway_user; GRANT SELECT ON ALL TABLES IN SCHEMA public, users, waterway TO waterway_user; GRANT SELECT ON systemconf.feature_colours 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; -- TODO: will there ever be UPDATEs and DELETEs or can we drop that for -- imported data due to historicisation? Special tables like -- import_configuration will further need UPDATE and DELETE privileges. GRANT INSERT, UPDATE, DELETE ON users.templates, users.user_templates TO waterway_admin; -- -- Extended privileges for sys_admin -- GRANT INSERT, UPDATE, DELETE ON users.list_users, 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; GRANT UPDATE ON systemconf.feature_colours TO sys_admin; GRANT UPDATE ON sys_admin.published_services TO sys_admin; GRANT INSERT, DELETE ON sys_admin.password_reset_requests 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 'username IN(SELECT username FROM users.list_users)' instead -- of 'username = current_user', because 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 DO LANGUAGE plpgsql $$ DECLARE the_table varchar; BEGIN FOREACH the_table IN ARRAY ARRAY[ 'gauge_measurements', 'sections_stretches', 'waterway_profiles', 'fairway_dimensions', '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('ALTER TABLE waterway.%I ENABLE ROW LEVEL SECURITY', the_table); END LOOP; END; $$; CREATE POLICY user_templates ON users.user_templates FOR ALL TO waterway_user USING (username IN(SELECT username FROM users.list_users)); 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 -- CREATE OR REPLACE FUNCTION best_utm(g geometry) RETURNS integer AS $$ DECLARE center geometry; BEGIN SELECT ST_Centroid(g) INTO center; RETURN CASE WHEN ST_Y(center) > 0 THEN 32600 ELSE 32700 END + floor((ST_X(center)+180)/6)::int + 1; END; $$ LANGUAGE plpgsql IMMUTABLE; CREATE OR REPLACE FUNCTION utm_covers(g geography) RETURNS boolean AS $$ DECLARE user_area geometry; utm integer; BEGIN SELECT area::geometry FROM users.responsibility_areas INTO user_area WHERE country = users.current_user_country(); SELECT best_utm(user_area) INTO utm; RETURN ST_Covers( ST_Transform(user_area, utm), ST_Transform(g::geometry, utm)); END; $$ LANGUAGE plpgsql STABLE; -- Staging area -- TODO: add all relevant tables here CREATE POLICY same_country ON waterway.gauge_measurements FOR ALL TO waterway_admin USING ((fk_gauge_id).country_code = users.current_user_country()); CREATE POLICY responsibility_area ON waterway.bottlenecks FOR ALL TO waterway_admin USING (utm_covers(area)); CREATE POLICY responsibility_area ON waterway.sounding_results FOR ALL TO waterway_admin USING (utm_covers(area)); -- Imports and import config CREATE POLICY same_country ON waterway.imports FOR ALL TO waterway_admin USING (users.current_user_country() = ( SELECT country FROM users.list_users lu WHERE lu.username = imports.username)); ALTER table waterway.imports ENABLE ROW LEVEL SECURITY; -- The job running the import queue is running as sys_admin and login users -- with that role should see all imports anyhow CREATE POLICY read_all ON waterway.imports FOR SELECT TO sys_admin USING (true); CREATE POLICY update_all ON waterway.imports FOR UPDATE TO sys_admin USING (true); CREATE POLICY parent_allowed ON waterway.import_logs FOR ALL TO waterway_admin USING (import_id IN (SELECT id FROM waterway.imports)); ALTER table waterway.import_logs ENABLE ROW LEVEL SECURITY; CREATE POLICY parent_allowed ON waterway.track_imports FOR ALL TO waterway_admin USING (import_id IN (SELECT id FROM waterway.imports)); ALTER table waterway.track_imports ENABLE ROW LEVEL SECURITY; CREATE POLICY import_configuration_policy ON waterway.import_configuration FOR ALL TO waterway_admin USING ( users.current_user_country() = ( SELECT country FROM users.list_users lu WHERE lu.username = waterway.import_configuration.username)); CREATE POLICY import_configuration_policy_sys_admin ON waterway.import_configuration FOR ALL TO sys_admin USING (true); ALTER table waterway.import_configuration ENABLE ROW LEVEL SECURITY; COMMIT;