- basic functionality with psql and sqlite

- cli helper tool

24 files changed, 311 insertions, 0 deletions

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
--- /dev/null
+++ b/crates/secd/store/pg/sql/read_identity.sql
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
--- /dev/null
+++ b/crates/secd/store/sqlite/sql/read_identity.sql
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;
+--