Mercurial > gemma
view schema/roles.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 | a554d7ca26ee |
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 Gottfried <tom@intevation.de> CREATE PROCEDURE create_roles() AS $$ DECLARE -- Role names wwuser CONSTANT varchar = 'waterway_user'; wwadmin CONSTANT varchar = 'waterway_admin'; sysadmin CONSTANT varchar = 'sys_admin'; metarole CONSTANT varchar = 'metamorph'; -- Messages warn_message CONSTANT varchar = 'Role %I already exists'; warn_detail CONSTANT varchar = 'Role attributes and memberships are kept as is'; err_message CONSTANT varchar = 'Role %I already exists but lacks necessary privileges'; BEGIN -- -- Primary GEMMA roles -- IF to_regrole(wwuser) IS NULL THEN EXECUTE format('CREATE ROLE %I', wwuser); ELSE RAISE WARNING USING MESSAGE = format(warn_message, wwuser), DETAIL = warn_detail; END IF; IF to_regrole(wwadmin) IS NULL THEN EXECUTE format('CREATE ROLE %I IN ROLE %I', wwadmin, wwuser); ELSE IF pg_has_role(wwadmin, wwuser, 'USAGE') THEN RAISE WARNING USING MESSAGE = format(warn_message, wwadmin), DETAIL = warn_detail; ELSE RAISE USING MESSAGE = format(err_message, wwadmin); END IF; END IF; IF to_regrole(sysadmin) IS NULL THEN EXECUTE format('CREATE ROLE %I IN ROLE %I', sysadmin, wwadmin); ELSE IF pg_has_role(sysadmin, wwadmin, 'USAGE') THEN RAISE WARNING USING MESSAGE = format(warn_message, sysadmin), DETAIL = warn_detail; ELSE RAISE USING MESSAGE = format(err_message, sysadmin); END IF; END IF; -- -- Special roles -- -- A role that is intended to be used for backend- or -- GeoServer-connections on which SET ROLE has to be used to -- gain privileges of a specific role IF to_regrole(metarole) IS NULL THEN EXECUTE format('CREATE ROLE %I NOINHERIT', metarole); ELSE IF (SELECT NOT rolinherit FROM pg_roles WHERE rolname = metarole) THEN RAISE WARNING USING MESSAGE = format(warn_message, metarole), DETAIL = warn_detail; ELSE RAISE USING MESSAGE = format(err_message, metarole); END IF; END IF; END; $$ LANGUAGE plpgsql; CALL create_roles(); DROP PROCEDURE create_roles();