Mercurial > gemma
view schema/auth.sql @ 3705:7006b92c0334
Handle updates (vs. historized and new versions) separately.
We need this distinction as updated data currently can not be
reviewed. More precisely: it can not be declined after review, as the
old data is updated in place.
The current exclusion from the review is a workaround and not meant to
be the final solution. Note that there are additional minor problems,
like the fact that the updated data is not counted as changed data for
the import.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 19 Jun 2019 17:00:08 +0200 |
parents | db87f34805fb |
children | 4f9a1ff2c2ee |
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, systemconf, 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 systemconf.feature_colours 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; -- Ensure templates are associated to the users country, if none is given ALTER TABLE users.templates ALTER COLUMN country SET DEFAULT users.current_user_country(); 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 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 INSERT, 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', '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('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; $$; -- -- 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 -- TODO: add all relevant tables here CREATE POLICY same_country ON waterway.gauge_measurements FOR ALL TO waterway_admin USING ((location).country_code = (SELECT country FROM users.list_users WHERE username = current_user) ); CREATE POLICY same_country ON waterway.waterway_profiles FOR ALL TO waterway_admin USING ((location).country_code = (SELECT country FROM users.list_users WHERE username = current_user)); CREATE POLICY responsibility_area ON waterway.bottlenecks FOR ALL TO waterway_admin USING (users.utm_covers(area)); CREATE POLICY responsibility_area ON waterway.sounding_results FOR ALL TO waterway_admin USING (users.utm_covers(area)); CREATE POLICY responsibility_area ON waterway.fairway_dimensions FOR ALL TO waterway_admin USING (users.utm_covers(area)); -- -- RLS policies for imports and import config -- CREATE POLICY same_country ON import.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 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.key_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 $1 = ANY(SELECT %I FROM import.%I)', columnname, tablename) 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 (import_id IN (SELECT id FROM import.imports)) WITH CHECK (import.is_new_key('imports', import_id) OR import_id IN (SELECT id FROM import.imports)); ALTER table import.import_logs ENABLE ROW LEVEL SECURITY; CREATE POLICY parent_allowed ON import.track_imports FOR ALL TO waterway_admin USING (import_id IN (SELECT id FROM import.imports)) WITH CHECK (import.is_new_key('imports', import_id) OR import_id IN (SELECT id FROM import.imports)); ALTER table import.track_imports ENABLE ROW LEVEL SECURITY; CREATE POLICY import_configuration_policy ON import.import_configuration FOR ALL TO waterway_admin USING ( (SELECT country FROM users.list_users WHERE username = current_user) = ( SELECT country FROM users.list_users lu WHERE lu.username = import.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 (import_configuration_id IN ( SELECT id FROM import.import_configuration)) WITH CHECK ( import.is_new_key('import_configuration', import_configuration_id) OR import_configuration_id IN ( SELECT id FROM import.import_configuration)); ALTER table import.import_configuration_attributes ENABLE ROW LEVEL SECURITY; COMMIT;