Mercurial > gemma

comparison schema/std_login_roles.sql @ 4405:7657640f0c8d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Gracefully handle existing default login roles If multiple databases are created in the same PostgreSQL cluster, existing roles are now properly re-used. This also fixes running database tests on a cluster that already has these roles, which was broken since rev. 5e38667f740c, because it silently fixed a test for existance of roles that failed if proper creation of the role 'sysadmin' failed during setup of the test database.
author Tom Gottfried <tom@intevation.de>
date Mon, 16 Sep 2019 19:58:13 +0200
parents 5e38667f740c
children a554d7ca26ee
comparison
equal deleted inserted replaced
4404:b0c974fc7d34 4405:7657640f0c8d
19 -- 19 --
20 20
21 -- NOTE: Passwords for these roles must be set during initial setup of 21 -- NOTE: Passwords for these roles must be set during initial setup of
22 -- the database. 22 -- the database.
23 23
24 -- 24 CREATE PROCEDURE create_login_roles(adminpw varchar, metapw varchar)
25 -- Admin User 25 AS $$
26 -- 26 DECLARE
27 -- We need an empty dummy country for the default admin, as the user is 27 dummy_country CONSTANT varchar = '--';
28 -- not supposed to work on data, it should be only used to create 28 admin CONSTANT varchar = 'sysadmin';
29 -- personalized accounts. 29 adminrole CONSTANT varchar = 'sys_admin';
30 INSERT INTO countries (country_code) VALUES ('--'); 30 box CONSTANT box2d = 'BOX(9.52115482500011 46.3786430870001,
31 17.1483378500001 49.0097744750001)';
32 meta CONSTANT varchar = 'meta_login';
33 metarole CONSTANT varchar = 'metamorph';
34 BEGIN
35 --
36 -- Admin User
37 --
38 -- We need an empty dummy country for the default admin, as the user is
39 -- not supposed to work on data, it should be only used to create
40 -- personalized accounts.
41 INSERT INTO countries (country_code) VALUES (dummy_country);
31 42
32 -- This initial Admin account is used to bootstrap the personalized accounts. 43 -- Initial Admin account used to bootstrap the personalized accounts
33 INSERT INTO users.list_users VALUES ( 44 IF to_regrole(admin) IS NULL THEN
34 'sys_admin', 45 INSERT INTO users.list_users VALUES (
35 'sysadmin', 46 adminrole, admin, adminpw, dummy_country, box, '');
36 :'adminpw', 47 RAISE NOTICE 'Default admin user ''%'' created with password ''%''',
37 '--', 48 admin, adminpw;
38 'BOX(9.52115482500011 46.3786430870001,17.1483378500001 49.0097744750001)', 49 ELSE
39 ''); 50 IF pg_has_role(admin, adminrole, 'USAGE') THEN
51 RAISE WARNING
52 'Role ''%'' already exists', admin
53 USING DETAIL = 'Password and role memberships are kept as is';
54 INSERT INTO internal.user_profiles (
55 username, map_extent, email_address, country)
56 VALUES (admin, box, '', dummy_country);
57 ELSE
58 RAISE 'Role ''%'' already exists but lacks necessary privileges',
59 admin;
60 END IF;
61 END IF;
40 62
41 -- 63 --
42 -- Functional Users 64 -- Functional Users
43 -- 65 --
44 66
45 -- Used by GeoServer and backend 67 -- Used by GeoServer and backend
46 CREATE ROLE meta_login IN ROLE metamorph LOGIN PASSWORD :'metapw'; 68 IF to_regrole(meta) IS NULL THEN
69 EXECUTE format('CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
70 meta, metarole, metapw);
71 RAISE NOTICE 'Backend user ''%'' created with password ''%''',
72 meta, metapw;
73 ELSE
74 IF pg_has_role(meta, metarole, 'USAGE') THEN
75 RAISE WARNING
76 'Role ''%'' already exists', meta
77 USING DETAIL = 'Password and role memberships are kept as is';
78 ELSE
79 RAISE
80 'Role ''%'' already exists but lacks necessary privileges',
81 meta;
82 END IF;
83 END IF;
47 84
48 -- Emit messages to the client if everything went ok 85 END;
49 SELECT 'Default admin user ''sysadmin'' created with password ' || :'adminpw'; 86 $$ LANGUAGE plpgsql;
50 SELECT 'Backend user ''meta_login'' created with password ' || :'metapw'; 87 CALL create_login_roles(:'adminpw', :'metapw');
88 DROP PROCEDURE create_login_roles(varchar, varchar);
51 89
52 COMMIT; 90 COMMIT;