Mercurial > gemma

annotate schema/std_login_roles.sql @ 458:01deefb7ec7a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add GeoServer Dockerfile
author Tom Gottfried <tom@intevation.de>
date Wed, 22 Aug 2018 14:34:37 +0200
parents 6c09aa214948
children 5611cf72cc92
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 BEGIN;
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
2
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
3 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
4 -- Standard users for the GEMMA database
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
5 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
6
399
Tom Gottfried <tom@intevation.de>
parents: 330
diff changeset
7 -- 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
8 -- the database.
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
9
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
10 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
11 -- Admin User
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
12 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
13 -- This initial Admin account is used to bootstrap the personalized
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
14 -- accounts.
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15 CREATE ROLE sysadmin IN ROLE sys_admin LOGIN;
399
Tom Gottfried <tom@intevation.de>
parents: 330
diff changeset
16 -- 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
17 -- 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
18 -- personalized accounts.
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 INSERT INTO countries (country_code) VALUES ('--');
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 INSERT INTO users.responsibility_areas (country, area)
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 VALUES ('--', ST_GeomFromText('MULTIPOLYGON(((0 0, 0 0, 0 0, 0 0)))', 4326));
399
Tom Gottfried <tom@intevation.de>
parents: 330
diff changeset
22 -- Add user profile data for sysadmin
330
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 INSERT INTO internal.user_profiles
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 (username, country, email_address, map_extent)
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 VALUES ('sysadmin','--','',
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26 'BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001)');
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 -- Functional Users
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 -- Used by the back end (gemma)
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33 CREATE ROLE gemma_service IN ROLE pw_reset LOGIN;
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
34
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
35
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
36 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
37 -- Remind the caller of his duties
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38 --
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 DO language plpgsql $$
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 BEGIN
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 RAISE NOTICE 'Don''t forget to set a password for the user "sysadmin".';
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 RAISE NOTICE 'Don''t forget to set a password for the user "gemma_service".';
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 END
fd04bccae6ca Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 $$;
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 COMMIT;