diff options
Diffstat (limited to '')
22 files changed, 164 insertions, 293 deletions
diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql index a8784f5..299f282 100644 --- a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql +++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql @@ -1,82 +1,81 @@ -create table if not exists identity ( - identity_id integer primary key autoincrement - , identity_public_id uuid - , data text - , created_at timestamptz not null - , deleted_at timestamptz - , unique(identity_public_id) +create table if not exists realm ( + realm_id integer primary key + , created_at integer not null ); -create table if not exists session ( - session_id integer primary key autoincrement - , identity_id bigint not null references identity(identity_id) - , secret_hash bytea not null - , created_at timestamptz not null - , expired_at timestamptz - , revoked_at timestamptz - , unique(secret_hash) +create table if not exists realm_data ( + realm_data_id integer primary key + , realm_id integer not null references realm(realm_id) + , email_provider text not null + , sms_provider text not null + , created_at integer not null + , deleted_at integer ); -create table if not exists oauth_provider ( - oauth_provider_id integer primary key autoincrement - , name text not null - , flow text not null - , base_url text not null - , response_type text not null - , default_scope text - , client_id text not null - , client_secret text not null - , redirect_url text not null - , created_at timestamptz not null - , deleted_at timestamptz - , unique (name, flow) +create table if not exists identity ( + identity_id integer primary key + , identity_public_id uuid not null + , data text -- some things are dervied, others are not + , created_at integer not null + , updated_at integer not null + , deleted_at integer + , unique(identity_public_id) ); -create table if not exists oauth_validation ( - oauth_validation_id integer primary key autoincrement - , oauth_validation_public_id uuid not null - , oauth_provider_id integer not null references oauth_provider(oauth_provider_id) - , access_token text - , raw_response text - , created_at timestamptz not null - , validated_at timestamptz - , unique (oauth_validation_public_id) +create table if not exists credential ( + credential_id integer primary key + , credential_public_id uuid not null + , identity_id integer not null references identity(identity_id) + , type text not null-- e.g. password, oidc, totop, lookup_secret, webauthn, ... + , data text not null + , version integer not null + , created_at integer not null + , revoked_at integer + , deleted_at integer ); -create table if not exists identity_oauth_validation ( - identity_oauth_validation_id integer primary key autoincrement - -- A validation does not require an identity to initiate - , identity_id bigint references identity(identity_id) - , oauth_validation_id bigint not null references oauth_validation(oauth_validation_id) - , revoked_at timestamptz - , deleted_at timestamptz - , unique(identity_id, oauth_validation_id) +create table if not exists address ( + address_id integer primary key + , address_public_id uuid not null + , type text not null + , value text not null + , created_at integer not null + , unique(value, type) ); -create table if not exists email ( - email_id integer primary key autoincrement - , address text not null - , unique(address) +create table if not exists address_validation ( + address_validation_id integer primary key + , address_validation_public_id uuid not null + , identity_id integer references identity(identity_id) + , address_id integer not null references address(address_id) + , method text not null -- e.g. email, sms, voice, oidc + , token_hash blob + , code_hash blob + , attempts integer not null + , created_at integer not null + , expires_at integer not null + , revoked_at integer + , validated_at integer + , unique(address_validation_public_id) ); -create table if not exists email_validation ( - email_validation_id integer primary key autoincrement - , email_validation_public_id uuid not null - , email_id bigint not null references email(email_id) - , code text - , is_oauth_derived boolean not null - , created_at timestamptz not null - , validated_at timestamptz - , expired_at timestamptz - , unique(email_validation_public_id) +create table if not exists session ( + session_id integer primary key + , identity_id integer not null references identity(identity_id) + , token_hash blob not null + , created_at integer not null + , expired_at integer not null + , revoked_at integer + , unique(token_hash) ); -create table if not exists identity_email_validation ( - identity_email_validation_id integer primary key autoincrement - -- A validation does not require an identity to initiate - , identity_id bigint references identity(identity_id) - , email_validation_id bigint not null references email_validation(email_validation_id) - , revoked_at timestamptz - , deleted_at timestamptz - , unique(identity_id, email_validation_id) +create table if not exists message ( + message_id integer primary key + , address_id integer not null references address(address_id) + , subject text + , body text + , template text not null + , template_vars text not null + , created_at integer not null + , sent_at integer ); diff --git a/crates/secd/store/sqlite/sql/find_address.sql b/crates/secd/store/sqlite/sql/find_address.sql new file mode 100644 index 0000000..da1df81 --- /dev/null +++ b/crates/secd/store/sqlite/sql/find_address.sql @@ -0,0 +1,8 @@ +select address_public_id + , type + , value + , created_at +from address +where (($1 is null) or (address_public_id = $1)) +and (($2 is null) or (type = $2)) +and (($3 is null) or (value = $3)); diff --git a/crates/secd/store/sqlite/sql/find_address_validation.sql b/crates/secd/store/sqlite/sql/find_address_validation.sql new file mode 100644 index 0000000..81d2cdf --- /dev/null +++ b/crates/secd/store/sqlite/sql/find_address_validation.sql @@ -0,0 +1,18 @@ +select av.address_validation_public_id + , i.identity_public_id + , a.address_public_id + , a.type + , a.value + , a.created_at + , av.method + , av.token_hash + , av.code_hash + , av.attempts + , av.created_at + , av.expires_at + , av.revoked_at + , av.validated_at +from address_validation av +join address a using(address_id) +left join identity i using(identity_id) +where (($1 is null) or (address_validation_public_id = $1)); diff --git a/crates/secd/store/sqlite/sql/find_email_validation.sql b/crates/secd/store/sqlite/sql/find_email_validation.sql deleted file mode 100644 index d7f311c..0000000 --- a/crates/secd/store/sqlite/sql/find_email_validation.sql +++ /dev/null @@ -1,18 +0,0 @@ -select - ev.email_validation_public_id - , i.identity_public_id - , e.address - , ev.code - , ev.is_oauth_derived - , ev.created_at - , ev.expired_at - , ev.validated_at - , iev.revoked_at - , iev.deleted_at -from email_validation ev -join email e using (email_id) -left join identity_email_validation iev using (email_validation_id) -left 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 index f94e7b1..1528407 100644 --- a/crates/secd/store/sqlite/sql/find_identity.sql +++ b/crates/secd/store/sqlite/sql/find_identity.sql @@ -1,11 +1,15 @@ -select - identity_public_id - , data - , i.created_at - , i.deleted_at -from identity i -join identity_email_validation iev using (identity_id) -join email_validation ev using (email_validation_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)); +select distinct + identity_public_id + , data + , i.created_at + , i.updated_at + , i.deleted_at +from identity i +left join address_validation av using (identity_id) +left join address a using (address_id) +left join session s using (identity_id) +where (($1 is null) or (i.identity_public_id = $1)) +and (($2 is null) or (a.value = $2)) +and (($3 is null) or (($3 is true) and (av.validated_at is not null))) +and (($4 is null) or (s.token_hash = $4)) +and i.deleted_at is null; diff --git a/crates/secd/store/sqlite/sql/find_identity_by_code.sql b/crates/secd/store/sqlite/sql/find_identity_by_code.sql deleted file mode 100644 index b70a13a..0000000 --- a/crates/secd/store/sqlite/sql/find_identity_by_code.sql +++ /dev/null @@ -1,11 +0,0 @@ -select identity_email_validation_id -from email_validation -where email_validation_public_id = $1::uuid --- -select - identity_public_id - , data - , i.created_at -from identity i -left join identity_email ie using (identity_id) -where ie.identity_email_validation_id = ?1; diff --git a/crates/secd/store/sqlite/sql/find_session.sql b/crates/secd/store/sqlite/sql/find_session.sql new file mode 100644 index 0000000..31640dd --- /dev/null +++ b/crates/secd/store/sqlite/sql/find_session.sql @@ -0,0 +1,11 @@ +select distinct + i.identity_public_id + , s.created_at + , s.expired_at + , s.revoked_at +from session s +join identity i using (identity_id) +where (($1 is null) or (s.token_hash = $1)) +and (($2 is null) or (i.identity_public_id = $2)) +and (($3 is null) or (s.expired_at > $3)) +and ((revoked_at is null) or ($4 is null) or (s.revoked_at > $4)); diff --git a/crates/secd/store/sqlite/sql/read_email_raw_id.sql b/crates/secd/store/sqlite/sql/read_email_raw_id.sql deleted file mode 100644 index a65c717..0000000 --- a/crates/secd/store/sqlite/sql/read_email_raw_id.sql +++ /dev/null @@ -1 +0,0 @@ -select email_id from email where address = ?1 diff --git a/crates/secd/store/sqlite/sql/read_identity.sql b/crates/secd/store/sqlite/sql/read_identity.sql deleted file mode 100644 index e69de29..0000000 --- a/crates/secd/store/sqlite/sql/read_identity.sql +++ /dev/null diff --git a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql b/crates/secd/store/sqlite/sql/read_identity_raw_id.sql deleted file mode 100644 index 2bdb718..0000000 --- a/crates/secd/store/sqlite/sql/read_identity_raw_id.sql +++ /dev/null @@ -1,2 +0,0 @@ -select identity_id from identity where identity_public_id = ?1; --- diff --git a/crates/secd/store/sqlite/sql/read_oauth_provider.sql b/crates/secd/store/sqlite/sql/read_oauth_provider.sql deleted file mode 100644 index 5c33cf0..0000000 --- a/crates/secd/store/sqlite/sql/read_oauth_provider.sql +++ /dev/null @@ -1,12 +0,0 @@ -select flow - , base_url - , response_type - , default_scope - , client_id - , client_secret - , redirect_url - , created_at - , deleted_at -from oauth_provider -where name = ?1 -and flow = ?2; diff --git a/crates/secd/store/sqlite/sql/read_oauth_validation.sql b/crates/secd/store/sqlite/sql/read_oauth_validation.sql deleted file mode 100644 index 75f5a94..0000000 --- a/crates/secd/store/sqlite/sql/read_oauth_validation.sql +++ /dev/null @@ -1,23 +0,0 @@ -select oauth_validation_public_id - , i.identity_public_id - , ov.access_token - , ov.raw_response - , ov.created_at - , ov.validated_at - , iov.revoked_at - , iov.deleted_at - , op.name as oauth_provider_name - , op.flow as oauth_provider_flow - , op.base_url as oauth_provider_base_url - , op.response_type as oauth_provider_response_type - , op.default_scope as oauth_provider_default_scope - , op.client_id as oauth_provider_client_id - , op.client_secret as oauth_provider_client_secret - , op.redirect_url as oauth_provider_redirect_url - , op.created_at as oauth_provider_created_at - , op.deleted_at as oauth_provider_deleted_at -from oauth_validation ov -join oauth_provider op using(oauth_provider_id) -left join identity_oauth_validation iov using(oauth_validation_id) -left join identity i using(identity_id) -where oauth_validation_public_id = ?1; diff --git a/crates/secd/store/sqlite/sql/read_session.sql b/crates/secd/store/sqlite/sql/read_session.sql deleted file mode 100644 index c415c4c..0000000 --- a/crates/secd/store/sqlite/sql/read_session.sql +++ /dev/null @@ -1,8 +0,0 @@ -select - i.identity_public_id - , s.created_at - , s.expired_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/read_validation_type.sql b/crates/secd/store/sqlite/sql/read_validation_type.sql deleted file mode 100644 index cc02ead..0000000 --- a/crates/secd/store/sqlite/sql/read_validation_type.sql +++ /dev/null @@ -1,7 +0,0 @@ -select 'Email' -from email_validation -where email_validation_public_id = ?1 -union -select 'Oauth' -from oauth_validation -where oauth_validation_public_id = ?1; diff --git a/crates/secd/store/sqlite/sql/write_address.sql b/crates/secd/store/sqlite/sql/write_address.sql new file mode 100644 index 0000000..56dab0c --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_address.sql @@ -0,0 +1,8 @@ +insert into address ( + address_public_id + , type + , value + , created_at +) values ( + $1, $2, $3, $4 +); diff --git a/crates/secd/store/sqlite/sql/write_address_validation.sql b/crates/secd/store/sqlite/sql/write_address_validation.sql new file mode 100644 index 0000000..67ce916 --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_address_validation.sql @@ -0,0 +1,26 @@ +insert into address_validation ( + address_validation_public_id + , identity_id + , address_id + , "method" + , token_hash + , code_hash + , attempts + , created_at + , expires_at + , revoked_at + , validated_at +) values( + $1 + , ( + select identity_id from identity where identity_public_id = $2 + ) + , ( + select address_id from address where address_public_id = $3 + ) + , $4, $5, $6, $7, $8, $9, $10, $11 +) on conflict (address_validation_public_id) do update + set identity_id = excluded.identity_id + , attempts = excluded.attempts + , revoked_at = excluded.revoked_at + , validated_at = excluded.validated_at; diff --git a/crates/secd/store/sqlite/sql/write_email.sql b/crates/secd/store/sqlite/sql/write_email.sql deleted file mode 100644 index a64aed4..0000000 --- a/crates/secd/store/sqlite/sql/write_email.sql +++ /dev/null @@ -1,6 +0,0 @@ -insert into email ( - address -) values ( - $1 -) on conflict (address) do nothing -returning email_id; diff --git a/crates/secd/store/sqlite/sql/write_email_validation.sql b/crates/secd/store/sqlite/sql/write_email_validation.sql deleted file mode 100644 index d839310..0000000 --- a/crates/secd/store/sqlite/sql/write_email_validation.sql +++ /dev/null @@ -1,43 +0,0 @@ -insert into email_validation - ( - email_validation_public_id - , email_id - , code - , is_oauth_derived - , created_at - , validated_at - , expired_at - ) -values ( - ?1 - , ?2 - , ?3 - , ?4 - , ?5 - , ?6 - , ?7 -) on conflict (email_validation_public_id) do update - set validated_at = excluded.validated_at - , expired_at = excluded.expired_at; --- -insert into identity_email_validation ( - identity_id - , email_validation_id - , revoked_at - , deleted_at -) values ( - ( - select identity_id - from identity - where identity_public_id = ?1 - ) - , ( - select email_validation_id - from email_validation - where email_validation_public_id = ?2 - ) - , ?3 - , ?4 -) on conflict (identity_id, email_validation_id) do update - set revoked_at = excluded.revoked_at - , deleted_at = excluded.deleted_at; diff --git a/crates/secd/store/sqlite/sql/write_identity.sql b/crates/secd/store/sqlite/sql/write_identity.sql index 8cf46c5..aa59358 100644 --- a/crates/secd/store/sqlite/sql/write_identity.sql +++ b/crates/secd/store/sqlite/sql/write_identity.sql @@ -1,11 +1,12 @@ insert into identity ( - identity_public_id, - data, - created_at + identity_public_id + , data + , created_at + , updated_at + , deleted_at ) values ( - ?1, - ?2, - ?3 -) on conflict(identity_public_id) do update + $1, $2, $3, $4, $5 +) on conflict (identity_public_id) do update set data = excluded.data + , updated_at = excluded.updated_at , deleted_at = excluded.deleted_at; diff --git a/crates/secd/store/sqlite/sql/write_oauth_provider.sql b/crates/secd/store/sqlite/sql/write_oauth_provider.sql deleted file mode 100644 index 421caf7..0000000 --- a/crates/secd/store/sqlite/sql/write_oauth_provider.sql +++ /dev/null @@ -1,23 +0,0 @@ -insert into oauth_provider ( - name - , flow - , base_url - , response_type - , default_scope - , client_id - , client_secret - , redirect_url - , created_at - , deleted_at -) values ( - ?1 - , ?2 - , ?3 - , ?4 - , ?5 - , ?6 - , ?7 - , ?8 - , ?9 - , ?10 -) on conflict (name, flow) do nothing; diff --git a/crates/secd/store/sqlite/sql/write_oauth_validation.sql b/crates/secd/store/sqlite/sql/write_oauth_validation.sql deleted file mode 100644 index ccb11aa..0000000 --- a/crates/secd/store/sqlite/sql/write_oauth_validation.sql +++ /dev/null @@ -1,45 +0,0 @@ -insert into oauth_validation ( - oauth_validation_public_id - , oauth_provider_id - , access_token - , raw_response - , created_at - , validated_at -) values ( - ?1 - , ( - select oauth_provider_id - from oauth_provider - where name = ?2 - and flow = ?3 - ) - , ?4 - , ?5 - , ?6 - , ?7 -) on conflict (oauth_validation_public_id) do update - set access_token = excluded.access_token - , validated_at = excluded.validated_at - , raw_response = excluded.raw_response; --- -insert into identity_oauth_validation ( - identity_id - , oauth_validation_id - , revoked_at - , deleted_at -) values ( - ( - select identity_id - from identity - where identity_public_id = ?1 - ) - , ( - select oauth_validation_id - from oauth_validation - where oauth_validation_public_id = ?2 - ) - , ?3 - , ?4 -) on conflict (identity_id, oauth_validation_id) do update - set revoked_at = excluded.revoked_at - , deleted_at = excluded.deleted_at; diff --git a/crates/secd/store/sqlite/sql/write_session.sql b/crates/secd/store/sqlite/sql/write_session.sql index 480af54..4679912 100644 --- a/crates/secd/store/sqlite/sql/write_session.sql +++ b/crates/secd/store/sqlite/sql/write_session.sql @@ -1,15 +1,10 @@ insert into session ( identity_id - , secret_hash + , token_hash , created_at , expired_at , revoked_at ) values ( - (select identity_id from identity where identity_public_id = ?1) - , ?2 - , ?3 - , ?4 - , ?5 -) on conflict (secret_hash) do update - set revoked_at = excluded.revoked_at; --- + (select identity_id from identity where identity_public_id = $1) + , $2, $3, $4, $5 +); |