Mercurial > gemma

comparison schema/manage_users_tests.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 f5087cebc740
comparison
equal deleted inserted replaced
342:c6bd6ed18942 343:5b03f420957d
1 -- 1 --
2 -- pgTAP test script for user management functions 2 -- pgTAP test script for user management functions
3 -- 3 --
4 4
5 SET search_path TO public, gemma, gemma_waterway, gemma_fairway; 5 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
6
7 SET SESSION AUTHORIZATION test_admin_at;
8
9 SELECT throws_ok($$
10 SELECT sys_admin.create_user(
11 'waterway_user', 'test0', 'secret1$', 'AT', NULL, 'test0')
12 $$,
13 42501, NULL,
14 'Less privileged user cannot call function in schema sys_admin');
15 6
16 -- 7 --
17 -- Role listing 8 -- Role listing
18 -- 9 --
19 SET SESSION AUTHORIZATION test_user_at; 10 SET SESSION AUTHORIZATION test_user_at;
41 32
42 -- 33 --
43 -- Role creation 34 -- Role creation
44 -- 35 --
45 SELECT lives_ok($$ 36 SELECT lives_ok($$
46 SELECT sys_admin.create_user( 37 INSERT INTO users.list_users VALUES (
47 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1') 38 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1')
48 $$, 39 $$,
49 'New waterway user can be added'); 40 'New waterway user can be added');
50 41
51 SELECT throws_ok($$ 42 SELECT throws_ok($$
52 SELECT sys_admin.create_user( 43 INSERT INTO users.list_users VALUES (
53 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2') 44 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
54 $$, 45 $$,
55 42704, NULL, 46 42704, NULL,
56 'Valid role name has to be provided'); 47 'Valid role name has to be provided');
57 48
58 SELECT throws_ok($$ 49 SELECT throws_ok($$
59 SELECT sys_admin.create_user( 50 INSERT INTO users.list_users VALUES (
60 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3') 51 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3')
61 $$, 52 $$,
62 23502, NULL, 53 23502, NULL,
63 'username is mandatory'); 54 'username is mandatory');
64 -- Though other arguments are mandatory, too, there are no explicit tests 55 -- Though other arguments are mandatory, too, there are no explicit tests
65 56
66 SELECT throws_ok($$ 57 SELECT throws_ok($$
67 SELECT sys_admin.create_user( 58 INSERT INTO users.list_users VALUES (
68 'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4') 59 'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4')
69 $$, 60 $$,
70 42710, NULL, 61 42710, NULL,
71 'Reserved role names cannot be used as username'); 62 'Reserved role names cannot be used as username');
72 63
73 SELECT throws_ok($$ 64 SELECT throws_ok($$
74 SELECT sys_admin.create_user( 65 INSERT INTO users.list_users VALUES (
75 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4') 66 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4')
76 $$, 67 $$,
77 23505, NULL, 68 23505, NULL,
78 'No duplicate user name is allowed'); 69 'No duplicate user name is allowed');
79 70
80 -- Test password policy 71 -- Test password policy
81 SELECT throws_ok($$ 72 SELECT throws_ok($$
82 SELECT sys_admin.create_user( 73 INSERT INTO users.list_users VALUES (
83 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2') 74 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2')
84 $$, 75 $$,
85 '28P01', NULL, 76 '28P01', NULL,
86 'Password with less than 8 characters is not accepted'); 77 'Password with less than 8 characters is not accepted');
87 78
88 SELECT throws_ok($$ 79 SELECT throws_ok($$
89 SELECT sys_admin.create_user( 80 INSERT INTO users.list_users VALUES (
90 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2') 81 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2')
91 $$, 82 $$,
92 '28P01', NULL, 83 '28P01', NULL,
93 'Password without non-alphanumeric character is not accepted'); 84 'Password without non-alphanumeric character is not accepted');
94 85
95 SELECT throws_ok($$ 86 SELECT throws_ok($$
96 SELECT sys_admin.create_user( 87 INSERT INTO users.list_users VALUES (
97 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2') 88 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2')
98 $$, 89 $$,
99 '28P01', NULL, 90 '28P01', NULL,
100 'Password without digit is not accepted'); 91 'Password without digit is not accepted');
101 92
139 'Waterway admin cannot update attributes of other users in country'); 130 'Waterway admin cannot update attributes of other users in country');
140 131
141 SET SESSION AUTHORIZATION test_sys_admin1; 132 SET SESSION AUTHORIZATION test_sys_admin1;
142 133
143 SELECT lives_ok($$ 134 SELECT lives_ok($$
144 SELECT sys_admin.create_user( 135 INSERT INTO users.list_users VALUES (
145 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2'); 136 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
146 UPDATE users.list_users 137 UPDATE users.list_users
147 SET (rolname, username, pw, country, map_extent, email_address) 138 SET (rolname, username, pw, country, map_extent, email_address)
148 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT', 139 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
149 (SELECT map_extent FROM users.list_users 140 (SELECT map_extent FROM users.list_users
274 -- Role deletion 265 -- Role deletion
275 -- 266 --
276 -- Note: backend termination is not tested in the following. 267 -- Note: backend termination is not tested in the following.
277 -- See also comments in function definition. 268 -- See also comments in function definition.
278 SELECT lives_ok($$ 269 SELECT lives_ok($$
279 SELECT sys_admin.create_user( 270 INSERT INTO users.list_users VALUES (
280 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3'); 271 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
281 DELETE FROM users.list_users WHERE username = 'test3' 272 DELETE FROM users.list_users WHERE username = 'test3'
282 $$, 273 $$,
283 'Existing user can be deleted'); 274 'Existing user can be deleted');
284 275