Mercurial > gemma

annotate schema/manage_users_tests.sql @ 367:7ecc4f8c895c

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge
author Thomas Junk <thomas.junk@intevation.de>
date Thu, 09 Aug 2018 13:11:50 +0200
parents f5087cebc740
children 3f803d64a6ee
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 --
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- pgTAP test script for user management functions
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 --
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
7 --
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
8 -- Role listing
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
9 --
263
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
10 SET SESSION AUTHORIZATION test_user_at;
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
11 SELECT results_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
12 SELECT username FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
13 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
14 $$
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
15 SELECT CAST(current_user AS varchar)
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
16 $$,
263
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
17 'User should only see his own profile');
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
18
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
19 SET SESSION AUTHORIZATION test_admin_at;
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
20 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
21 SELECT DISTINCT country FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
22 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
23 ARRAY['AT'],
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
24 'Waterway admin should only see profiles of his country');
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
25
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
26 SET SESSION AUTHORIZATION test_sys_admin1;
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
27 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
28 SELECT count(*) FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
29 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
30 ARRAY[4],
13ad969a9138 Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents: 262
diff changeset
31 'System admin can see all users');
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
32
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
33 --
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
34 -- Role creation
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
35 --
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 SELECT lives_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
37 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
38 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 'New waterway user can be added');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
43 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
44 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 42704, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 'Valid role name has to be provided');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
50 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
51 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 23502, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 'username is mandatory');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 -- Though other arguments are mandatory, too, there are no explicit tests
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
58 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
59 'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 $$,
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
61 42710, NULL,
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
62 'Reserved role names cannot be used as username');
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
63
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
64 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
65 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
66 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4')
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
67 $$,
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 23505, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 'No duplicate user name is allowed');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70
361
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
71 SELECT throws_ok($$
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
72 INSERT INTO users.list_users VALUES (
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
73 'waterway_user',
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
74 'Test Nutzer AT, Test User RO, Täst Nützer ÄT, Täst Üser RÖ',
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
75 'secret1$', 'AT', NULL, 'test4')
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
76 $$,
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
77 23514, NULL,
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
78 'User name length is restricted to 63 bytes');
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
79
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
80 -- Test password policy
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
81 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
82 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
83 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
84 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
85 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
86 'Password with less than 8 characters is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
87
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
88 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
89 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
90 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
91 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
92 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
93 'Password without non-alphanumeric character is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
94
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
95 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
96 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
97 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
98 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
99 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
100 'Password without digit is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
101
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
102 --
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
103 -- Role update
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
104 --
327
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
105
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
106 SET SESSION AUTHORIZATION test_user_at;
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
107
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
108 SELECT results_eq($$
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
109 UPDATE users.list_users
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
110 SET (pw, map_extent, email_address)
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
111 = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test')
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
112 RETURNING username
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
113 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
114 $$
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
115 SELECT CAST('test_user_at' AS varchar)
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
116 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
117 'Waterway user can update own password, map extent and email address');
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
118
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
119 SELECT throws_ok($$
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
120 UPDATE users.list_users
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
121 SET username = 'test_rename', rolname = 'test'
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
122 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
123 42501, NULL,
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
124 'Waterway user cannot update arbitrary user attributes');
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
125
334
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
126 SET SESSION AUTHORIZATION test_admin_at;
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
127
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
128 SELECT results_eq($$
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
129 UPDATE users.list_users
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
130 SET (pw, map_extent, email_address)
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
131 = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test')
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
132 WHERE country = users.current_user_country()
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
133 AND username <> current_user
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
134 RETURNING *
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
135 $$,
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
136 $$
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
137 SELECT '' WHERE false -- Empty result set
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
138 $$,
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
139 'Waterway admin cannot update attributes of other users in country');
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
140
327
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
141 SET SESSION AUTHORIZATION test_sys_admin1;
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
142
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
143 SELECT lives_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
144 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
145 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
146 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
147 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
148 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
149 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
150 WHERE username = 'test_user_at'), 'test5')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
151 WHERE username = 'test2'
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
152 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
153 'Existing user can be updated');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
154
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
155 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
156 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
157 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
158 = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
159 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
160 WHERE username = 'test_user_at'), 'test6')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
161 WHERE username = CAST(current_user AS varchar)
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
162 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
163 '0A000', NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
164 'Name of current user cannot be altered');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
165
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
166 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
167 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
168 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
169 = ('invalid', 'test2', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
170 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
171 WHERE username = 'test_user_at'), 'test2')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
172 WHERE username = 'test_user_at'
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
173 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
174 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
175 'Valid role name has to be provided');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
176
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
177 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
178 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
179 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
180 = ('waterway_user', NULL, 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
181 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
182 WHERE username = 'test_user_at'), 'test3')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
183 WHERE username = 'test_user_at'
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
184 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
185 23502, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
186 'New username is mandatory');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
187 -- Though other arguments are mandatory, too, there are no explicit tests
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
188
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
189 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
190 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
191 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
192 = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
193 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
194 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
195 WHERE username = 'test_user_at'
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
196 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
197 42710, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
198 'Reserved role names cannot be used as username');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
199
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
200 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
201 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
202 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
203 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
204 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
205 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
206 WHERE username = 'test_user_at'
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
207 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
208 23505, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
209 'No duplicate user name is allowed');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
210
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
211 -- Test password policy (only one rule to ensure it's also used on update)
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
212 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
213 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
214 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
215 = ('waterway_user', 'test_user_at', 'secret', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
216 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
217 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
218 WHERE username = 'test_user_at'
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
219 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
220 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
221 'Non-compliant password is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
222
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
223 -- To compare passwords, we need to run the following tests as superuser
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
224 RESET SESSION AUTHORIZATION;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
226 CREATE TEMP TABLE old_pw_hash AS
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
227 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
228
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
229 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
230 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
231 = ('waterway_user', 'test_user_at', NULL, 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
232 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
233 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
234 WHERE username = 'test_user_at';
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
235 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
236 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
237 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
238 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
239 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
240 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
241 'Giving NULL password does not change password');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
242
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
243 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
244 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
245 = ('waterway_user', 'test_user_at', '', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
246 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
247 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
248 WHERE username = 'test_user_at';
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
249 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
250 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
251 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
252 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
253 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
254 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
255 'Giving empty string as password does not change password');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
256
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
257 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
258 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
259 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
260 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
261 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
262 WHERE username = 'test_user_at';
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
263 SELECT set_ne($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
264 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
265 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
266 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
267 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
268 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
269 'Giving a non-empty password string changes password');
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
270
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
271 SET SESSION AUTHORIZATION test_sys_admin1;
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
272
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
273 --
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
274 -- Role deletion
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
275 --
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
276 -- Note: backend termination is not tested in the following.
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
277 -- See also comments in function definition.
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
278 SELECT lives_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
279 INSERT INTO users.list_users VALUES (
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
280 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
281 DELETE FROM users.list_users WHERE username = 'test3'
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
282 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
283 'Existing user can be deleted');
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
284
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
285 SELECT throws_ok($$
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
286 DELETE FROM users.list_users WHERE username = CAST(current_user AS varchar)
232
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
287 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
288 55006, NULL,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
289 'Current user cannot be deleted');
319
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
290
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
291
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
292 --
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
293 -- Password reset
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
294 --
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
295
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
296 -- Workaround broken relocatability of pgtap (otherwise we could
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
297 -- put pgtap in its own schema and GRANT USAGE to PUBLIC on it)
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
298 RESET SESSION AUTHORIZATION;
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
299 GRANT USAGE ON SCHEMA public TO pw_reset;
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
300
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
301 SET SESSION AUTHORIZATION test_pw_reset;
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
302
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
303 SELECT isnt_empty($$
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
304 SELECT username, email_address FROM pw_reset.list_users
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
305 $$,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
306 'Special role can see users with their email addresses');
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
307
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
308 SELECT results_eq($$
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
309 UPDATE pw_reset.list_users
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
310 SET pw = 'user_at2!' WHERE username = 'test_user_at'
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
311 RETURNING email_address
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
312 $$,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
313 $$
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
314 SELECT email_address FROM pw_reset.list_users
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
315 WHERE username = 'test_user_at'
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
316 $$,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
317 'Special role can update password');
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
318
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
319 SELECT throws_ok($$
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
320 UPDATE pw_reset.list_users
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
321 SET username = 'test_rename', email_address = 'test'
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
322 $$,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
323 42501, NULL,
ac760b0f22a9 Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents: 307
diff changeset
324 'Special role cannot update arbitrary user attributes');