Mercurial > gemma
view schema/roles.sql @ 4846:c69e35ec6adf
Explaining comment on constraint triggers
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 14 Nov 2019 14:53:15 +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();