gemma: schema/manage_users_tests.sql annotate

annotate schema/manage_users_tests.sql @ 3678:8f58851927c0

client: make layer factory only return new layer config for individual maps instead of each time it is invoked. The purpose of the factory was to support multiple maps with individual layers. But returning a new config each time it is invoked leads to bugs that rely on the layer's state. Now this factory reuses the same objects it created before, per map.

author	Markus Kottlaender <markus@intevation.de>
date	Mon, 17 Jun 2019 17:31:35 +0200
parents	93fa55bce126
children	966d7eb6d99b

rev	line source
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	3
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	6
2912 93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	7 -- Copyright (C) 2018, 2019 by via donau
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	10
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	11 -- Author(s):
1301 2304778c4432 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1298 diff changeset	12 -- * Tom Gottfried <tom@intevation.de>
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 478 diff changeset	13
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	14 --
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	15 -- pgTAP test script for user management functions
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	16 --
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	17
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	18 SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	19
2912 93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	20 SET SESSION AUTHORIZATION test_user_at;
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	21 --
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	22 -- Utility functions
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	23 --
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	24 SELECT results_eq($$
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	25 SELECT ST_SRID(users.current_user_area_utm())
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	26 $$,
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	27 $$
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	28 SELECT best_utm(area)
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	29 FROM users.responsibility_areas
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	30 WHERE country = users.current_user_country()
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	31 $$,
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	32 'Geometry has SRID corresponding to best_utm()');
93fa55bce126 Add utility function to get users area of responsibility Tom Gottfried <tom@intevation.de> parents: 1904 diff changeset	33
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	34 --
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	35 -- Role listing
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	36 --
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	37 SELECT results_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	38 SELECT username FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	39 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	40 $$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	41 SELECT CAST(current_user AS varchar)
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	42 $$,
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	43 'User should only see his own profile');
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	44
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	45 SET SESSION AUTHORIZATION test_admin_at;
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	46 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	47 SELECT DISTINCT country FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	48 $$,
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	49 ARRAY['AT'],
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	50 'Waterway admin should only see profiles of his country');
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	51
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	52 SET SESSION AUTHORIZATION test_sys_admin1;
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	53 SELECT set_eq($$
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	54 SELECT count(*) FROM users.list_users
13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	55 $$,
1904 931b15be6d7f Complement authorisation tests for import management Tom Gottfried <tom@intevation.de> parents: 1873 diff changeset	56 ARRAY[6],
263 13ad969a9138 Enable listing of users for all roles with appropriate filters Tom Gottfried <tom@intevation.de> parents: 262 diff changeset	57 'System admin can see all users');
247 946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	58
946baea3d280 Add view to list user profiles with role Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	59 --
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	60 -- Role creation
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	61 --
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	62 SELECT lives_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	63 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	64 'waterway_user', 'test1', 'secret1$', 'AT', NULL, 'test1')
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	65 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	66 'New waterway user can be added');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	67
463 5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	68 SELECT results_eq($$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	69 SELECT pg_has_role('metamorph', 'test1', 'MEMBER')
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	70 $$,
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	71 $$
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	72 SELECT true
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	73 $$,
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	74 'New role is GRANTed to metamorph after creation');
5611cf72cc92 Add metamorphic database role and user e.g. for GeoServer Tom Gottfried <tom@intevation.de> parents: 410 diff changeset	75
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	76 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	77 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	78 'invalid', 'test2', 'secret1$', 'AT', NULL, 'test2')
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	79 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	80 42704, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	81 'Valid role name has to be provided');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	82
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	83 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	84 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	85 'waterway_user', NULL, 'secret1$', 'AT', NULL, 'test3')
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	86 $$,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	87 23502, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	88 'username is mandatory');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	89 -- Though other arguments are mandatory, too, there are no explicit tests
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	90
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	91 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	92 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	93 'waterway_user', 'waterway_user', 'secret1$', 'AT', NULL, 'test4')
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	94 $$,
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	95 42710, NULL,
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	96 'Reserved role names cannot be used as username');
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	97
88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	98 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	99 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	100 'waterway_user', 'test_user_at', 'secret1$', 'AT', NULL, 'test4')
207 88d21c29cf04 Care for the fact that role attributes are not inherited Tom Gottfried <tom@intevation.de> parents: 196 diff changeset	101 $$,
185 a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	102 23505, NULL,
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	103 'No duplicate user name is allowed');
a9d9c2b1d08c Add database function to create role and user profile Tom Gottfried <tom@intevation.de> parents: diff changeset	104
361 f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	105 SELECT throws_ok($$
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	106 INSERT INTO users.list_users VALUES (
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	107 'waterway_user',
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	108 'Test Nutzer AT, Test User RO, Täst Nützer ÄT, Täst Üser RÖ',
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	109 'secret1$', 'AT', NULL, 'test4')
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	110 $$,
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	111 23514, NULL,
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	112 'User name length is restricted to 63 bytes');
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 343 diff changeset	113
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	114 -- Test password policy
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	115 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	116 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	117 'waterway_user', 'test2', 'ecret1$', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	118 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	119 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	120 'Password with less than 8 characters is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	121
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	122 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	123 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	124 'waterway_user', 'test2', 'secret12', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	125 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	126 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	127 'Password without non-alphanumeric character is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	128
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	129 SELECT throws_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	130 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	131 'waterway_user', 'test2', 'secret!$', 'AT', NULL, 'test2')
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	132 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	133 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	134 'Password without digit is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	135
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	136 --
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	137 -- Role update
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	138 --
327 363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	139
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	140 SET SESSION AUTHORIZATION test_user_at;
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	141
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	142 SELECT results_eq($$
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	143 UPDATE users.list_users
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	144 SET (pw, map_extent, email_address)
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	145 = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test')
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	146 RETURNING username
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	147 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	148 $$
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	149 SELECT CAST('test_user_at' AS varchar)
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	150 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	151 'Waterway user can update own password, map extent and email address');
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	152
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	153 SELECT throws_ok($$
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	154 UPDATE users.list_users
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	155 SET username = 'test_rename', rolname = 'test'
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	156 $$,
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	157 42501, NULL,
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	158 'Waterway user cannot update arbitrary user attributes');
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	159
334 df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	160 SET SESSION AUTHORIZATION test_admin_at;
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	161
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	162 SELECT results_eq($$
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	163 UPDATE users.list_users
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	164 SET (pw, map_extent, email_address)
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	165 = ('user_at2!', 'BOX(0 0,1 1)', 'user_at_test')
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	166 WHERE country = users.current_user_country()
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	167 AND username <> current_user
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	168 RETURNING *
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	169 $$,
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	170 $$
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	171 SELECT '' WHERE false -- Empty result set
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	172 $$,
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	173 'Waterway admin cannot update attributes of other users in country');
df1fc589ad9d Prevent Waterway Admins from updating users from their country Tom Gottfried <tom@intevation.de> parents: 327 diff changeset	174
410 3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	175 -- The above test will pass even if the password is actually updated in case
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	176 -- a trigger returns NULL after ALTER ROLE ... PASSWORD ... has been executed.
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	177 RESET SESSION AUTHORIZATION;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	178 CREATE TEMP TABLE old_pw_hash AS
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	179 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at';
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	180 SET SESSION AUTHORIZATION test_admin_at;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	181 UPDATE users.list_users
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	182 SET pw = 'test_user_at2!'
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	183 WHERE username = 'test_user_at';
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	184 RESET SESSION AUTHORIZATION;
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	185 SELECT set_eq($$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	186 SELECT rolpassword FROM old_pw_hash
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	187 $$,
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	188 $$
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	189 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	190 $$,
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	191 'Waterway admin cannot update password of other users in country');
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	192
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	193
327 363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	194 SET SESSION AUTHORIZATION test_sys_admin1;
363983d5c567 Allow Waterway User to update a limited set of profile attributes Tom Gottfried <tom@intevation.de> parents: 319 diff changeset	195
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	196 SELECT lives_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	197 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	198 'waterway_user', 'test2', 'secret1$', 'AT', NULL, 'test2');
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	199 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	200 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	201 = ('waterway_user', 'test2_new', 'new_secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	202 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	203 WHERE username = 'test_user_at'), 'test5')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	204 WHERE username = 'test2'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	205 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	206 'Existing user can be updated');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	207
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	208 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	209 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	210 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	211 = ('waterway_user', 'test_new_name', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	212 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	213 WHERE username = 'test_user_at'), 'test6')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	214 WHERE username = CAST(current_user AS varchar)
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	215 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	216 '0A000', NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	217 'Name of current user cannot be altered');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	218
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	219 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	220 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	221 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	222 = ('invalid', 'test2', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	223 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	224 WHERE username = 'test_user_at'), 'test2')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	225 WHERE username = 'test_user_at'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	226 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	227 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	228 'Valid role name has to be provided');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	229
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	230 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	231 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	232 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	233 = ('waterway_user', NULL, 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	234 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	235 WHERE username = 'test_user_at'), 'test3')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	236 WHERE username = 'test_user_at'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	237 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	238 23502, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	239 'New username is mandatory');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	240 -- Though other arguments are mandatory, too, there are no explicit tests
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	241
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	242 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	243 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	244 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	245 = ('waterway_user', 'waterway_user', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	246 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	247 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	248 WHERE username = 'test_user_at'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	249 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	250 42710, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	251 'Reserved role names cannot be used as username');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	252
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	253 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	254 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	255 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	256 = ('waterway_user', 'test_user_ro', 'secret1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	257 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	258 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	259 WHERE username = 'test_user_at'
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	260 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	261 23505, NULL,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	262 'No duplicate user name is allowed');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	263
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	264 -- Test password policy (only one rule to ensure it's also used on update)
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	265 SELECT throws_ok($$
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	266 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	267 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	268 = ('waterway_user', 'test_user_at', 'secret', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	269 (SELECT map_extent FROM users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	270 WHERE username = 'test_user_at'), 'test4')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	271 WHERE username = 'test_user_at'
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	272 $$,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	273 '28P01', NULL,
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	274 'Non-compliant password is not accepted');
92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	275
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	276 -- To compare passwords, we need to run the following tests as superuser
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	277 RESET SESSION AUTHORIZATION;
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	278
410 3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	279 UPDATE old_pw_hash SET rolpassword = (
3f803d64a6ee Do not rely on session_user for authorization Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	280 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at');
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	281
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	282 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	283 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	284 = ('waterway_user', 'test_user_at', NULL, 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	285 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	286 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	287 WHERE username = 'test_user_at';
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	288 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	289 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	290 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	291 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	292 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	293 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	294 'Giving NULL password does not change password');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	295
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	296 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	297 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	298 = ('waterway_user', 'test_user_at', '', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	299 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	300 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	301 WHERE username = 'test_user_at';
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	302 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	303 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	304 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	305 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	306 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	307 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	308 'Giving empty string as password does not change password');
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	309
307 750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	310 UPDATE users.list_users
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	311 SET (rolname, username, pw, country, map_extent, email_address)
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	312 = ('waterway_user', 'test_user_at', 'new_pw1$', 'AT',
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	313 (SELECT map_extent FROM internal.user_profiles
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	314 WHERE username = 'test_user_at'), 'xxx')
750a9c9cd965 Use SQL UPDATE to update users Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	315 WHERE username = 'test_user_at';
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	316 SELECT set_ne($$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	317 SELECT rolpassword FROM old_pw_hash
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	318 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	319 $$
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	320 SELECT rolpassword FROM pg_authid WHERE rolname = 'test_user_at'
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	321 $$,
8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 224 diff changeset	322 'Giving a non-empty password string changes password');
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	323
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	324 SET SESSION AUTHORIZATION test_sys_admin1;
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	325
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	326 --
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	327 -- Role deletion
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	328 --
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	329 -- Note: backend termination is not tested in the following.
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	330 -- See also comments in function definition.
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	331 SELECT lives_ok($$
343 5b03f420957d Use INSTEAD OF trigger for user creation Tom Gottfried <tom@intevation.de> parents: 342 diff changeset	332 INSERT INTO users.list_users VALUES (
262 92470caf81fd Add database function to check password against policy Tom Gottfried <tom@intevation.de> parents: 247 diff changeset	333 'waterway_user', 'test3', 'secret1$', 'AT', NULL, 'test3');
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	334 DELETE FROM users.list_users WHERE username = 'test3'
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	335 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	336 'Existing user can be deleted');
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	337
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	338 SELECT throws_ok($$
342 c6bd6ed18942 Use INSTEAD OF trigger for user deletion Tom Gottfried <tom@intevation.de> parents: 334 diff changeset	339 DELETE FROM users.list_users WHERE username = CAST(current_user AS varchar)
232 4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	340 $$,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	341 55006, NULL,
4859aa6c96be Add database function to delete role and user profile Tom Gottfried <tom@intevation.de> parents: 225 diff changeset	342 'Current user cannot be deleted');

Mercurial > gemma

annotate schema/manage_users_tests.sql @ 3678:8f58851927c0