Mercurial > gemma

annotate schema/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 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();