Mercurial > gemma

annotate schema/manage_users_tests.sql @ 2624:9dbaf69c7a66

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve geoserver config to better calculate bounding boxes * Disable the use of estimated extents for the postgis storage configuration for geoserver, which is set via the gemma middleware. This way we are able to get better bounding boxes for many layers where the postgis function `ST_EstimatedExtent()` would be far off.
author Bernhard Reiter <bernhard@intevation.de>
date Wed, 13 Mar 2019 16:18:39 +0100
parents 931b15be6d7f
children 93fa55bce126
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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');