Mercurial > gemma
annotate schema/manage_users_tests.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
parents | b33121a54793 |
children |
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 $$ |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
28 SELECT best_utm(ST_Collect(area::geometry)) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
29 FROM users.stretches st |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
30 JOIN users.stretch_countries stc ON stc.stretch_id = st.id |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
31 WHERE country = users.user_country() |
2912
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
32 $$, |
93fa55bce126
Add utility function to get users area of responsibility
Tom Gottfried <tom@intevation.de>
parents:
1904
diff
changeset
|
33 '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
|
34 |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
35 SELECT ok( |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
36 ST_IsValid(users.current_user_area_utm()), |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
37 'Returns valid geometry for stretches that touch each other'); |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
38 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
39 -- |
247
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
40 -- Role listing |
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
41 -- |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
42 SELECT results_eq($$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
43 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
|
44 $$, |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
45 $$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
46 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
|
47 $$, |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
48 '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
|
49 |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
50 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
|
51 SELECT set_eq($$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
52 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
|
53 $$, |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
54 ARRAY['AT'], |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
55 '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
|
56 |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
57 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
|
58 SELECT set_eq($$ |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
59 SELECT username FROM users.list_users |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
60 $$, |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
61 ARRAY[ |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
62 'sysadmin', |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
63 'test_admin_at', |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
64 'test_admin_at2', |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
65 'test_admin_ro', |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
66 'test_sys_admin1', |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
67 'test_user_at', |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
68 'test_user_ro' |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4303
diff
changeset
|
69 ], |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
70 '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
|
71 |
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
72 -- |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
73 -- Role creation |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
74 -- |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
76 INSERT INTO users.list_users VALUES ( |
5516
b33121a54793
Fixup rev. eec88a166251 and 8797274e2739: Adapt tests
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
77 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1', false, true) |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
79 'New waterway user can be added'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
80 |
463
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
81 SELECT results_eq($$ |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
82 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
|
83 $$, |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
84 $$ |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
85 SELECT true |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
86 $$, |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
87 '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
|
88 |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
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 '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
|
92 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
93 42704, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
94 '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
|
95 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
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', NULL, 'secret1$', 'AT', NULL, 'test3') |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
99 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
100 23502, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
101 'username is mandatory'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
102 -- 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
|
103 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
104 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
105 INSERT INTO users.list_users VALUES ( |
4723
baabc2b2f094
Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
106 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4') |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
107 $$, |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
108 42710, NULL, |
4723
baabc2b2f094
Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
109 'No existing role name is allowed'); |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
110 |
361
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
111 SELECT throws_ok($$ |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
112 INSERT INTO users.list_users VALUES ( |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
113 'waterway_user', |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
114 'Test Nutzer AT, Test User RO, Täst Nützer ÄT, Täst Üser RÖ', |
5516
b33121a54793
Fixup rev. eec88a166251 and 8797274e2739: Adapt tests
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
115 'secret1$', 'AT', NULL, 'test4', false, true) |
361
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
116 $$, |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
117 23514, NULL, |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
118 'User name length is restricted to 63 bytes'); |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
119 |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
120 -- Test password policy |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
121 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
122 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
|
123 '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
|
124 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
125 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
126 '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
|
127 |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
128 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
129 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
|
130 '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
|
131 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
132 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
133 '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
|
134 |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
135 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
136 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
|
137 '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
|
138 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
139 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
140 'Password without digit is not accepted'); |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
141 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
142 -- |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
143 -- Role update |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
144 -- |
327
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
145 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
146 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
|
147 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
148 SELECT results_eq($$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
149 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
|
150 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
|
151 = ('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
|
152 RETURNING username |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
153 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
154 $$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
155 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
|
156 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
157 '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
|
158 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
159 SELECT throws_ok($$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
160 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
|
161 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
|
162 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
163 42501, NULL, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
164 '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
|
165 |
334
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
166 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
|
167 |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
168 SELECT results_eq($$ |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
169 UPDATE users.list_users |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
170 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
|
171 = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test') |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
172 WHERE country = users.user_country() |
334
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
173 AND username <> current_user |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
174 RETURNING * |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
175 $$, |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
176 $$ |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
177 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
|
178 $$, |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
179 '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
|
180 |
410
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
181 -- 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
|
182 -- 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
|
183 RESET SESSION AUTHORIZATION; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
184 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
|
185 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
|
186 SET SESSION AUTHORIZATION test_admin_at; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
187 UPDATE users.list_users |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
188 SET pw = 'test_user_at2!' |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
189 WHERE username = 'test_user_at'; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
190 RESET SESSION AUTHORIZATION; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
191 SELECT set_eq($$ |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
192 SELECT rolpassword FROM old_pw_hash |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
193 $$, |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
194 $$ |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
195 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
|
196 $$, |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
197 '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
|
198 |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
199 |
327
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
200 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
|
201 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
202 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
203 INSERT INTO users.list_users VALUES ( |
5516
b33121a54793
Fixup rev. eec88a166251 and 8797274e2739: Adapt tests
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
204 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2', false, true); |
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', 'test2_new', 'new_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'), 'test5') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
210 WHERE username = 'test2' |
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 'Existing user can be updated'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
213 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
214 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
215 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
216 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
|
217 = ('waterway_user', 'test_new_name', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
218 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
219 WHERE username = 'test_user_at'), 'test6') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
220 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
|
221 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
222 '0A000', NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
223 '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
|
224 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
225 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
226 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
227 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
|
228 = ('invalid', 'test2', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
229 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
230 WHERE username = 'test_user_at'), 'test2') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
231 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
|
232 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
233 42704, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
234 '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
|
235 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
236 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
237 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
238 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
|
239 = ('waterway_user', NULL, 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
240 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
241 WHERE username = 'test_user_at'), 'test3') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
242 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
|
243 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
244 23502, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
245 'New username is mandatory'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
246 -- 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
|
247 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
248 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
249 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
250 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
|
251 = ('waterway_user', 'waterway_user', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
252 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
253 WHERE username = 'test_user_at'), 'test4') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
254 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
|
255 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
256 42710, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
257 '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
|
258 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
259 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
260 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
261 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
|
262 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
263 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
264 WHERE username = 'test_user_at'), 'test4') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
265 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
|
266 $$, |
4723
baabc2b2f094
Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
267 42710, NULL, |
baabc2b2f094
Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
4389
diff
changeset
|
268 'No existing role name is allowed'); |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
269 |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
270 -- 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
|
271 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
272 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
273 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
|
274 = ('waterway_user', 'test_user_at', 'secret', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
275 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
276 WHERE username = 'test_user_at'), 'test4') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
277 WHERE username = 'test_user_at' |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
278 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
279 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
280 'Non-compliant password is not accepted'); |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
281 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
282 -- 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
|
283 RESET SESSION AUTHORIZATION; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
284 |
410
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
285 UPDATE old_pw_hash SET rolpassword = ( |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
286 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
|
287 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
288 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
289 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
|
290 = ('waterway_user', 'test_user_at', NULL, 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
291 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
292 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
293 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
|
294 SELECT set_eq($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
295 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
|
296 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
297 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
298 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
|
299 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
300 '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
|
301 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
302 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
303 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
|
304 = ('waterway_user', 'test_user_at', '', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
305 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
306 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
307 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
|
308 SELECT set_eq($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
309 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
|
310 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
311 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
312 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
|
313 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
314 '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
|
315 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
316 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
317 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
|
318 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
319 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
320 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
321 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
|
322 SELECT set_ne($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
323 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
|
324 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
325 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
326 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
|
327 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
328 '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
|
329 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
330 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
|
331 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
332 -- |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
333 -- Role deletion |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
334 -- |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
335 -- 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
|
336 -- 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
|
337 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
338 INSERT INTO users.list_users VALUES ( |
5516
b33121a54793
Fixup rev. eec88a166251 and 8797274e2739: Adapt tests
Tom Gottfried <tom@intevation.de>
parents:
4740
diff
changeset
|
339 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3', false, true); |
342
c6bd6ed18942
Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents:
334
diff
changeset
|
340 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
|
341 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
342 'Existing user can be deleted'); |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
343 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
344 SELECT throws_ok($$ |
342
c6bd6ed18942
Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents:
334
diff
changeset
|
345 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
|
346 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
347 55006, NULL, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
348 'Current user cannot be deleted'); |