Mercurial > gemma
annotate schema/roles.sql @ 5560:f2204f91d286
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 | a554d7ca26ee |
children |
rev | line source |
---|---|
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
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:
478
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:
478
diff
changeset
|
3 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
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:
478
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:
478
diff
changeset
|
6 |
4413
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
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:
478
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
10 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
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 -- * Tom Gottfried <tom@intevation.de> |
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
13 |
4413
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
14 CREATE PROCEDURE create_roles() |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
15 AS $$ |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
16 DECLARE |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
17 -- Role names |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
18 wwuser CONSTANT varchar = 'waterway_user'; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
19 wwadmin CONSTANT varchar = 'waterway_admin'; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
20 sysadmin CONSTANT varchar = 'sys_admin'; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
21 metarole CONSTANT varchar = 'metamorph'; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
22 |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
23 -- Messages |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
24 warn_message CONSTANT varchar = 'Role %I already exists'; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
25 warn_detail CONSTANT varchar = |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
26 'Role attributes and memberships are kept as is'; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
27 err_message CONSTANT varchar = |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
28 'Role %I already exists but lacks necessary privileges'; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
29 BEGIN |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
30 -- |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
31 -- Primary GEMMA roles |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
32 -- |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
33 IF to_regrole(wwuser) IS NULL THEN |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
34 EXECUTE format('CREATE ROLE %I', wwuser); |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
35 ELSE |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
36 RAISE WARNING USING |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
37 MESSAGE = format(warn_message, wwuser), |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
38 DETAIL = warn_detail; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
39 END IF; |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
40 |
4413
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
41 IF to_regrole(wwadmin) IS NULL THEN |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
42 EXECUTE format('CREATE ROLE %I IN ROLE %I', wwadmin, wwuser); |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
43 ELSE |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
44 IF pg_has_role(wwadmin, wwuser, 'USAGE') THEN |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
45 RAISE WARNING USING |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
46 MESSAGE = format(warn_message, wwadmin), |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
47 DETAIL = warn_detail; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
48 ELSE |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
49 RAISE USING MESSAGE = format(err_message, wwadmin); |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
50 END IF; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
51 END IF; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
52 |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
53 IF to_regrole(sysadmin) IS NULL THEN |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
54 EXECUTE format('CREATE ROLE %I IN ROLE %I', sysadmin, wwadmin); |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
55 ELSE |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
56 IF pg_has_role(sysadmin, wwadmin, 'USAGE') THEN |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
57 RAISE WARNING USING |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
58 MESSAGE = format(warn_message, sysadmin), |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
59 DETAIL = warn_detail; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
60 ELSE |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
61 RAISE USING MESSAGE = format(err_message, sysadmin); |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
62 END IF; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
63 END IF; |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
64 |
4413
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
65 -- |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
66 -- Special roles |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
67 -- |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
68 |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
69 -- A role that is intended to be used for backend- or |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
70 -- GeoServer-connections on which SET ROLE has to be used to |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
71 -- gain privileges of a specific role |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
72 IF to_regrole(metarole) IS NULL THEN |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
73 EXECUTE format('CREATE ROLE %I NOINHERIT', metarole); |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
74 ELSE |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
75 IF (SELECT NOT rolinherit FROM pg_roles WHERE rolname = metarole) THEN |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
76 RAISE WARNING USING |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
77 MESSAGE = format(warn_message, metarole), |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
78 DETAIL = warn_detail; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
79 ELSE |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
80 RAISE USING MESSAGE = format(err_message, metarole); |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
81 END IF; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
82 END IF; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
83 |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
84 END; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
85 $$ LANGUAGE plpgsql; |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
86 CALL create_roles(); |
a554d7ca26ee
Do not ignore any errors in database setup
Tom Gottfried <tom@intevation.de>
parents:
1301
diff
changeset
|
87 DROP PROCEDURE create_roles(); |