Mercurial > gemma
view schema/std_login_roles.sql @ 4405:7657640f0c8d
Gracefully handle existing default login roles
If multiple databases are created in the same PostgreSQL cluster,
existing roles are now properly re-used. This also fixes running
database tests on a cluster that already has these roles, which
was broken since rev. 5e38667f740c, because it silently fixed a test
for existance of roles that failed if proper creation of the
role 'sysadmin' failed during setup of the test database.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Mon, 16 Sep 2019 19:58:13 +0200 |
parents | 5e38667f740c |
children | a554d7ca26ee |
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 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 = '--'; 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'; 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 'Role ''%'' already exists', admin USING DETAIL = 'Password and role memberships are kept as is'; INSERT INTO internal.user_profiles ( username, map_extent, email_address, country) VALUES (admin, box, '', dummy_country); ELSE RAISE 'Role ''%'' already exists but lacks necessary privileges', 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 'Role ''%'' already exists', meta USING DETAIL = 'Password and role memberships are kept as is'; ELSE RAISE 'Role ''%'' already exists but lacks necessary privileges', meta; END IF; END IF; END; $$ LANGUAGE plpgsql; CALL create_login_roles(:'adminpw', :'metapw'); DROP PROCEDURE create_login_roles(varchar, varchar); COMMIT;