Merge branch 'bnewbold-crude-auth'

Fixed a conflict in:
  python/fatcat_export.py

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'),