Mercurial > gemma

annotate schema/roles.sql @ 5590:826e67e959c9 surveysperbottleneckid

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
For BN Overview display id for all doubles
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Apr 2022 13:02:13 +0200
parents a554d7ca26ee
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: 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();