Mercurial > gemma

comparison schema/manage_users.sql @ 262:92470caf81fd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add database function to check password against policy Though it is currently only used in sys_admin-exclusive functions, it is exposed to normal users because there is nothing to hide and users should be able to change (and check) their password, too.
author Tom Gottfried <tom@intevation.de>
date Fri, 27 Jul 2018 15:26:16 +0200
parents 946baea3d280
children 13ad969a9138
comparison
equal deleted inserted replaced
261:ab9859981ee3 262:92470caf81fd
1 -- 1 --
2 -- Functions encapsulating user management functionality and 2 -- Functions encapsulating user management functionality and
3 -- exposing it to privileged users 3 -- exposing it to appropriately privileged users
4 -- 4 --
5
6 CREATE OR REPLACE FUNCTION users.check_password(
7 pw varchar
8 )
9 RETURNS varchar
10 AS $$
11 DECLARE
12 min_len CONSTANT int = 8;
13 BEGIN
14 IF char_length(pw) < min_len
15 OR pw NOT SIMILAR TO '%[^[:alnum:]]%'
16 OR pw NOT SIMILAR TO '%[[:digit:]]%'
17 THEN
18 RAISE invalid_password USING MESSAGE = 'Invalid password';
19 ELSE
20 RETURN pw;
21 END IF;
22 END;
23 $$
24 LANGUAGE plpgsql;
25
5 26
6 CREATE OR REPLACE VIEW sys_admin.list_users AS 27 CREATE OR REPLACE VIEW sys_admin.list_users AS
7 SELECT r.rolname, p.* 28 SELECT r.rolname, p.*
8 FROM users.user_profiles p 29 FROM users.user_profiles p
9 JOIN pg_roles u ON p.username = u.rolname 30 JOIN pg_roles u ON p.username = u.rolname
28 WHERE ra.country = create_user.country; 49 WHERE ra.country = create_user.country;
29 END IF; 50 END IF;
30 INSERT INTO users.user_profiles VALUES ( 51 INSERT INTO users.user_profiles VALUES (
31 username, country, map_extent, email_address); 52 username, country, map_extent, email_address);
32 EXECUTE format( 53 EXECUTE format(
33 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L', username, userrole, pw); 54 'CREATE ROLE %I IN ROLE %I LOGIN PASSWORD %L',
55 username,
56 userrole,
57 users.check_password(pw));
34 END; 58 END;
35 $$ 59 $$
36 LANGUAGE plpgsql 60 LANGUAGE plpgsql
37 SECURITY DEFINER; 61 SECURITY DEFINER;
38 62
81 'GRANT %I TO %I', new_userrole, cur_username); 105 'GRANT %I TO %I', new_userrole, cur_username);
82 106
83 IF new_pw IS NOT NULL AND new_pw <> '' 107 IF new_pw IS NOT NULL AND new_pw <> ''
84 THEN 108 THEN
85 EXECUTE format( 109 EXECUTE format(
86 'ALTER ROLE %I PASSWORD %L', cur_username, new_pw); 110 'ALTER ROLE %I PASSWORD %L',
111 cur_username,
112 users.check_password(new_pw));
87 END IF; 113 END IF;
88 END; 114 END;
89 $$ 115 $$
90 LANGUAGE plpgsql 116 LANGUAGE plpgsql
91 SECURITY DEFINER; 117 SECURITY DEFINER;