Mercurial > gemma
annotate schema/std_login_roles.sql @ 5637:7f9600ab5461
merge
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Wed, 14 Jun 2023 08:45:11 +0200 |
parents | 5ff9d2f9d357 |
children |
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 | 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; |