Mercurial > gemma

comparison schema/gemma.sql @ 478:3af7ca761f6a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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