Mercurial > gemma

comparison schema/manage_users.sql @ 307:750a9c9cd965

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use SQL UPDATE to update users This implies it's not a database error anymore to try to update a non-existent user. Thus, handle this as a HTTP-404 in the backend, which is in line with what GET does. Using UPDATE here will allow to GRANT column-wise privileges. The password has become part of the view to be updatable as well.
author Tom Gottfried <tom@intevation.de>
date Wed, 01 Aug 2018 15:49:38 +0200
parents 0b2d9f96ddb8
children 0745b4d336c4
comparison
equal deleted inserted replaced
306:70592a18c5c6 307:750a9c9cd965
36 SECURITY DEFINER 36 SECURITY DEFINER
37 STABLE PARALLEL SAFE; 37 STABLE PARALLEL SAFE;
38 38
39 39
40 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS 40 CREATE OR REPLACE VIEW users.list_users WITH (security_barrier) AS
41 SELECT r.rolname, p.* 41 SELECT
42 r.rolname,
43 p.username,
44 CAST('' AS varchar) AS pw,
45 p.country,
46 p.map_extent,
47 p.email_address
42 FROM internal.user_profiles p 48 FROM internal.user_profiles p
43 JOIN pg_roles u ON p.username = u.rolname 49 JOIN pg_roles u ON p.username = u.rolname
44 JOIN pg_auth_members a ON u.oid = a.member 50 JOIN pg_auth_members a ON u.oid = a.member
45 JOIN pg_roles r ON a.roleid = r.oid 51 JOIN pg_roles r ON a.roleid = r.oid
46 WHERE p.username = current_user 52 WHERE p.username = current_user
77 $$ 83 $$
78 LANGUAGE plpgsql 84 LANGUAGE plpgsql
79 SECURITY DEFINER; 85 SECURITY DEFINER;
80 86
81 87
82 CREATE OR REPLACE FUNCTION sys_admin.update_user( 88 CREATE OR REPLACE FUNCTION internal.update_user() RETURNS trigger
83 username internal.user_profiles.username%TYPE,
84 new_userrole varchar,
85 new_username internal.user_profiles.username%TYPE,
86 new_pw varchar,
87 new_country internal.user_profiles.country%TYPE,
88 new_map_extent internal.user_profiles.map_extent%TYPE,
89 new_email_address internal.user_profiles.email_address%TYPE
90 )
91 RETURNS void
92 AS $$ 89 AS $$
93 DECLARE 90 DECLARE
94 cur_username name; 91 cur_username varchar;
95 cur_userrole name;
96 BEGIN 92 BEGIN
97 cur_username = username; 93 cur_username = OLD.username;
98 94
99 UPDATE internal.user_profiles p 95 UPDATE internal.user_profiles p
100 SET (username, country, map_extent, email_address) 96 SET (username, country, map_extent, email_address)
101 = (new_username, new_country, new_map_extent, new_email_address) 97 = (NEW.username, NEW.country, NEW.map_extent, NEW.email_address)
102 WHERE p.username = cur_username; 98 WHERE p.username = cur_username;
103 99
104 IF new_username <> cur_username 100 IF NEW.username <> cur_username
105 THEN 101 THEN
106 EXECUTE format( 102 EXECUTE format(
107 'ALTER ROLE %I RENAME TO %I', username, new_username); 103 'ALTER ROLE %I RENAME TO %I', cur_username, NEW.username);
108 cur_username = new_username; 104 cur_username = NEW.username;
109 END IF; 105 END IF;
110 106
111 cur_userrole = rolname FROM pg_roles r 107 IF NEW.rolname <> OLD.rolname
112 JOIN pg_auth_members a ON r.oid = a.roleid
113 WHERE member = (
114 SELECT oid FROM pg_roles WHERE rolname = cur_username);
115 IF new_userrole <> cur_userrole
116 THEN 108 THEN
117 EXECUTE format( 109 EXECUTE format(
118 'REVOKE %I FROM %I', cur_userrole, cur_username); 110 'REVOKE %I FROM %I', OLD.rolname, cur_username);
111 EXECUTE format(
112 'GRANT %I TO %I', NEW.rolname, cur_username);
119 END IF; 113 END IF;
120 -- GRANT new_userrole unconditionally to ensure it's an error to upgrade
121 -- a non-existent cur_username (GRANTing a role twice is not an error)
122 EXECUTE format(
123 'GRANT %I TO %I', new_userrole, cur_username);
124 114
125 IF new_pw IS NOT NULL AND new_pw <> '' 115 IF NEW.pw IS NOT NULL AND NEW.pw <> ''
126 THEN 116 THEN
127 EXECUTE format( 117 EXECUTE format(
128 'ALTER ROLE %I PASSWORD %L', 118 'ALTER ROLE %I PASSWORD %L',
129 cur_username, 119 cur_username,
130 users.check_password(new_pw)); 120 users.check_password(NEW.pw));
131 END IF; 121 END IF;
122
123 -- Do not leak new password
124 NEW.pw = '';
125 RETURN NEW;
132 END; 126 END;
133 $$ 127 $$
134 LANGUAGE plpgsql 128 LANGUAGE plpgsql
135 SECURITY DEFINER; 129 SECURITY DEFINER;
130
131 CREATE TRIGGER update_user INSTEAD OF UPDATE ON users.list_users FOR EACH ROW
132 EXECUTE PROCEDURE internal.update_user();
136 133
137 134
138 CREATE OR REPLACE FUNCTION sys_admin.delete_user( 135 CREATE OR REPLACE FUNCTION sys_admin.delete_user(
139 username internal.user_profiles.username%TYPE 136 username internal.user_profiles.username%TYPE
140 ) 137 )