Mercurial > gemma

annotate schema/std_login_roles.sql @ 5652:9b3779f649c2 v5.7.1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Bumped version for release.
author Sascha Wilde <wilde@sha-bang.de>
date Mon, 07 Aug 2023 17:59:18 +0200
parents 5ff9d2f9d357
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
6
4413
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Sascha Wilde <wilde@intevation.de>
1336
f65d1767452c add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1301
diff changeset
13 -- * Tom Gottfried <tom@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
14
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15 BEGIN;
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 -- Standard users for the GEMMA database
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20
399
Tom Gottfried <tom@intevation.de>
parents: 330
diff changeset
21 -- NOTE: Passwords for these roles must be set during initial setup of
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 -- the database.
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
24 CREATE PROCEDURE create_login_roles(adminpw varchar, metapw varchar)
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
25 AS $$
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
26 DECLARE
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
27 dummy_country CONSTANT varchar = '--';
4413
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
28
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
29 -- Role names and attributes
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
30 admin CONSTANT varchar = 'sysadmin';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
31 adminrole CONSTANT varchar = 'sys_admin';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
32 box CONSTANT box2d = 'BOX(9.52115482500011 46.3786430870001,
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
33 17.1483378500001 49.0097744750001)';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
34 meta CONSTANT varchar = 'meta_login';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
35 metarole CONSTANT varchar = 'metamorph';
4413
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
36
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
37 -- Messages
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
38 warn_message CONSTANT varchar = 'Role %I already exists';
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
39 warn_detail CONSTANT varchar =
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
40 'Password, role attributes and memberships are kept as is';
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
41 err_message CONSTANT varchar =
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
42 'Role %I already exists but lacks necessary privileges';
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
43 BEGIN
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
44 --
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
45 -- Admin User
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
46 --
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
47 -- We need an empty dummy country for the default admin, as the user is
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
48 -- not supposed to work on data, it should be only used to create
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
49 -- personalized accounts.
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
50 INSERT INTO countries (country_code) VALUES (dummy_country);
2006
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
51
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
52 -- Initial Admin account used to bootstrap the personalized accounts
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
53 IF to_regrole(admin) IS NULL THEN
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
54 INSERT INTO users.list_users VALUES (
5517
5ff9d2f9d357 Fixup rev. eec88a166251 and 8797274e2739: Adapt database initialization
Tom Gottfried <tom@intevation.de>
parents: 4413
diff changeset
55 adminrole, admin, adminpw, dummy_country, box, '', false, true);
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
56 RAISE NOTICE 'Default admin user ''%'' created with password ''%''',
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
57 admin, adminpw;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
58 ELSE
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
59 IF pg_has_role(admin, adminrole, 'USAGE') THEN
4413
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
60 RAISE WARNING USING
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
61 MESSAGE = format(warn_message, admin),
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
62 DETAIL = warn_detail;
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
63 INSERT INTO internal.user_profiles (
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
64 username, map_extent, email_address, country)
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
65 VALUES (admin, box, '', dummy_country);
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
66 ELSE
4413
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
67 RAISE USING MESSAGE = format(err_message, admin);
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
68 END IF;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
69 END IF;
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
70
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
71 --
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
72 -- Functional Users
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
73 --
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
74
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
75 -- Used by GeoServer and backend
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
76 IF to_regrole(meta) IS NULL THEN
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
77 EXECUTE format('CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
78 meta, metarole, metapw);
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
79 RAISE NOTICE 'Backend user ''%'' created with password ''%''',
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
80 meta, metapw;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
81 ELSE
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
82 IF pg_has_role(meta, metarole, 'USAGE') THEN
4413
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
83 RAISE WARNING USING
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
84 MESSAGE = format(warn_message, meta),
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
85 DETAIL = warn_detail;
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
86 ELSE
4413
a554d7ca26ee Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents: 4405
diff changeset
87 RAISE USING MESSAGE = format(err_message, admin);
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
88 END IF;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
89 END IF;
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
90
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
91 END;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
92 $$ LANGUAGE plpgsql;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
93 CALL create_login_roles(:'adminpw', :'metapw');
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
94 DROP PROCEDURE create_login_roles(varchar, varchar);
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
95
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
96 COMMIT;