Mercurial > gemma

annotate schema/std_login_roles.sql @ 4410:3759b47f20f3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
FD_import: change unit of depth to meter for consistency
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 17 Sep 2019 16:08:45 +0200
parents 7657640f0c8d
children a554d7ca26ee
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: 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
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 525
diff changeset
7 -- Copyright (C) 2018 by via donau
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
Tom Gottfried <tom@intevation.de>
parents: 330
diff changeset
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 = '--';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
28 admin CONSTANT varchar = 'sysadmin';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
29 adminrole CONSTANT varchar = 'sys_admin';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
30 box CONSTANT box2d = 'BOX(9.52115482500011 46.3786430870001,
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
31 17.1483378500001 49.0097744750001)';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
32 meta CONSTANT varchar = 'meta_login';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
33 metarole CONSTANT varchar = 'metamorph';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
34 BEGIN
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
35 --
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
36 -- Admin User
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
37 --
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
38 -- 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
39 -- 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
40 -- personalized accounts.
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
41 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
42
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
43 -- 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
44 IF to_regrole(admin) IS NULL THEN
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
45 INSERT INTO users.list_users VALUES (
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
46 adminrole, admin, adminpw, dummy_country, box, '');
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
47 RAISE NOTICE 'Default admin user ''%'' created with password ''%''',
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
48 admin, adminpw;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
49 ELSE
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
50 IF pg_has_role(admin, adminrole, 'USAGE') THEN
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
51 RAISE WARNING
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
52 'Role ''%'' already exists', admin
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
53 USING DETAIL = 'Password and role memberships are kept as is';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
54 INSERT INTO internal.user_profiles (
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
55 username, map_extent, email_address, country)
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
56 VALUES (admin, box, '', dummy_country);
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
57 ELSE
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
58 RAISE 'Role ''%'' already exists but lacks necessary privileges',
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
59 admin;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
60 END IF;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
61 END IF;
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
63 --
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
64 -- Functional Users
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
65 --
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
66
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
67 -- Used by GeoServer and backend
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
68 IF to_regrole(meta) IS NULL THEN
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
69 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
70 meta, metarole, metapw);
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
71 RAISE NOTICE 'Backend user ''%'' created with password ''%''',
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
72 meta, metapw;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
73 ELSE
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
74 IF pg_has_role(meta, metarole, 'USAGE') THEN
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
75 RAISE WARNING
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
76 'Role ''%'' already exists', meta
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
77 USING DETAIL = 'Password and role memberships are kept as is';
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
78 ELSE
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
79 RAISE
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
80 'Role ''%'' already exists but lacks necessary privileges',
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
81 meta;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
82 END IF;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
83 END IF;
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
84
4405
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
85 END;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
86 $$ LANGUAGE plpgsql;
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
87 CALL create_login_roles(:'adminpw', :'metapw');
7657640f0c8d Gracefully handle existing default login roles
Tom Gottfried <tom@intevation.de>
parents: 4389
diff changeset
88 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
89
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
90 COMMIT;