From 2c4eb2d311919ad9fb70738199ecf99bf20c9fce Mon Sep 17 00:00:00 2001 From: benj Date: Thu, 1 Dec 2022 10:30:34 -0800 Subject: - basic functionality with psql and sqlite - cli helper tool --- .../pg/migrations/20221116062550_bootstrap.sql | 49 ++++++++++++++++++++++ crates/secd/store/pg/sql/find_email_validation.sql | 17 ++++++++ crates/secd/store/pg/sql/find_identity.sql | 9 ++++ crates/secd/store/pg/sql/find_identity_by_code.sql | 11 +++++ crates/secd/store/pg/sql/read_email_raw_id.sql | 1 + crates/secd/store/pg/sql/read_identity.sql | 0 crates/secd/store/pg/sql/read_identity_raw_id.sql | 2 + crates/secd/store/pg/sql/read_session.sql | 8 ++++ crates/secd/store/pg/sql/write_email.sql | 11 +++++ .../secd/store/pg/sql/write_email_validation.sql | 27 ++++++++++++ crates/secd/store/pg/sql/write_identity.sql | 9 ++++ crates/secd/store/pg/sql/write_session.sql | 18 ++++++++ .../sqlite/migrations/20221125051738_bootstrap.sql | 45 ++++++++++++++++++++ .../store/sqlite/sql/find_email_validation.sql | 16 +++++++ crates/secd/store/sqlite/sql/find_identity.sql | 9 ++++ .../store/sqlite/sql/find_identity_by_code.sql | 11 +++++ crates/secd/store/sqlite/sql/read_email_raw_id.sql | 1 + crates/secd/store/sqlite/sql/read_identity.sql | 0 .../secd/store/sqlite/sql/read_identity_raw_id.sql | 2 + crates/secd/store/sqlite/sql/read_session.sql | 8 ++++ crates/secd/store/sqlite/sql/write_email.sql | 11 +++++ .../store/sqlite/sql/write_email_validation.sql | 27 ++++++++++++ crates/secd/store/sqlite/sql/write_identity.sql | 1 + crates/secd/store/sqlite/sql/write_session.sql | 18 ++++++++ 24 files changed, 311 insertions(+) create mode 100644 crates/secd/store/pg/migrations/20221116062550_bootstrap.sql create mode 100644 crates/secd/store/pg/sql/find_email_validation.sql create mode 100644 crates/secd/store/pg/sql/find_identity.sql create mode 100644 crates/secd/store/pg/sql/find_identity_by_code.sql create mode 100644 crates/secd/store/pg/sql/read_email_raw_id.sql create mode 100644 crates/secd/store/pg/sql/read_identity.sql create mode 100644 crates/secd/store/pg/sql/read_identity_raw_id.sql create mode 100644 crates/secd/store/pg/sql/read_session.sql create mode 100644 crates/secd/store/pg/sql/write_email.sql create mode 100644 crates/secd/store/pg/sql/write_email_validation.sql create mode 100644 crates/secd/store/pg/sql/write_identity.sql create mode 100644 crates/secd/store/pg/sql/write_session.sql create mode 100644 crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql create mode 100644 crates/secd/store/sqlite/sql/find_email_validation.sql create mode 100644 crates/secd/store/sqlite/sql/find_identity.sql create mode 100644 crates/secd/store/sqlite/sql/find_identity_by_code.sql create mode 100644 crates/secd/store/sqlite/sql/read_email_raw_id.sql create mode 100644 crates/secd/store/sqlite/sql/read_identity.sql create mode 100644 crates/secd/store/sqlite/sql/read_identity_raw_id.sql create mode 100644 crates/secd/store/sqlite/sql/read_session.sql create mode 100644 crates/secd/store/sqlite/sql/write_email.sql create mode 100644 crates/secd/store/sqlite/sql/write_email_validation.sql create mode 100644 crates/secd/store/sqlite/sql/write_identity.sql create mode 100644 crates/secd/store/sqlite/sql/write_session.sql (limited to 'crates/secd/store') diff --git a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql new file mode 100644 index 0000000..7a1bf07 --- /dev/null +++ b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql @@ -0,0 +1,49 @@ +create extension if not exists pgcrypto; +create extension if not exists citext; +create schema if not exists auth; + +create table if not exists auth.identity ( + identity_id bigserial primary key + , identity_public_id uuid + , data text + , created_at timestamptz not null + , unique(identity_public_id) +); + +create table if not exists auth.email ( + email_id bigserial primary key + , address text not null + , unique(address) +); + +create table if not exists auth.identity_email ( + identity_email_id bigserial primary key + , identity_id bigint not null references auth.identity(identity_id) + , email_id bigint not null references auth.email(email_id) + , created_at timestamptz not null + , deleted_at timestamptz +); + +create table if not exists auth.email_validation ( + email_validation_id bigserial primary key + , email_validation_public_id uuid not null + , identity_email_id integer not null references auth.identity_email(identity_email_id) + , attempts integer not null + , code text + , is_validated boolean not null default false + , created_at timestamptz not null + , expires_at timestamptz + , revoked_at timestamptz + , unique(email_validation_public_id) +); + +create table if not exists auth.session ( + session_id bigserial primary key + , identity_id bigint not null references auth.identity(identity_id) + , secret_hash bytea not null + , created_at timestamptz not null + , touched_at timestamptz not null + , expires_at timestamptz + , revoked_at timestamptz + , unique(secret_hash) +); diff --git a/crates/secd/store/pg/sql/find_email_validation.sql b/crates/secd/store/pg/sql/find_email_validation.sql new file mode 100644 index 0000000..d16d8e7 --- /dev/null +++ b/crates/secd/store/pg/sql/find_email_validation.sql @@ -0,0 +1,17 @@ +select + ev.email_validation_public_id + , i.identity_public_id + , e.address + , ev.attempts + , ev.code + , ev.is_validated + , ev.created_at + , ev.expires_at + , ev.revoked_at +from auth.email_validation ev +join auth.identity_email ie using (identity_email_id) +join auth.email e using (email_id) +join auth.identity i using (identity_id) +where (($1 is null) or (email_validation_public_id = $1)) +and (($2 is null) or (code = $2)); +-- diff --git a/crates/secd/store/pg/sql/find_identity.sql b/crates/secd/store/pg/sql/find_identity.sql new file mode 100644 index 0000000..3a86a83 --- /dev/null +++ b/crates/secd/store/pg/sql/find_identity.sql @@ -0,0 +1,9 @@ +select + identity_public_id, + data, + i.created_at +from auth.identity i +join auth.identity_email ie using (identity_id) +join auth.email e using (email_id) +where (($1 is null) or (i.identity_public_id = $1)) +and (($2 is null) or (e.address = $2)) diff --git a/crates/secd/store/pg/sql/find_identity_by_code.sql b/crates/secd/store/pg/sql/find_identity_by_code.sql new file mode 100644 index 0000000..9df6614 --- /dev/null +++ b/crates/secd/store/pg/sql/find_identity_by_code.sql @@ -0,0 +1,11 @@ +select identity_email_id +from auth.email_validation +where email_validation_public_id = $1::uuid +-- +select + identity_public_id + , data + , i.created_at +from auth.identity i +left join auth.identity_email ie using (identity_id) +where ie.identity_email_id = $1; diff --git a/crates/secd/store/pg/sql/read_email_raw_id.sql b/crates/secd/store/pg/sql/read_email_raw_id.sql new file mode 100644 index 0000000..f62331c --- /dev/null +++ b/crates/secd/store/pg/sql/read_email_raw_id.sql @@ -0,0 +1 @@ +select email_id from auth.email where address = $1 diff --git a/crates/secd/store/pg/sql/read_identity.sql b/crates/secd/store/pg/sql/read_identity.sql new file mode 100644 index 0000000..e69de29 diff --git a/crates/secd/store/pg/sql/read_identity_raw_id.sql b/crates/secd/store/pg/sql/read_identity_raw_id.sql new file mode 100644 index 0000000..d550cc0 --- /dev/null +++ b/crates/secd/store/pg/sql/read_identity_raw_id.sql @@ -0,0 +1,2 @@ +select identity_id from auth.identity where identity_public_id = $1; +-- diff --git a/crates/secd/store/pg/sql/read_session.sql b/crates/secd/store/pg/sql/read_session.sql new file mode 100644 index 0000000..febc1ab --- /dev/null +++ b/crates/secd/store/pg/sql/read_session.sql @@ -0,0 +1,8 @@ +select + i.identity_public_id + , s.created_at + , s.expires_at + , s.revoked_at +from auth.session s +join auth.identity i using (identity_id) +where secret_hash = $1; diff --git a/crates/secd/store/pg/sql/write_email.sql b/crates/secd/store/pg/sql/write_email.sql new file mode 100644 index 0000000..75fc494 --- /dev/null +++ b/crates/secd/store/pg/sql/write_email.sql @@ -0,0 +1,11 @@ +insert into auth.email ( + address +) values ( + $1 +) on conflict (address) do nothing +returning email_id; +-- +select email_id from auth.email where address = $1; +-- +insert into auth.identity_email (identity_id, email_id, created_at) values ($1, $2, $3); +-- diff --git a/crates/secd/store/pg/sql/write_email_validation.sql b/crates/secd/store/pg/sql/write_email_validation.sql new file mode 100644 index 0000000..98fc60e --- /dev/null +++ b/crates/secd/store/pg/sql/write_email_validation.sql @@ -0,0 +1,27 @@ +insert into auth.email_validation + ( + email_validation_public_id + , identity_email_id + , attempts + , code + , is_validated + , created_at + , expires_at + ) +values ( + $1 + , ( + select identity_email_id + from auth.identity_email + where identity_id = $2 + and email_id = $3 + ) + , $4 + , $5 + , $6 + , $7 + , $8 +) on conflict (email_validation_public_id) do update + set attempts = excluded.attempts + , is_validated = excluded.is_validated + , expires_at = excluded.expires_at; diff --git a/crates/secd/store/pg/sql/write_identity.sql b/crates/secd/store/pg/sql/write_identity.sql new file mode 100644 index 0000000..eed1710 --- /dev/null +++ b/crates/secd/store/pg/sql/write_identity.sql @@ -0,0 +1,9 @@ +insert into auth.identity ( + identity_public_id, + data, + created_at +) values ( + $1, + $2, + $3 +); diff --git a/crates/secd/store/pg/sql/write_session.sql b/crates/secd/store/pg/sql/write_session.sql new file mode 100644 index 0000000..cd5892b --- /dev/null +++ b/crates/secd/store/pg/sql/write_session.sql @@ -0,0 +1,18 @@ +insert into auth.session ( + identity_id + , secret_hash + , created_at + , touched_at + , expires_at + , revoked_at +) values ( + (select identity_id from auth.identity where identity_public_id = $1) + , $2 + , $3 + , $4 + , $5 + , $6 +) on conflict (secret_hash) do update + set touched_at = excluded.touched_at + , revoked_at = excluded.revoked_at; +-- diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql new file mode 100644 index 0000000..aa95afc --- /dev/null +++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql @@ -0,0 +1,45 @@ +create table if not exists identity ( + identity_id integer primary key autoincrement + , identity_public_id uuid + , data text + , created_at timestamp not null + , unique(identity_public_id) +); + +create table if not exists email ( + email_id integer primary key autoincrement + , address text not null + , unique(address) +); + +create table if not exists identity_email ( + identity_email_id integer primary key autoincrement + , identity_id integer not null references identity(identity_id) + , email_id integer not null references email(email_id) + , created_at timestamp not null + , deleted_at timestamp +); + +create table if not exists email_validation ( + email_validation_id integer primary key autoincrement + , email_validation_public_id text not null -- uuid + , identity_email_id integer not null references identity_email(identity_email_id) + , attempts integer not null + , code text + , is_validated boolean not null + , created_at timestamp not null + , expires_at timestamp + , revoked_at timestamp + , unique(email_validation_public_id) +); + +create table if not exists session ( + session_id integer primary key autoincrement + , identity_id not null references identity(identity_id) + , secret_hash blob not null + , created_at timestamp not null + , touched_at timestamp not null + , expires_at timestamp + , revoked_at timestamp + , unique(secret_hash) +); diff --git a/crates/secd/store/sqlite/sql/find_email_validation.sql b/crates/secd/store/sqlite/sql/find_email_validation.sql new file mode 100644 index 0000000..a34c149 --- /dev/null +++ b/crates/secd/store/sqlite/sql/find_email_validation.sql @@ -0,0 +1,16 @@ +select + ev.email_validation_public_id + , i.identity_public_id + , e.address + , ev.attempts + , ev.code + , ev.is_validated + , ev.created_at + , ev.expires_at + , ev.revoked_at +from email_validation ev +join identity_email ie using (identity_email_id) +join email e using (email_id) +join identity i using (identity_id) +where ((?1 is null) or (email_validation_public_id = ?1)) +and ((?2 is null) or (code = ?2)); diff --git a/crates/secd/store/sqlite/sql/find_identity.sql b/crates/secd/store/sqlite/sql/find_identity.sql new file mode 100644 index 0000000..bd1654d --- /dev/null +++ b/crates/secd/store/sqlite/sql/find_identity.sql @@ -0,0 +1,9 @@ +select + identity_public_id, + data, + i.created_at +from identity i +join identity_email ie using (identity_id) +join email e using (email_id) +where ((?1 is null) or (i.identity_public_id = ?1)) +and ((?2 is null) or (e.address = ?2)) diff --git a/crates/secd/store/sqlite/sql/find_identity_by_code.sql b/crates/secd/store/sqlite/sql/find_identity_by_code.sql new file mode 100644 index 0000000..e1a6050 --- /dev/null +++ b/crates/secd/store/sqlite/sql/find_identity_by_code.sql @@ -0,0 +1,11 @@ +select identity_email_id +from auth.email_validation +where email_validation_public_id = ?1; +-- +select + identity_public_id + , data + , i.created_at +from auth.identity i +left join auth.identity_email ie using (identity_id) +where ie.identity_email_id = ?1; diff --git a/crates/secd/store/sqlite/sql/read_email_raw_id.sql b/crates/secd/store/sqlite/sql/read_email_raw_id.sql new file mode 100644 index 0000000..0bbafad --- /dev/null +++ b/crates/secd/store/sqlite/sql/read_email_raw_id.sql @@ -0,0 +1 @@ +select email_id from email where address = ? diff --git a/crates/secd/store/sqlite/sql/read_identity.sql b/crates/secd/store/sqlite/sql/read_identity.sql new file mode 100644 index 0000000..e69de29 diff --git a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql new file mode 100644 index 0000000..552c570 --- /dev/null +++ b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql @@ -0,0 +1,2 @@ +select identity_id from identity where identity_public_id = ?; +-- diff --git a/crates/secd/store/sqlite/sql/read_session.sql b/crates/secd/store/sqlite/sql/read_session.sql new file mode 100644 index 0000000..4daa352 --- /dev/null +++ b/crates/secd/store/sqlite/sql/read_session.sql @@ -0,0 +1,8 @@ +select + i.identity_public_id + , s.created_at + , s.expires_at + , s.revoked_at +from session s +join identity i using (identity_id) +where secret_hash = ?1; diff --git a/crates/secd/store/sqlite/sql/write_email.sql b/crates/secd/store/sqlite/sql/write_email.sql new file mode 100644 index 0000000..c127d9c --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_email.sql @@ -0,0 +1,11 @@ +insert into email ( + address +) values ( + ?1 +) on conflict (address) do nothing +returning email_id; +-- +select email_id from email where email = ?1; +-- +insert into identity_email (identity_id, email_id, created_at) values (?1, ?2, ?3); +-- diff --git a/crates/secd/store/sqlite/sql/write_email_validation.sql b/crates/secd/store/sqlite/sql/write_email_validation.sql new file mode 100644 index 0000000..37b13e1 --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_email_validation.sql @@ -0,0 +1,27 @@ +insert into email_validation + ( + email_validation_public_id + , identity_email_id + , attempts + , code + , is_validated + , created_at + , expires_at + ) +values ( + ?1 + , ( + select identity_email_id + from identity_email + where identity_id = ?2 + and email_id = ?3 + ) + , ?4 + , ?5 + , ?6 + , ?7 + , ?8 +) on conflict (email_validation_public_id) do update + set attempts = excluded.attempts + , is_validated = excluded.is_validated + , expires_at = excluded.expires_at; diff --git a/crates/secd/store/sqlite/sql/write_identity.sql b/crates/secd/store/sqlite/sql/write_identity.sql new file mode 100644 index 0000000..ff54468 --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_identity.sql @@ -0,0 +1 @@ +insert into identity (identity_public_id, data, created_at) values (?1, ?2, ?3); diff --git a/crates/secd/store/sqlite/sql/write_session.sql b/crates/secd/store/sqlite/sql/write_session.sql new file mode 100644 index 0000000..3c26986 --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_session.sql @@ -0,0 +1,18 @@ +insert into session ( + identity_id + , secret_hash + , created_at + , touched_at + , expires_at + , revoked_at +) values ( + (select identity_id from identity where identity_public_id = ?1) + , ?2 + , ?3 + , ?4 + , ?5 + , ?6 +) on conflict (secret_hash) do update + set touched_at = excluded.touched_at + , revoked_at = excluded.revoked_at; +-- -- cgit v1.2.3