Mercurial > gemma

annotate schema/manage_users_tests.sql @ 1234:1a5564655f2a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
refac: Sidebar reorganized In order to make context switches between administrative tasks which are map related and those which are system related, we now have a category "administration" and "systemadministration". The Riverbedmorphology does nothing than display the map, so it is renamed to that (map). In case the context of "systemadministration" is chosen, the "map" brings you just back to the map.
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 20 Nov 2018 09:54:53 +0100
parents 3af7ca761f6a
children 6590208e3ee1
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
463
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
42 SELECT results_eq($$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
43 SELECT pg_has_role('metamorph', 'test1', 'MEMBER')
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
44 $$,
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
45 $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
46 SELECT true
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
47 $$,
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
48 'New role is GRANTed to metamorph after creation');
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents: 410
diff changeset
49
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
51 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
52 '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
53 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 42704, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 '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
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', NULL, 'secret1$', 'AT', NULL, 'test3')
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 23502, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 'username is mandatory');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 -- 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
64
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
66 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
67 '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
68 $$,
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
69 42710, NULL,
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
70 '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
71
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
72 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
73 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
74 '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
75 $$,
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 23505, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 'No duplicate user name is allowed');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78
361
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
79 SELECT throws_ok($$
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
80 INSERT INTO users.list_users VALUES (
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
81 'waterway_user',
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
82 '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
83 'secret1$', 'AT', NULL, 'test4')
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
84 $$,
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
85 23514, NULL,
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
86 'User name length is restricted to 63 bytes');
f5087cebc740 Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents: 343
diff changeset
87
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
88 -- Test password policy
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
89 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
90 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
91 '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
92 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
93 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
94 '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
95
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
96 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
97 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
98 '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
99 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
100 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
101 '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
102
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
103 SELECT throws_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
104 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
105 '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
106 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
107 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
108 'Password without digit is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
109
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
110 --
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
111 -- Role update
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
112 --
327
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 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
115
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
116 SELECT results_eq($$
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
117 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
118 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
119 = ('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
120 RETURNING username
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
121 $$,
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 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
124 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
125 '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
126
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
127 SELECT throws_ok($$
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
128 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
129 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
130 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
131 42501, NULL,
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
132 '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
133
334
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
134 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
135
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
136 SELECT results_eq($$
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
137 UPDATE users.list_users
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
138 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
139 = ('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
140 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
141 AND username <> current_user
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
142 RETURNING *
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
143 $$,
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
144 $$
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
145 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
146 $$,
df1fc589ad9d Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents: 327
diff changeset
147 '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
148
410
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
149 -- The above test will pass even if the password is actually updated in case
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
150 -- a trigger returns NULL after ALTER ROLE ... PASSWORD ... has been executed.
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
151 RESET SESSION AUTHORIZATION;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
152 CREATE TEMP TABLE old_pw_hash AS
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
153 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
154 SET SESSION AUTHORIZATION test_admin_at;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
155 UPDATE users.list_users
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
156 SET pw = 'test_user_at2!'
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
157 WHERE username = 'test_user_at';
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
158 RESET SESSION AUTHORIZATION;
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
159 SELECT set_eq($$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
160 SELECT rolpassword FROM old_pw_hash
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
161 $$,
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
162 $$
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
163 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
164 $$,
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
165 'Waterway admin cannot update password of other users in country');
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
166
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
167
327
363983d5c567 Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents: 319
diff changeset
168 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
169
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
170 SELECT lives_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
171 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
172 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
173 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
174 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
175 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
176 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
177 WHERE username = 'test_user_at'), 'test5')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
178 WHERE username = 'test2'
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
179 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
180 'Existing user can be updated');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
181
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
182 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
183 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
184 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
185 = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
186 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
187 WHERE username = 'test_user_at'), 'test6')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
188 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
189 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
190 '0A000', NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
191 '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
192
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
193 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
194 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
195 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
196 = ('invalid', 'test2', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
197 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
198 WHERE username = 'test_user_at'), 'test2')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
199 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
200 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
201 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
202 '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
203
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
204 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
205 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
206 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
207 = ('waterway_user', NULL, 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
208 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
209 WHERE username = 'test_user_at'), 'test3')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
210 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
211 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
212 23502, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
213 'New username is mandatory');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
214 -- 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
215
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
216 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
217 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
218 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
219 = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
220 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
221 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
222 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
223 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
224 42710, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
225 '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
226
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
227 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
228 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
229 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
230 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
231 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
232 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
233 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
234 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
235 23505, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
236 '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
237
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
238 -- 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
239 SELECT throws_ok($$
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
240 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
241 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
242 = ('waterway_user', 'test_user_at', 'secret', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
243 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
244 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
245 WHERE username = 'test_user_at'
262
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
246 $$,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
247 '28P01', NULL,
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
248 'Non-compliant password is not accepted');
92470caf81fd Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents: 247
diff changeset
249
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
250 -- 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
251 RESET SESSION AUTHORIZATION;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
252
410
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
253 UPDATE old_pw_hash SET rolpassword = (
3f803d64a6ee Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents: 361
diff changeset
254 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at');
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
255
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
256 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
257 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
258 = ('waterway_user', 'test_user_at', NULL, 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
259 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
260 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
261 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
262 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
263 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
264 $$,
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 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
267 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
268 '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
269
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
270 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
271 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
272 = ('waterway_user', 'test_user_at', '', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
273 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
274 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
275 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
276 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
277 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
278 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
279 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
280 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
281 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
282 '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
283
307
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
284 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
285 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
286 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
287 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
288 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents: 268
diff changeset
289 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
290 SELECT set_ne($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
291 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
292 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
293 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
294 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
295 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
296 '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
297
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
298 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
299
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
300 --
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
301 -- Role deletion
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
302 --
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
303 -- 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
304 -- 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
305 SELECT lives_ok($$
343
5b03f420957d Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents: 342
diff changeset
306 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
307 '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
308 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
309 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
310 'Existing user can be deleted');
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
311
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
312 SELECT throws_ok($$
342
c6bd6ed18942 Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents: 334
diff changeset
313 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
314 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
315 55006, NULL,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
316 'Current user cannot be deleted');