Mercurial > gemma
view schema/std_login_roles.sql @ 5313:1b4cb1545a44 zpg-exception
Allow ZPG as available depth-reference under stricter rules
LDC is present but no ZPG => add ZPG to the list
LDC is not present and no ZPG but BN is BG or RO => add ZPG to the list
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Wed, 16 Jun 2021 14:08:39 +0200 |
parents | a554d7ca26ee |
children | 5ff9d2f9d357 |
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): -- * Sascha Wilde <wilde@intevation.de> -- * Tom Gottfried <tom@intevation.de> BEGIN; -- -- Standard users for the GEMMA database -- -- NOTE: Passwords for these roles must be set during initial setup of -- the database. CREATE PROCEDURE create_login_roles(adminpw varchar, metapw varchar) AS $$ DECLARE dummy_country CONSTANT varchar = '--'; -- Role names and attributes admin CONSTANT varchar = 'sysadmin'; adminrole CONSTANT varchar = 'sys_admin'; box CONSTANT box2d = 'BOX(9.52115482500011 46.3786430870001, 17.1483378500001 49.0097744750001)'; meta CONSTANT varchar = 'meta_login'; metarole CONSTANT varchar = 'metamorph'; -- Messages warn_message CONSTANT varchar = 'Role %I already exists'; warn_detail CONSTANT varchar = 'Password, role attributes and memberships are kept as is'; err_message CONSTANT varchar = 'Role %I already exists but lacks necessary privileges'; BEGIN -- -- Admin User -- -- We need an empty dummy country for the default admin, as the user is -- not supposed to work on data, it should be only used to create -- personalized accounts. INSERT INTO countries (country_code) VALUES (dummy_country); -- Initial Admin account used to bootstrap the personalized accounts IF to_regrole(admin) IS NULL THEN INSERT INTO users.list_users VALUES ( adminrole, admin, adminpw, dummy_country, box, ''); RAISE NOTICE 'Default admin user ''%'' created with password ''%''', admin, adminpw; ELSE IF pg_has_role(admin, adminrole, 'USAGE') THEN RAISE WARNING USING MESSAGE = format(warn_message, admin), DETAIL = warn_detail; INSERT INTO internal.user_profiles ( username, map_extent, email_address, country) VALUES (admin, box, '', dummy_country); ELSE RAISE USING MESSAGE = format(err_message, admin); END IF; END IF; -- -- Functional Users -- -- Used by GeoServer and backend IF to_regrole(meta) IS NULL THEN EXECUTE format('CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', meta, metarole, metapw); RAISE NOTICE 'Backend user ''%'' created with password ''%''', meta, metapw; ELSE IF pg_has_role(meta, metarole, 'USAGE') THEN RAISE WARNING USING MESSAGE = format(warn_message, meta), DETAIL = warn_detail; ELSE RAISE USING MESSAGE = format(err_message, admin); END IF; END IF; END; $$ LANGUAGE plpgsql; CALL create_login_roles(:'adminpw', :'metapw'); DROP PROCEDURE create_login_roles(varchar, varchar); COMMIT;