Mercurial > gemma
view schema/auth.sql @ 1234:1a5564655f2a
refac: Sidebar reorganized
In order to make context switches between administrative tasks
which are map related and those which are system related, we now have
a category "administration" and "systemadministration".
The Riverbedmorphology does nothing than display the map, so it is
renamed to that (map). In case the context of "systemadministration"
is chosen, the "map" brings you just back to the map.
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 20 Nov 2018 09:54:53 +0100 |
parents | d395b2940a82 |
children | 6590208e3ee1 |
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 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 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; GRANT INSERT, UPDATE, DELETE ON waterway.imports, waterway.import_logs, waterway.track_imports 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 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)); COMMIT;