Mercurial > gemma
comparison schema/gemma.sql @ 478:3af7ca761f6a
Purge password reset role
The risk of SQL-injections and thus privilege escalation
via the metamorphic user was estimated not high enough to
justify the extra role. Thus, bring database back in line
with rev. ffdb507d5b42 and re-enable password reset.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 23 Aug 2018 16:41:44 +0200 |
parents | 62ffb6c8a42e |
children | f3452ce5c056 |
comparison
equal
deleted
inserted
replaced
477:00b52d653039 | 478:3af7ca761f6a |
---|---|
54 CREATE TABLE system_config ( | 54 CREATE TABLE system_config ( |
55 config_key varchar PRIMARY KEY, | 55 config_key varchar PRIMARY KEY, |
56 config_val varchar | 56 config_val varchar |
57 ) | 57 ) |
58 | 58 |
59 CREATE TABLE password_reset_requests ( | |
60 hash varchar(32) PRIMARY KEY, | |
61 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
62 username varchar NOT NULL | |
63 REFERENCES internal.user_profiles(username) | |
64 ON DELETE CASCADE ON UPDATE CASCADE | |
65 ) | |
66 | |
59 CREATE TABLE external_services ( | 67 CREATE TABLE external_services ( |
60 local_name varchar PRIMARY KEY, | 68 local_name varchar PRIMARY KEY, |
61 remote_url varchar NOT NULL, | 69 remote_url varchar NOT NULL, |
62 is_wfs boolean NOT NULL DEFAULT TRUE | 70 is_wfs boolean NOT NULL DEFAULT TRUE |
63 ) | 71 ) |
178 PRIMARY KEY (username, template_name) | 186 PRIMARY KEY (username, template_name) |
179 ) | 187 ) |
180 ; | 188 ; |
181 ALTER TABLE internal.user_profiles ADD | 189 ALTER TABLE internal.user_profiles ADD |
182 country char(2) NOT NULL REFERENCES users.responsibility_areas; | 190 country char(2) NOT NULL REFERENCES users.responsibility_areas; |
183 | |
184 | |
185 -- Namespace intended to be the only one that pw_reset can access | |
186 CREATE SCHEMA pw_reset | |
187 CREATE TABLE password_reset_requests ( | |
188 hash varchar(32) PRIMARY KEY, | |
189 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, | |
190 username varchar NOT NULL | |
191 REFERENCES internal.user_profiles(username) | |
192 ON DELETE CASCADE ON UPDATE CASCADE | |
193 ) | |
194 ; | |
195 | 191 |
196 | 192 |
197 -- Namespace for waterway data that can change in a running system | 193 -- Namespace for waterway data that can change in a running system |
198 CREATE SCHEMA waterway | 194 CREATE SCHEMA waterway |
199 | 195 |