Mercurial > gemma

annotate schema/std_login_roles.sql @ 2006:35acb7f9ae0c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Do anything else before expectedly failing role creation Creating roles during database setup expectedly fails in case there already is another gemma database in the cluster. Doing it at the end of the transaction ensures it does not hide errors in other commands in the script. In passing, add the default admin via the designated view to ensure it will become a correctly set up application user.
author Tom Gottfried <tom@intevation.de>
date Thu, 24 Jan 2019 17:23:43 +0100
parents 917c672591c2
children 5e38667f740c
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
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 -- Admin User
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26 --
399
Tom Gottfried <tom@intevation.de>
parents: 330
diff changeset
27 -- We need an empty dummy country for the default admin, as the user is
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 -- not supposed to work on data, it should be only used to create
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 -- personalized accounts.
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30 INSERT INTO countries (country_code) VALUES ('--');
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31 INSERT INTO users.responsibility_areas (country, area)
2003
917c672591c2 login_roles: temp fix with rectangle instead of point
Thomas Junk <thomas.junk@intevation.de>
parents: 1338
diff changeset
32 VALUES ('--', ST_GeomFromText('MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)))', 4326));
2006
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
33
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
34 -- This initial Admin account is used to bootstrap the personalized accounts.
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
35 INSERT INTO users.list_users VALUES (
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
36 'sys_admin',
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
37 'sysadmin',
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
38 :'adminpw',
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
39 '--',
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
40 'BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001)',
35acb7f9ae0c Do anything else before expectedly failing role creation
Tom Gottfried <tom@intevation.de>
parents: 2003
diff changeset
41 '');
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 -- Functional Users
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 399
diff changeset
47 -- Used by GeoServer and backend
470
638371a0e557 Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents: 463
diff changeset
48 CREATE ROLE meta_login IN ROLE metamorph LOGIN PASSWORD :'metapw';
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49
470
638371a0e557 Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents: 463
diff changeset
50 -- Emit messages to the client if everything went ok
638371a0e557 Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents: 463
diff changeset
51 SELECT 'Default admin user ''sysadmin'' created with password ' || :'adminpw';
638371a0e557 Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents: 463
diff changeset
52 SELECT 'Backend user ''meta_login'' created with password ' || :'metapw';
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
53
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54 COMMIT;