Mercurial > gemma

comparison schema/manage_users.sql @ 343:5b03f420957d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use INSTEAD OF trigger for user creation Now make the whole thing look like a real table. There is no more function in schema sys_admin, thus remove respective privilege test.
author Tom Gottfried <tom@intevation.de>
date Mon, 06 Aug 2018 13:25:18 +0200
parents c6bd6ed18942
children fe87457a05d7
comparison
equal deleted inserted replaced
342:c6bd6ed18942 343:5b03f420957d
54 AND p.country = users.current_user_country() 54 AND p.country = users.current_user_country()
55 OR pg_has_role('pw_reset', 'MEMBER') 55 OR pg_has_role('pw_reset', 'MEMBER')
56 OR pg_has_role('sys_admin', 'MEMBER'); 56 OR pg_has_role('sys_admin', 'MEMBER');
57 57
58 58
59 CREATE OR REPLACE FUNCTION sys_admin.create_user( 59 CREATE OR REPLACE FUNCTION internal.create_user() RETURNS trigger
60 userrole varchar,
61 username internal.user_profiles.username%TYPE,
62 pw varchar,
63 country internal.user_profiles.country%TYPE,
64 map_extent internal.user_profiles.map_extent%TYPE,
65 email_address internal.user_profiles.email_address%TYPE
66 )
67 RETURNS void
68 AS $$ 60 AS $$
69 BEGIN 61 BEGIN
70 IF map_extent IS NULL 62 IF NEW.map_extent IS NULL
71 THEN 63 THEN
72 map_extent = ST_Extent(area) FROM users.responsibility_areas ra 64 NEW.map_extent = ST_Extent(area) FROM users.responsibility_areas ra
73 WHERE ra.country = create_user.country; 65 WHERE ra.country = NEW.country;
74 END IF; 66 END IF;
75 INSERT INTO internal.user_profiles ( 67 INSERT INTO internal.user_profiles (
76 username, country, map_extent, email_address) 68 username, country, map_extent, email_address)
77 VALUES (username, country, map_extent, email_address); 69 VALUES (NEW.username, NEW.country, NEW.map_extent, NEW.email_address);
78 EXECUTE format( 70 EXECUTE format(
79 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', 71 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
80 username, 72 NEW.username,
81 userrole, 73 NEW.rolname,
82 internal.check_password(pw)); 74 internal.check_password(NEW.pw));
75
76 -- Do not leak new password
77 NEW.pw = '';
78 RETURN NEW;
83 END; 79 END;
84 $$ 80 $$
85 LANGUAGE plpgsql 81 LANGUAGE plpgsql
86 SECURITY DEFINER; 82 SECURITY DEFINER;
83
84 CREATE TRIGGER create_user INSTEAD OF INSERT ON users.list_users FOR EACH ROW
85 EXECUTE PROCEDURE internal.create_user();
87 86
88 87
89 CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger 88 CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger
90 AS $$ 89 AS $$
91 DECLARE 90 DECLARE