Mercurial > gemma

annotate schema/manage_users_tests.sql @ 261:ab9859981ee3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
If a user got renamed kick her/him from the connection pool.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 27 Jul 2018 14:55:47 +0200
parents 946baea3d280
children 92470caf81fd
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 --
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- 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
3 --
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 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
6
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
7 SET SESSION AUTHORIZATION test_admin_at;
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
8
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
9 SELECT throws_ok($$
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
10 SELECT sys_admin.create_user(
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
11 'waterway_user', 'test0', 'secret', 'AT', NULL, 'test0')
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
12 $$,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
13 42501, NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
14 'Less privileged user cannot call function in schema sys_admin');
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
15
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
16 SET SESSION AUTHORIZATION test_sys_admin1;
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
18 --
247
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
19 -- Role listing
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
20 --
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
21 SELECT isnt_empty($$
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
22 SELECT * FROM sys_admin.list_users
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
23 $$,
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
24 'List of users can be queried');
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
25
946baea3d280 Add view to list user profiles with role
Tom Gottfried <tom@intevation.de>
parents: 234
diff changeset
26 --
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
27 -- Role creation
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
28 --
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 SELECT lives_ok($$
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
30 SELECT sys_admin.create_user(
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 'waterway_user', 'test1', 'secret', 'AT', NULL, 'test1')
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 'New waterway user can be added');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 SELECT throws_ok($$
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
36 SELECT sys_admin.create_user(
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 'invalid', 'test2', 'secret', 'AT', NULL, 'test2')
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 42704, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 '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
41
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 SELECT throws_ok($$
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
43 SELECT sys_admin.create_user(
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 'waterway_user', NULL, 'secret', 'AT', NULL, 'test3')
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 $$,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 23502, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 'username is mandatory');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 -- 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
49
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 SELECT throws_ok($$
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 185
diff changeset
51 SELECT sys_admin.create_user(
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 'waterway_user', 'waterway_user', 'secret', 'AT', NULL, 'test4')
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 $$,
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
54 42710, NULL,
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
55 '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
56
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
57 SELECT throws_ok($$
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
58 SELECT sys_admin.create_user(
224
57dfab80973c By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents: 207
diff changeset
59 'waterway_user', 'test_user_at', 'secret', 'AT', NULL, 'test4')
207
88d21c29cf04 Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents: 196
diff changeset
60 $$,
185
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 23505, NULL,
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 'No duplicate user name is allowed');
a9d9c2b1d08c Add database function to create role and user profile
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63
225
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
64 --
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
65 -- Role update
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
66 --
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
67 SELECT lives_ok($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
68 SELECT sys_admin.create_user(
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
69 'waterway_user', 'test2', 'secret', 'AT', NULL, 'test2');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
70 SELECT sys_admin.update_user('test2',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
71 'waterway_user', 'test2_new', 'new_secret', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
72 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
73 WHERE username = 'test_user_at'), 'test5')
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
74 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
75 'Existing user can be updated');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
76
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
77 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
78 SELECT sys_admin.update_user('test_non_existent',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
79 'waterway_user', 'test_non_existent', '', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
80 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
81 WHERE username = 'test_user_at'), 'test5')
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
82 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
83 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
84 'Non-existent user cannot be updated');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
85
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
86 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
87 SELECT sys_admin.update_user(CAST(current_user AS varchar),
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
88 'waterway_user', 'test_new_name', 'secret', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
89 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
90 WHERE username = 'test_user_at'), 'test6')
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
91 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
92 '0A000', NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
93 '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
94
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
95 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
96 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
97 'invalid', 'test2', 'secret', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
98 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
99 WHERE username = 'test_user_at'), 'test2')
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
100 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
101 42704, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
102 '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
103
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
104 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
105 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
106 'waterway_user', NULL, 'secret', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
107 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
108 WHERE username = 'test_user_at'), 'test3')
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
109 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
110 23502, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
111 'New username is mandatory');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
112 -- 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
113
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
114 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
115 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
116 'waterway_user', 'waterway_user', 'secret', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
117 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
118 WHERE username = 'test_user_at'), 'test4')
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
119 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
120 42710, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
121 '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
122
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
123 SELECT throws_ok($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
124 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
125 'waterway_user', 'test_user_ro', 'secret', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
126 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
127 WHERE username = 'test_user_at'), 'test4')
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
128 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
129 23505, NULL,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
130 '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
131
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
132 -- 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
133 RESET SESSION AUTHORIZATION;
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
134
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
135 CREATE TEMP TABLE old_pw_hash AS
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
136 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
137
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
138 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
139 'waterway_user', 'test_user_at', NULL, 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
140 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
141 WHERE username = 'test_user_at'), 'xxx');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
142 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
143 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
144 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
145 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
146 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
147 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
148 '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
149
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
150 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
151 'waterway_user', 'test_user_at', '', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
152 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
153 WHERE username = 'test_user_at'), 'xxx');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
154 SELECT set_eq($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
155 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
156 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
157 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
158 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
159 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
160 '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
161
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
162 SELECT sys_admin.update_user('test_user_at',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
163 'waterway_user', 'test_user_at', 'new_pw', 'AT',
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
164 (SELECT map_extent FROM users.user_profiles
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
165 WHERE username = 'test_user_at'), 'xxx');
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
166 SELECT set_ne($$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
167 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
168 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
169 $$
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
170 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
171 $$,
8b9cae6d3a21 Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents: 224
diff changeset
172 '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
173
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
174 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
175
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
176 --
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
177 -- Role deletion
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
178 --
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
179 -- 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
180 -- 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
181 SELECT lives_ok($$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
182 SELECT sys_admin.create_user(
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
183 'waterway_user', 'test3', 'secret', 'AT', NULL, 'test3');
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
184 SELECT sys_admin.delete_user('test3')
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
185 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
186 'Existing user can be deleted');
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
187
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
188 SELECT throws_ok($$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
189 SELECT sys_admin.delete_user('test_non_existent')
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
190 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
191 42704, NULL,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
192 'Non-existent user cannot be deleted');
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
193
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
194 SELECT throws_ok($$
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
195 SELECT sys_admin.delete_user(CAST(current_user AS varchar))
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
196 $$,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
197 55006, NULL,
4859aa6c96be Add database function to delete role and user profile
Tom Gottfried <tom@intevation.de>
parents: 225
diff changeset
198 'Current user cannot be deleted');