diff options
Diffstat (limited to 'rust/migrations/2018-05-12-001226_init')
| -rw-r--r-- | rust/migrations/2018-05-12-001226_init/down.sql | 1 | ||||
| -rw-r--r-- | rust/migrations/2018-05-12-001226_init/up.sql | 35 |
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'), |