Mercurial > gemma
annotate schema/manage_users_tests.sql @ 2101:9372e4604a2c
different colors for varying levels of service for fairway dimensions
issue254
author | Markus Kottlaender <markus@intevation.de> |
---|---|
date | Mon, 04 Feb 2019 13:01:06 +0100 |
parents | 931b15be6d7f |
children | 93fa55bce126 |
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 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
478
diff
changeset
|
7 -- Copyright (C) 2018 by via donau |
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 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
20 -- |
247
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
21 -- Role listing |
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
22 -- |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
23 SET SESSION AUTHORIZATION test_user_at; |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
24 SELECT results_eq($$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
25 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
|
26 $$, |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
27 $$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
28 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
|
29 $$, |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
30 '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
|
31 |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
32 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
|
33 SELECT set_eq($$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
34 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
|
35 $$, |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
36 ARRAY['AT'], |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
37 '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
|
38 |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
39 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
|
40 SELECT set_eq($$ |
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
41 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
|
42 $$, |
1904
931b15be6d7f
Complement authorisation tests for import management
Tom Gottfried <tom@intevation.de>
parents:
1873
diff
changeset
|
43 ARRAY[6], |
263
13ad969a9138
Enable listing of users for all roles with appropriate filters
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
44 '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
|
45 |
946baea3d280
Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
46 -- |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
47 -- Role creation |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
48 -- |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
50 INSERT INTO users.list_users VALUES ( |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
51 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1') |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 'New waterway user can be added'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 |
463
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
55 SELECT results_eq($$ |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
56 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
|
57 $$, |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
58 $$ |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
59 SELECT true |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
60 $$, |
5611cf72cc92
Add metamorphic database role and user e.g. for GeoServer
Tom Gottfried <tom@intevation.de>
parents:
410
diff
changeset
|
61 '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
|
62 |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
63 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
64 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
|
65 '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
|
66 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
67 42704, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
68 '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
|
69 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
70 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
71 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
|
72 '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
|
73 $$, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
74 23502, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
75 'username is mandatory'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
76 -- 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
|
77 |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
78 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
79 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
|
80 '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
|
81 $$, |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
82 42710, NULL, |
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
83 '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
|
84 |
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
196
diff
changeset
|
85 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
86 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
|
87 '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
|
88 $$, |
185
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
89 23505, NULL, |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
90 'No duplicate user name is allowed'); |
a9d9c2b1d08c
Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
91 |
361
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
92 SELECT throws_ok($$ |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
93 INSERT INTO users.list_users VALUES ( |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
94 'waterway_user', |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
95 '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
|
96 'secret1$', 'AT', NULL, 'test4') |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
97 $$, |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
98 23514, NULL, |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
99 'User name length is restricted to 63 bytes'); |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
343
diff
changeset
|
100 |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
101 -- Test password policy |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
102 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
103 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
|
104 '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
|
105 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
106 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
107 '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
|
108 |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
109 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
110 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
|
111 '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
|
112 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
113 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
114 '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
|
115 |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
116 SELECT throws_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
117 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
|
118 '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
|
119 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
120 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
121 'Password without digit is not accepted'); |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
122 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
123 -- |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
124 -- Role update |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
125 -- |
327
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
126 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
127 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
|
128 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
129 SELECT results_eq($$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
130 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
|
131 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
|
132 = ('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
|
133 RETURNING username |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
134 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
135 $$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
136 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
|
137 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
138 '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
|
139 |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
140 SELECT throws_ok($$ |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
141 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
|
142 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
|
143 $$, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
144 42501, NULL, |
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
145 '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
|
146 |
334
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
147 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
|
148 |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
149 SELECT results_eq($$ |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
150 UPDATE users.list_users |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
151 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
|
152 = ('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
|
153 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
|
154 AND username <> current_user |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
155 RETURNING * |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
156 $$, |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
157 $$ |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
158 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
|
159 $$, |
df1fc589ad9d
Prevent Waterway Admins from updating users from their country
Tom Gottfried <tom@intevation.de>
parents:
327
diff
changeset
|
160 '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
|
161 |
410
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
162 -- 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
|
163 -- 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
|
164 RESET SESSION AUTHORIZATION; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
165 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
|
166 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
|
167 SET SESSION AUTHORIZATION test_admin_at; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
168 UPDATE users.list_users |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
169 SET pw = 'test_user_at2!' |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
170 WHERE username = 'test_user_at'; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
171 RESET SESSION AUTHORIZATION; |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
172 SELECT set_eq($$ |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
173 SELECT rolpassword FROM old_pw_hash |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
174 $$, |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
175 $$ |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
176 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
|
177 $$, |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
178 '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
|
179 |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
180 |
327
363983d5c567
Allow Waterway User to update a limited set of profile attributes
Tom Gottfried <tom@intevation.de>
parents:
319
diff
changeset
|
181 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
|
182 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
183 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
184 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
|
185 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2'); |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
186 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
187 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
|
188 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
189 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
190 WHERE username = 'test_user_at'), 'test5') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
191 WHERE username = 'test2' |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
192 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
193 'Existing user can be updated'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
194 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
195 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
196 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
197 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
|
198 = ('waterway_user', 'test_new_name', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
199 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
200 WHERE username = 'test_user_at'), 'test6') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
201 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
|
202 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
203 '0A000', NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
204 '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
|
205 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
206 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
207 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
208 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
|
209 = ('invalid', 'test2', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
210 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
211 WHERE username = 'test_user_at'), 'test2') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
212 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
|
213 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
214 42704, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
215 '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
|
216 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
217 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
218 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
219 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
|
220 = ('waterway_user', NULL, 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
221 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
222 WHERE username = 'test_user_at'), 'test3') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
223 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
|
224 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
225 23502, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
226 'New username is mandatory'); |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
227 -- 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
|
228 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
229 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
230 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
231 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
|
232 = ('waterway_user', 'waterway_user', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
233 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
234 WHERE username = 'test_user_at'), 'test4') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
235 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
|
236 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
237 42710, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
238 '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
|
239 |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
240 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
241 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
242 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
|
243 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
244 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
245 WHERE username = 'test_user_at'), 'test4') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
246 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
|
247 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
248 23505, NULL, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
249 '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
|
250 |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
251 -- 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
|
252 SELECT throws_ok($$ |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
253 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
254 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
|
255 = ('waterway_user', 'test_user_at', 'secret', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
256 (SELECT map_extent FROM users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
257 WHERE username = 'test_user_at'), 'test4') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
258 WHERE username = 'test_user_at' |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
259 $$, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
260 '28P01', NULL, |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
261 'Non-compliant password is not accepted'); |
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
247
diff
changeset
|
262 |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
263 -- 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
|
264 RESET SESSION AUTHORIZATION; |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
265 |
410
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
266 UPDATE old_pw_hash SET rolpassword = ( |
3f803d64a6ee
Do not rely on session_user for authorization
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
267 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
|
268 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
269 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
270 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
|
271 = ('waterway_user', 'test_user_at', NULL, 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
272 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
273 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
274 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
|
275 SELECT set_eq($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
276 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
|
277 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
278 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
279 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
|
280 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
281 '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
|
282 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
283 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
284 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
|
285 = ('waterway_user', 'test_user_at', '', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
286 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
287 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
288 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
|
289 SELECT set_eq($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
290 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
|
291 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
292 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
293 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
|
294 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
295 '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
|
296 |
307
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
297 UPDATE users.list_users |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
298 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
|
299 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT', |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
300 (SELECT map_extent FROM internal.user_profiles |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
301 WHERE username = 'test_user_at'), 'xxx') |
750a9c9cd965
Use SQL UPDATE to update users
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
302 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
|
303 SELECT set_ne($$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
304 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
|
305 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
306 $$ |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
307 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
|
308 $$, |
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
309 '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
|
310 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
311 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
|
312 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
313 -- |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
314 -- Role deletion |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
315 -- |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
316 -- 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
|
317 -- 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
|
318 SELECT lives_ok($$ |
343
5b03f420957d
Use INSTEAD OF trigger for user creation
Tom Gottfried <tom@intevation.de>
parents:
342
diff
changeset
|
319 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
|
320 '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
|
321 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
|
322 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
323 'Existing user can be deleted'); |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
324 |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
325 SELECT throws_ok($$ |
342
c6bd6ed18942
Use INSTEAD OF trigger for user deletion
Tom Gottfried <tom@intevation.de>
parents:
334
diff
changeset
|
326 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
|
327 $$, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
328 55006, NULL, |
4859aa6c96be
Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents:
225
diff
changeset
|
329 'Current user cannot be deleted'); |