diff options
Diffstat (limited to '')
23 files changed, 184 insertions, 314 deletions
diff --git a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql b/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql deleted file mode 100644 index 3d4d84c..0000000 --- a/crates/secd/store/pg/migrations/20221116062550_bootstrap.sql +++ /dev/null @@ -1,86 +0,0 @@ -create extension if not exists pgcrypto; -create extension if not exists citext; -create schema if not exists secd; - -create table if not exists secd.identity ( - identity_id bigserial primary key - , identity_public_id uuid - , data text - , created_at timestamptz not null - , deleted_at timestamptz - , unique(identity_public_id) -); - -create table if not exists secd.session ( - session_id bigserial primary key - , identity_id bigint not null references secd.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 secd.oauth_provider ( - oauth_provider_id serial primary key - , 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 secd.oauth_validation ( - oauth_validation_id bigserial primary key - , oauth_validation_public_id uuid not null - , oauth_provider_id integer not null references secd.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 secd.identity_oauth_validation ( - identity_oauth_validation_id bigserial primary key - -- A validation does not require an identity to initiate - , identity_id bigint references secd.identity(identity_id) - , oauth_validation_id bigint not null references secd.oauth_validation(oauth_validation_id) - , revoked_at timestamptz - , deleted_at timestamptz - , unique(identity_id, oauth_validation_id) -); - -create table if not exists secd.email ( - email_id bigserial primary key - , address text not null - , unique(address) -); - -create table if not exists secd.email_validation ( - email_validation_id bigserial primary key - , email_validation_public_id uuid not null - , email_id bigint not null references secd.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 secd.identity_email_validation ( - identity_email_validation_id bigserial primary key - -- A validation does not require an identity to initiate - , identity_id bigint references secd.identity(identity_id) - , email_validation_id bigint not null references secd.email_validation(email_validation_id) - , revoked_at timestamptz - , deleted_at timestamptz - , unique(identity_id, email_validation_id) -); diff --git a/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql b/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql new file mode 100644 index 0000000..2b89957 --- /dev/null +++ b/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql @@ -0,0 +1,85 @@ +create extension if not exists pgcrypto; +create extension if not exists citext; +create schema if not exists secd; + +create table if not exists secd.realm ( + realm_id bigserial primary key + , created_at timestamptz not null +); + +create table if not exists secd.realm_data ( + realm_data_id bigserial primary key + , realm_id bigint not null references secd.realm(realm_id) + , email_provider jsonb not null + , sms_provider jsonb not null + , created_at timestamptz not null + , deleted_at timestamptz +); + +create table if not exists secd.identity ( + identity_id bigserial primary key + , identity_public_id uuid not null + , data jsonb -- some things are dervied, others are not + , created_at timestamptz not null + , updated_at timestamptz not null + , deleted_at timestamptz + , unique(identity_public_id) +); + +create table if not exists secd.credential ( + credential_id bigserial primary key + , credential_public_id uuid not null + , identity_id bigint not null references secd.identity(identity_id) + , type text not null-- e.g. password, oidc, totop, lookup_secret, webauthn, ... + , data jsonb not null + , version integer not null + , created_at timestamptz not null + , revoked_at timestamptz + , deleted_at timestamptz +); + +create table if not exists secd.address ( + address_id bigserial primary key + , address_public_id uuid not null + , type text not null + , value text not null + , created_at timestamptz not null + , unique(value, type) +); + +create table if not exists secd.address_validation ( + address_validation_id bigserial primary key + , address_validation_public_id uuid not null + , identity_id bigint references secd.identity(identity_id) + , address_id bigint not null references secd.address(address_id) + , method text not null -- e.g. email, sms, voice, oidc + , token_hash bytea + , code_hash bytea + , attempts integer not null + , created_at timestamptz not null + , expires_at timestamptz not null + , revoked_at timestamptz + , validated_at timestamptz + , unique(address_validation_public_id) +); + +create table if not exists secd.session ( + session_id bigserial primary key + , identity_id bigint not null references secd.identity(identity_id) + , token_hash bytea not null + , created_at timestamptz not null + , expired_at timestamptz not null + , revoked_at timestamptz + , unique(token_hash) +); + +create table if not exists secd.message ( + message_id bigserial primary key + , address_id bigint not null references secd.address(address_id) + , subject text + , body text + , template text not null + , template_vars jsonb not null + , created_at timestamptz not null + , sent_at timestamptz +); diff --git a/crates/secd/store/pg/sql/find_address.sql b/crates/secd/store/pg/sql/find_address.sql new file mode 100644 index 0000000..5eaafbb --- /dev/null +++ b/crates/secd/store/pg/sql/find_address.sql @@ -0,0 +1,8 @@ +select address_public_id + , type + , value + , created_at +from secd.address +where (($1::uuid is null) or (address_public_id = $1)) +and (($2::text is null) or (type = $2)) +and (($3::text is null) or (value = $3)); diff --git a/crates/secd/store/pg/sql/find_address_validation.sql b/crates/secd/store/pg/sql/find_address_validation.sql new file mode 100644 index 0000000..3874994 --- /dev/null +++ b/crates/secd/store/pg/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 secd.address_validation av +join secd.address a using(address_id) +left join secd.identity i using(identity_id) +where (($1::uuid is null) or (address_validation_public_id = $1)); diff --git a/crates/secd/store/pg/sql/find_email_validation.sql b/crates/secd/store/pg/sql/find_email_validation.sql deleted file mode 100644 index 1eb3e43..0000000 --- a/crates/secd/store/pg/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 secd.email_validation ev -join secd.email e using (email_id) -left join secd.identity_email_validation iev using (email_validation_id) -left join secd.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 index 135ff9a..37105cb 100644 --- a/crates/secd/store/pg/sql/find_identity.sql +++ b/crates/secd/store/pg/sql/find_identity.sql @@ -1,11 +1,15 @@ -select - identity_public_id - , data - , i.created_at - , i.deleted_at -from secd.identity i -join secd.identity_email_validation iev using (identity_id) -join secd.email_validation ev using (email_validation_id) -join secd.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::text + , i.created_at + , i.updated_at + , i.deleted_at +from secd.identity i +left join secd.address_validation av using (identity_id) +left join secd.address a using (address_id) +left join secd.session s using (identity_id) +where (($1::uuid is null) or (i.identity_public_id = $1)) +and (($2::text is null) or (a.value = $2)) +and (($3::bool is null) or (($3::bool is true) and (av.validated_at is not null))) +and (($4::bytea is null) or (s.token_hash = $4)) +and i.deleted_at is null; diff --git a/crates/secd/store/pg/sql/find_identity_by_code.sql b/crates/secd/store/pg/sql/find_identity_by_code.sql deleted file mode 100644 index e5a0970..0000000 --- a/crates/secd/store/pg/sql/find_identity_by_code.sql +++ /dev/null @@ -1,11 +0,0 @@ -select identity_email_validation_id -from secd.email_validation -where email_validation_public_id = $1::uuid --- -select - identity_public_id - , data - , i.created_at -from secd.identity i -left join secd.identity_email ie using (identity_id) -where ie.identity_email_validation_id = $1; diff --git a/crates/secd/store/pg/sql/find_session.sql b/crates/secd/store/pg/sql/find_session.sql new file mode 100644 index 0000000..ca58480 --- /dev/null +++ b/crates/secd/store/pg/sql/find_session.sql @@ -0,0 +1,11 @@ +select distinct + i.identity_public_id + , s.created_at + , s.expired_at + , s.revoked_at +from secd.session s +join secd.identity i using (identity_id) +where (($1::bytea is null) or (s.token_hash = $1)) +and (($2::uuid is null) or (i.identity_public_id = $2)) +and (($3::timestamptz is null) or (s.expired_at > $3)) +and ((revoked_at is null) or ($4::timestamptz is null) or (s.revoked_at > $4)); diff --git a/crates/secd/store/pg/sql/read_email_raw_id.sql b/crates/secd/store/pg/sql/read_email_raw_id.sql deleted file mode 100644 index 6604fb0..0000000 --- a/crates/secd/store/pg/sql/read_email_raw_id.sql +++ /dev/null @@ -1 +0,0 @@ -select email_id from secd.email where address = $1 diff --git a/crates/secd/store/pg/sql/read_identity.sql b/crates/secd/store/pg/sql/read_identity.sql deleted file mode 100644 index e69de29..0000000 --- a/crates/secd/store/pg/sql/read_identity.sql +++ /dev/null diff --git a/crates/secd/store/pg/sql/read_identity_raw_id.sql b/crates/secd/store/pg/sql/read_identity_raw_id.sql deleted file mode 100644 index 5b5d95c..0000000 --- a/crates/secd/store/pg/sql/read_identity_raw_id.sql +++ /dev/null @@ -1,2 +0,0 @@ -select identity_id from secd.identity where identity_public_id = $1; --- diff --git a/crates/secd/store/pg/sql/read_oauth_provider.sql b/crates/secd/store/pg/sql/read_oauth_provider.sql deleted file mode 100644 index edaa114..0000000 --- a/crates/secd/store/pg/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 secd.oauth_provider -where name = $1 -and flow = $2; diff --git a/crates/secd/store/pg/sql/read_oauth_validation.sql b/crates/secd/store/pg/sql/read_oauth_validation.sql deleted file mode 100644 index d8361ea..0000000 --- a/crates/secd/store/pg/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 secd.oauth_validation ov -join secd.oauth_provider op using(oauth_provider_id) -left join secd.identity_oauth_validation iov using(oauth_validation_id) -left join secd.identity i using(identity_id) -where oauth_validation_public_id = $1; diff --git a/crates/secd/store/pg/sql/read_session.sql b/crates/secd/store/pg/sql/read_session.sql deleted file mode 100644 index b1f98d4..0000000 --- a/crates/secd/store/pg/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 secd.session s -join secd.identity i using (identity_id) -where secret_hash = $1; diff --git a/crates/secd/store/pg/sql/read_validation_type.sql b/crates/secd/store/pg/sql/read_validation_type.sql deleted file mode 100644 index 2eceb98..0000000 --- a/crates/secd/store/pg/sql/read_validation_type.sql +++ /dev/null @@ -1,7 +0,0 @@ -select 'Email' -from secd.email_validation -where email_validation_public_id = $1 -union -select 'Oauth' -from secd.oauth_validation -where oauth_validation_public_id = $1; diff --git a/crates/secd/store/pg/sql/write_address.sql b/crates/secd/store/pg/sql/write_address.sql new file mode 100644 index 0000000..da1bf3a --- /dev/null +++ b/crates/secd/store/pg/sql/write_address.sql @@ -0,0 +1,8 @@ +insert into secd.address ( + address_public_id + , type + , value + , created_at +) values ( + $1, $2, $3, $4 +); diff --git a/crates/secd/store/pg/sql/write_address_validation.sql b/crates/secd/store/pg/sql/write_address_validation.sql new file mode 100644 index 0000000..3be830e --- /dev/null +++ b/crates/secd/store/pg/sql/write_address_validation.sql @@ -0,0 +1,27 @@ +insert into secd.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 secd.identity where identity_public_id = $2 + ) + , ( + select address_id from secd.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 +returning (xmax = 0); diff --git a/crates/secd/store/pg/sql/write_email.sql b/crates/secd/store/pg/sql/write_email.sql deleted file mode 100644 index 06a1dc5..0000000 --- a/crates/secd/store/pg/sql/write_email.sql +++ /dev/null @@ -1,6 +0,0 @@ -insert into secd.email ( - address -) values ( - $1 -) on conflict (address) do nothing -returning email_id; diff --git a/crates/secd/store/pg/sql/write_email_validation.sql b/crates/secd/store/pg/sql/write_email_validation.sql deleted file mode 100644 index ff25b87..0000000 --- a/crates/secd/store/pg/sql/write_email_validation.sql +++ /dev/null @@ -1,43 +0,0 @@ -insert into secd.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 secd.identity_email_validation ( - identity_id - , email_validation_id - , revoked_at - , deleted_at -) values ( - ( - select identity_id - from secd.identity - where identity_public_id = $1 - ) - , ( - select email_validation_id - from secd.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/pg/sql/write_identity.sql b/crates/secd/store/pg/sql/write_identity.sql index 94a51fe..67662a6 100644 --- a/crates/secd/store/pg/sql/write_identity.sql +++ b/crates/secd/store/pg/sql/write_identity.sql @@ -1,11 +1,12 @@ insert into secd.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::jsonb, $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/pg/sql/write_oauth_provider.sql b/crates/secd/store/pg/sql/write_oauth_provider.sql deleted file mode 100644 index ba69857..0000000 --- a/crates/secd/store/pg/sql/write_oauth_provider.sql +++ /dev/null @@ -1,25 +0,0 @@ -insert into secd.oauth_provider ( - oauth_provider_id - , name - , flow - , base_url - , response_type - , default_scope - , client_id - , client_secret - , redirect_url - , created_at - , deleted_at -) values ( - default - , $1 - , $2 - , $3 - , $4 - , $5 - , $6 - , $7 - , $8 - , $9 - , $10 -) on conflict (name, flow) do nothing; diff --git a/crates/secd/store/pg/sql/write_oauth_validation.sql b/crates/secd/store/pg/sql/write_oauth_validation.sql deleted file mode 100644 index 11f2578..0000000 --- a/crates/secd/store/pg/sql/write_oauth_validation.sql +++ /dev/null @@ -1,45 +0,0 @@ -insert into secd.oauth_validation ( - oauth_validation_public_id - , oauth_provider_id - , access_token - , raw_response - , created_at - , validated_at -) values ( - $1 - , ( - select oauth_provider_id - from secd.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 secd.identity_oauth_validation ( - identity_id - , oauth_validation_id - , revoked_at - , deleted_at -) values ( - ( - select identity_id - from secd.identity - where identity_public_id = $1 - ) - , ( - select oauth_validation_id - from secd.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/pg/sql/write_session.sql b/crates/secd/store/pg/sql/write_session.sql index 1b238c6..18dc1f1 100644 --- a/crates/secd/store/pg/sql/write_session.sql +++ b/crates/secd/store/pg/sql/write_session.sql @@ -1,15 +1,10 @@ insert into secd.session ( identity_id - , secret_hash + , token_hash , created_at , expired_at , revoked_at ) values ( - (select identity_id from secd.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 secd.identity where identity_public_id = $1) + , $2, $3, $4, $5 +); |