Mercurial > gemma
annotate schema/tap_tests_data.sql @ 319:ac760b0f22a9
Add special role for password reset
As password reset is exposed without requiring a login, let this role
have privileges limited to reseting passwords, and only reseting passwords.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 02 Aug 2018 13:06:39 +0200 |
parents | 92470caf81fd |
children | 5b03f420957d |
rev | line source |
---|---|
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
1 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
2 -- Test data used in *_tests.sql scripts |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
3 -- |
109
0e486e6ee60d
Set search path in test data script for standalone use.
Tom Gottfried <tom@intevation.de>
parents:
108
diff
changeset
|
4 |
177
4e2451d561b1
Make schema for templates more realistic
Tom Gottfried <tom@intevation.de>
parents:
136
diff
changeset
|
5 INSERT INTO countries VALUES ('AT'), ('RO'); |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
7 INSERT INTO users.responsibility_areas VALUES |
211
e7826710d9c4
Make responsibility area mandatory
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
8 ('AT', ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326)), |
e7826710d9c4
Make responsibility area mandatory
Tom Gottfried <tom@intevation.de>
parents:
207
diff
changeset
|
9 ('RO', ST_geomfromtext('MULTIPOLYGON(((1 0, 1 1, 2 1, 2 0, 1 0)))', 4326)); |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
195
diff
changeset
|
11 SELECT sys_admin.create_user( |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
12 'waterway_user', 'test_user_at', 'user_at1$', 'AT', NULL, 'xxx'); |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
195
diff
changeset
|
13 SELECT sys_admin.create_user( |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
14 'waterway_user', 'test_user_ro', 'user_ro1$', 'RO', NULL, 'xxy'); |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
195
diff
changeset
|
15 SELECT sys_admin.create_user( |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
16 'waterway_admin', 'test_admin_at', 'admin_at1$', 'AT', NULL, 'yyy'); |
207
88d21c29cf04
Care for the fact that role attributes are not inherited
Tom Gottfried <tom@intevation.de>
parents:
195
diff
changeset
|
17 SELECT sys_admin.create_user( |
262
92470caf81fd
Add database function to check password against policy
Tom Gottfried <tom@intevation.de>
parents:
224
diff
changeset
|
18 'sys_admin', 'test_sys_admin1', 'sys_admin1$', 'AT', NULL, 'zzz'); |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
20 CREATE ROLE test_pw_reset IN ROLE pw_reset LOGIN PASSWORD 'ppp'; |
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
262
diff
changeset
|
21 |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 INSERT INTO limiting_factors VALUES ('depth'), ('width'); |
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
24 INSERT INTO waterway.gauges ( |
182 | 25 location, function_code, objname, geom, zero_point, source_organization) |
26 VALUES ( | |
27 ('AT', 'XXX', '00001', '00000', 1)::isrs, | |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 'xxx', |
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 'testgauge', |
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 ST_geomfromtext('POINT(0 0)', 4326), |
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 0, |
182 | 32 'testorganization' |
33 ); | |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
35 INSERT INTO waterway.bottlenecks ( |
182 | 36 bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, |
37 revisiting_time, limiting, source_organization, staging_done) | |
38 VALUES ( | |
39 'testbottleneck1', | |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 ('AT', 'XXX', '00001', '00000', 1)::isrs, |
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, |
182 | 42 ('AT', 'XXX', '00001', '00000', 2)::isrs), |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), |
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 'AT', 'AT', 'AT', |
182 | 45 1, 'depth', 'testorganization', false |
46 ), ( | |
47 'testbottleneck2', | |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 ('AT', 'XXX', '00001', '00000', 1)::isrs, |
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, |
182 | 50 ('AT', 'XXX', '00001', '00000', 2)::isrs), |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), |
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 'AT', 'AT', 'AT', |
182 | 53 1, 'depth', 'testorganization', true |
54 ); | |
108
f6aba3ee3a75
Put test data in own file for easier debugging.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
56 INSERT INTO users.templates (template_name, template_data) |
182 | 57 VALUES ('AT', '\x'), ('RO', '\x'); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
182
diff
changeset
|
58 INSERT INTO users.user_templates |
224
57dfab80973c
By convention, prefix all test users with 'test'
Tom Gottfried <tom@intevation.de>
parents:
211
diff
changeset
|
59 VALUES ('test_user_at', 'AT'), ('test_user_ro', 'RO'); |