Mercurial > gemma

annotate schema/std_login_roles.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
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;