Mercurial > gemma
annotate schema/roles.sql @ 4505:30c499f996d7
Simplified code.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 02 Oct 2019 16:00:42 +0200 |
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(); |