Mercurial > gemma
view schema/std_login_roles.sql @ 5095:e21cbb9768a2
Prevent duplicate fairway areas
In principal, there can be only one or no fairway area at each point
on the map. Since polygons from real data will often be topologically
inexact, just disallow equal geometries. This will also help to
avoid importing duplicates with concurrent imports, once the history
of fairway dimensions will be preserved.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 25 Mar 2020 18:10:02 +0100 |
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;