Mercurial > gemma
annotate schema/manage_users_tests.sql @ 3548:f3102fa16a69
client: data availability/accuracy: inject mapId into layer/styles factory to fix display issue with two maps
This layer displays features based on which other layers are visible. To detect those other layers visibility realiably, the layer
needs to be aware about the map it belongs to.
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Fri, 31 May 2019 12:45:30 +0200 |
parents | 93fa55bce126 |
children | 966d7eb6d99b |
rev | line source |
---|---|
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
3 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
6 |
2912
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
7 -- Copyright (C) 2018, 2019 by via donau |
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
10 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
11 -- Author(s): |
1301
2304778c4432
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1298
diff
changeset
|
12 -- * Tom Gottfried <tom@intevation.de> |
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
13 |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 -- |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 -- 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
|
16 -- |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 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
|
19 |
2912
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
20 SET SESSION AUTHORIZATION test_user_at; |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
21 -- |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
22 -- Utility functions |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
23 -- |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
24 SELECT results_eq($$ |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
25 SELECT ST_SRID(users.current_user_area_utm()) |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
26 $$, |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
27 $$ |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
28 SELECT best_utm(area) |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
29 FROM users.responsibility_areas |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
30 WHERE country = users.current_user_country() |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
31 $$, |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
32 'Geometry has SRID corresponding to best_utm()'); |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
33 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
34 -- |
247
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
35 -- Role listing |
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
36 -- |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
37 SELECT results_eq($$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
38 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
|
39 $$, |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
40 $$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
41 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
|
42 $$, |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
43 '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
|
44 |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
45 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
|
46 SELECT set_eq($$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
47 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
|
48 $$, |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
49 ARRAY['AT'], |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
50 '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
|
51 |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
52 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
|
53 SELECT set_eq($$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
54 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
|
55 $$, |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
56 ARRAY[6], |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
57 '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
|
58 |
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
59 -- |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
60 -- Role creation |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
61 -- |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
62 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
63 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
|
64 '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
|
65 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
66 'New waterway user can be added'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 |
463
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
68 SELECT results_eq($$ |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
69 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
|
70 $$, |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
71 $$ |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
72 SELECT true |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
73 $$, |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
74 '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
|
75 |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
77 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
|
78 '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
|
79 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 42704, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
81 '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
|
82 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
83 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
84 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
|
85 '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
|
86 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
87 23502, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
88 'username is mandatory'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 -- 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
|
90 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
92 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
|
93 '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
|
94 $$, |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
95 42710, NULL, |
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
96 '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
|
97 |
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
98 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
99 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
|
100 '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
|
101 $$, |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 23505, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
103 'No duplicate user name is allowed'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
104 |
361
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
105 SELECT throws_ok($$ |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
106 INSERT INTO users.list_users VALUES ( |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
107 'waterway_user', |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
108 '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
|
109 'secret1$', 'AT', NULL, 'test4') |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
110 $$, |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
111 23514, NULL, |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
112 'User name length is restricted to 63 bytes'); |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
113 |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
114 -- Test password policy |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
115 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
116 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
|
117 '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
|
118 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
119 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
120 '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
|
121 |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
122 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
123 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
|
124 '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
|
125 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
126 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
127 '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
|
128 |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
129 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
130 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
|
131 '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
|
132 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
133 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
134 'Password without digit is not accepted'); |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
135 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
136 -- |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
137 -- Role update |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
138 -- |
327
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
139 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
140 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
|
141 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
142 SELECT results_eq($$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
143 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
|
144 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
|
145 = ('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
|
146 RETURNING username |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
147 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
148 $$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
149 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
|
150 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
151 '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
|
152 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
153 SELECT throws_ok($$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
154 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
|
155 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
|
156 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
157 42501, NULL, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
158 '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
|
159 |
334
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
160 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
|
161 |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
162 SELECT results_eq($$ |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
163 UPDATE users.list_users |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
164 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
|
165 = ('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
|
166 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
|
167 AND username <> current_user |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
168 RETURNING * |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
169 $$, |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
170 $$ |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
171 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
|
172 $$, |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
173 '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
|
174 |
410
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
175 -- 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
|
176 -- 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
|
177 RESET SESSION AUTHORIZATION; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
178 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
|
179 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
|
180 SET SESSION AUTHORIZATION test_admin_at; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
181 UPDATE users.list_users |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
182 SET pw = 'test_user_at2!' |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
183 WHERE username = 'test_user_at'; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
184 RESET SESSION AUTHORIZATION; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
185 SELECT set_eq($$ |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
186 SELECT rolpassword FROM old_pw_hash |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
187 $$, |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
188 $$ |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
189 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
|
190 $$, |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
191 '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
|
192 |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
193 |
327
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
194 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
|
195 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
196 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
197 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
|
198 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2'); |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
199 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
200 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
|
201 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
202 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
203 WHERE username = 'test_user_at'), 'test5') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
204 WHERE username = 'test2' |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
205 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
206 'Existing user can be updated'); |
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 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
209 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
210 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
|
211 = ('waterway_user', 'test_new_name', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
212 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
213 WHERE username = 'test_user_at'), 'test6') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
214 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
|
215 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
216 '0A000', NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
217 '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
|
218 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
219 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
220 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
221 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
|
222 = ('invalid', 'test2', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
223 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
224 WHERE username = 'test_user_at'), 'test2') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
225 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
|
226 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
227 42704, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
228 '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
|
229 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
230 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
231 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
232 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
|
233 = ('waterway_user', NULL, 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
234 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
235 WHERE username = 'test_user_at'), 'test3') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
236 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
|
237 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
238 23502, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
239 'New username is mandatory'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
240 -- 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
|
241 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
242 SELECT throws_ok($$ |
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', 'waterway_user', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
246 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
247 WHERE username = 'test_user_at'), 'test4') |
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 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
250 42710, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
251 '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
|
252 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
253 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
254 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
255 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
|
256 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
257 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
258 WHERE username = 'test_user_at'), 'test4') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
259 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
|
260 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
261 23505, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
262 '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
|
263 |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
264 -- 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
|
265 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
266 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
267 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
|
268 = ('waterway_user', 'test_user_at', 'secret', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
269 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
270 WHERE username = 'test_user_at'), 'test4') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
271 WHERE username = 'test_user_at' |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
272 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
273 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
274 'Non-compliant password is not accepted'); |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
275 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
276 -- 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
|
277 RESET SESSION AUTHORIZATION; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
278 |
410
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
279 UPDATE old_pw_hash SET rolpassword = ( |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
280 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
|
281 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
282 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
283 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
|
284 = ('waterway_user', 'test_user_at', NULL, 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
285 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
286 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
287 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
|
288 SELECT set_eq($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
289 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
|
290 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
291 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
292 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
|
293 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
294 '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
|
295 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
296 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
297 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
|
298 = ('waterway_user', 'test_user_at', '', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
299 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
300 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
301 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
|
302 SELECT set_eq($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
303 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
|
304 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
305 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
306 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
|
307 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
308 '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
|
309 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
310 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
311 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
|
312 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
313 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
314 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
315 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
|
316 SELECT set_ne($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
317 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
|
318 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
319 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
320 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
|
321 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
322 '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
|
323 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
324 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
|
325 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
326 -- |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
327 -- Role deletion |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
328 -- |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
329 -- 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
|
330 -- 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
|
331 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
332 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
|
333 '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
|
334 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
|
335 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
336 'Existing user can be deleted'); |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
337 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
338 SELECT throws_ok($$ |
342
c6bd6ed18942
Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents:
334
diff
changeset
|
339 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
|
340 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
341 55006, NULL, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
342 'Current user cannot be deleted'); |