Mercurial > gemma
annotate schema/std_login_roles.sql @ 887:aae517757923
fix: layout systemconfig
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Mon, 01 Oct 2018 17:41:00 +0200 |
parents | 67c3837bcc3d |
children | 6590208e3ee1 |
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 | 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. |
525
67c3837bcc3d
schema: Added missing membership in "metamorph" for "sysadmin" user.
Sascha Wilde <wilde@intevation.de>
parents:
478
diff
changeset
|
15 CREATE ROLE sysadmin IN ROLE sys_admin ROLE metamorph LOGIN PASSWORD :'adminpw'; |
470
638371a0e557
Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents:
463
diff
changeset
|
16 |
399 | 17 -- 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
|
18 -- 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
|
19 -- personalized accounts. |
fd04bccae6ca
Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 INSERT INTO countries (country_code) VALUES ('--'); |
fd04bccae6ca
Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 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
|
22 VALUES ('--', ST_GeomFromText('MULTIPOLYGON(((0 0, 0 0, 0 0, 0 0)))', 4326)); |
399 | 23 -- 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
|
24 INSERT INTO internal.user_profiles |
fd04bccae6ca
Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 (username, country, email_address, map_extent) |
fd04bccae6ca
Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 VALUES ('sysadmin','--','', |
fd04bccae6ca
Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 '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
|
28 |
fd04bccae6ca
Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 -- |
fd04bccae6ca
Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 -- Functional Users |
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 |
463
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
399
diff
changeset
|
33 -- Used by GeoServer and backend |
470
638371a0e557
Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents:
463
diff
changeset
|
34 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
|
35 |
470
638371a0e557
Do not touch existing roles on database setup
Tom Gottfried <tom@intevation.de>
parents:
463
diff
changeset
|
36 -- 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
|
37 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
|
38 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
|
39 |
fd04bccae6ca
Create standard roles as part of the base schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 COMMIT; |