summaryrefslogtreecommitdiffstats
path: root/rust/migrations
diff options
context:
space:
mode:
Diffstat (limited to 'rust/migrations')
-rw-r--r--rust/migrations/2018-05-12-001226_init/down.sql1
-rw-r--r--rust/migrations/2018-05-12-001226_init/up.sql35
2 files changed, 28 insertions, 8 deletions
diff --git a/rust/migrations/2018-05-12-001226_init/down.sql b/rust/migrations/2018-05-12-001226_init/down.sql
index d156370e..b2666083 100644
--- a/rust/migrations/2018-05-12-001226_init/down.sql
+++ b/rust/migrations/2018-05-12-001226_init/down.sql
@@ -42,6 +42,7 @@ DROP TABLE IF EXISTS creator_ident CASCADE;
DROP TABLE IF EXISTS creator_edit CASCADE;
DROP TABLE IF EXISTS abstracts CASCADE;
+DROP TABLE IF EXISTS auth_oidc CASCADE;
DROP TABLE IF EXISTS editor CASCADE;
DROP TABLE IF EXISTS editgroup CASCADE;
DROP TABLE IF EXISTS changelog CASCADE;
diff --git a/rust/migrations/2018-05-12-001226_init/up.sql b/rust/migrations/2018-05-12-001226_init/up.sql
index 22f5cca6..ddaa60b3 100644
--- a/rust/migrations/2018-05-12-001226_init/up.sql
+++ b/rust/migrations/2018-05-12-001226_init/up.sql
@@ -16,13 +16,32 @@ CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE editor (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
- username TEXT NOT NULL UNIQUE,
+ username TEXT NOT NULL CHECK (username ~* '^[A-Za-z][A-Za-z0-9._-]{2,24}$'), -- UNIQ below
+ is_superuser BOOLEAN NOT NULL DEFAULT false,
is_admin BOOLEAN NOT NULL DEFAULT false,
+ is_bot BOOLEAN NOT NULL DEFAULT false,
+ is_active BOOLEAN NOT NULL DEFAULT true,
registered TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
+ auth_epoch TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
+ wrangler_id UUID REFERENCES editor(id),
active_editgroup_id UUID -- REFERENCES( editgroup(id) via ALTER below
);
+-- case-insensitive UNIQ index on username
+CREATE UNIQUE INDEX editor_username_uniq_idx on editor(lower(username));
CREATE INDEX active_editgroup_idx ON editor(active_editgroup_id);
+CREATE INDEX editor_username_idx ON editor(username);
+
+CREATE TABLE auth_oidc (
+ id BIGSERIAL PRIMARY KEY,
+ created TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
+ editor_id UUID REFERENCES editor(id) NOT NULL,
+ provider TEXT NOT NULL,
+ oidc_iss TEXT NOT NULL,
+ oidc_sub TEXT NOT NULL,
+ UNIQUE (editor_id, provider),
+ UNIQUE (oidc_iss, oidc_sub)
+);
CREATE TABLE editgroup (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
@@ -452,10 +471,13 @@ CREATE INDEX webcapture_rev_release_target_release_idx ON webcapture_rev_release
BEGIN;
-INSERT INTO editor (id, username, is_admin) VALUES
- ('00000000-0000-0000-AAAA-000000000001', 'admin', true), -- aaaaaaaaaaaabkvkaaaaaaaaae
- ('00000000-0000-0000-AAAA-000000000002', 'demo-user', true), -- aaaaaaaaaaaabkvkaaaaaaaaai
- ('00000000-0000-0000-AAAA-000000000003', 'claire', false); -- aaaaaaaaaaaabkvkaaaaaaaaam
+INSERT INTO editor (id, username, is_superuser, is_admin, is_bot, auth_epoch) VALUES
+ ('00000000-0000-0000-AAAA-000000000001', 'root', true, true, false, '1970-01-01T01:01:01Z'), -- aaaaaaaaaaaabkvkaaaaaaaaae
+ ('00000000-0000-0000-AAAA-000000000002', 'admin', true, true, false, '1970-01-01T01:01:01Z'), -- aaaaaaaaaaaabkvkaaaaaaaaai
+ ('00000000-0000-0000-AAAA-000000000003', 'demo-user', false, true, false, '1970-01-01T01:01:01Z'), -- aaaaaaaaaaaabkvkaaaaaaaaam
+ ('00000000-0000-0000-AAAA-000000000004', 'claire', false, false, false, default), -- aaaaaaaaaaaabkvkaaaaaaaaaq
+ ('00000000-0000-0000-AAAA-000000000005', 'webface-bot', true, true, true, '1970-01-01T01:01:01Z'), -- aaaaaaaaaaaabkvkaaaaaaaaau
+ ('00000000-0000-0000-AAAA-000000000006', 'bnewbold', false, true, false, '1970-01-01T01:01:01Z'); -- aaaaaaaaaaaabkvkaaaaaaaaay
INSERT INTO editgroup (id, editor_id, description) VALUES
('00000000-0000-0000-BBBB-000000000001', '00000000-0000-0000-AAAA-000000000001', 'first edit ever!'), -- aaaaaaaaaaaabo53aaaaaaaaae
@@ -465,9 +487,6 @@ INSERT INTO editgroup (id, editor_id, description) VALUES
('00000000-0000-0000-BBBB-000000000005', '00000000-0000-0000-AAAA-000000000001', 'journal edit'), -- aaaaaaaaaaaabo53aaaaaaaaau
('00000000-0000-0000-BBBB-000000000006', '00000000-0000-0000-AAAA-000000000001', 'another journal edit'); -- aaaaaaaaaaaabo53aaaaaaaaay
-INSERT INTO editor (id, username, is_admin, active_editgroup_id) VALUES
- ('00000000-0000-0000-AAAA-000000000004', 'bnewbold', true, '00000000-0000-0000-BBBB-000000000004');
-
INSERT INTO changelog (editgroup_id) VALUES
('00000000-0000-0000-BBBB-000000000001'),
('00000000-0000-0000-BBBB-000000000002'),