diff options
Diffstat (limited to 'crates/secd/store')
7 files changed, 95 insertions, 5 deletions
diff --git a/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql b/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql index 2b89957..0cf3fa0 100644 --- a/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql +++ b/crates/secd/store/pg/migrations/20221222002434_bootstrap.sql @@ -19,7 +19,7 @@ create table if not exists secd.realm_data ( 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 + , data text -- we do not prescribe JSON or any other serialization format. , created_at timestamptz not null , updated_at timestamptz not null , deleted_at timestamptz @@ -30,14 +30,18 @@ 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) + , partial_key text , 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 unique index if not exists credential_passphrase_type_key_ix +on secd.credential (partial_key) +where type = 'Passphrase'; + create table if not exists secd.address ( address_id bigserial primary key , address_public_id uuid not null @@ -83,3 +87,23 @@ create table if not exists secd.message ( , created_at timestamptz not null , sent_at timestamptz ); + +create table if not exists secd.namespace_config ( + namespace text not null + , serialized_config text not null + , created_at xid8 not null + , deleted_at xid8 + -- TODO: indexes and stuff +); + +create table if not exists secd.relation_tuple ( + namespace text not null + , object_id text not null + , relation text not null + , userset_namespace text not null + , userset_object_id text not null + , userset_relation text not null + , created_at xid8 not null + , deleted_at xid8 not null + -- TODO: indexes and stuff +); diff --git a/crates/secd/store/pg/sql/find_credential.sql b/crates/secd/store/pg/sql/find_credential.sql new file mode 100644 index 0000000..e30c0ea --- /dev/null +++ b/crates/secd/store/pg/sql/find_credential.sql @@ -0,0 +1,12 @@ +select c.credential_public_id + , i.identity_public_id + , c.data::text + , c.created_at + , c.revoked_at + , c.deleted_at +from secd.credential c +join secd.identity i using (identity_id) +where (($1::uuid is null) or (c.credential_public_id = $1)) +and (($2::uuid is null) or (i.identity_public_id = $2)) +and (($3::text is null) or (c.type = $3)) +and (($3::text is null or $4::text is null) or (c.data->$3->>'key' = $4)) diff --git a/crates/secd/store/pg/sql/write_credential.sql b/crates/secd/store/pg/sql/write_credential.sql new file mode 100644 index 0000000..17e03a2 --- /dev/null +++ b/crates/secd/store/pg/sql/write_credential.sql @@ -0,0 +1,19 @@ +insert into secd.credential ( + credential_public_id + , identity_id + , partial_key + , type + , data + , created_at + , revoked_at + , deleted_at +) values ( + $1 + , (select identity_id from secd.identity where identity_public_id = $2) + , $3 + , $4 + , $5::jsonb + , $6 + , $7 + , $8 +); diff --git a/crates/secd/store/pg/sql/write_identity.sql b/crates/secd/store/pg/sql/write_identity.sql index 67662a6..4b2745b 100644 --- a/crates/secd/store/pg/sql/write_identity.sql +++ b/crates/secd/store/pg/sql/write_identity.sql @@ -5,7 +5,7 @@ insert into secd.identity ( , updated_at , deleted_at ) values ( - $1, $2::jsonb, $3, $4, $5 + $1, $2, $3, $4, $5 ) on conflict (identity_public_id) do update set data = excluded.data , updated_at = excluded.updated_at diff --git a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql index 299f282..b2ce45d 100644 --- a/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql +++ b/crates/secd/store/sqlite/migrations/20221125051738_bootstrap.sql @@ -15,7 +15,7 @@ create table if not exists realm_data ( 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 + , data text -- we do not prescribe JSON or any other serialization format , created_at integer not null , updated_at integer not null , deleted_at integer @@ -26,14 +26,18 @@ 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) + , partial_key text , 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 unique index if not exists credential_passphrase_type_key_ix +on credential (partial_key) +where type = 'Passphrase'; + create table if not exists address ( address_id integer primary key , address_public_id uuid not null diff --git a/crates/secd/store/sqlite/sql/find_credential.sql b/crates/secd/store/sqlite/sql/find_credential.sql new file mode 100644 index 0000000..9062914 --- /dev/null +++ b/crates/secd/store/sqlite/sql/find_credential.sql @@ -0,0 +1,12 @@ +select c.credential_public_id + , i.identity_public_id + , c.data + , c.created_at + , c.revoked_at + , c.deleted_at +from credential c +join identity i using (identity_id) +where (($1 is null) or (c.credential_public_id = $1)) +and (($2 is null) or (i.identity_public_id = $2)) +and (($3 is null) or (c.type = $3)) +and (($3 is null or $4 is null) or (c.data->$3->>'key' = $4)) diff --git a/crates/secd/store/sqlite/sql/write_credential.sql b/crates/secd/store/sqlite/sql/write_credential.sql new file mode 100644 index 0000000..3319226 --- /dev/null +++ b/crates/secd/store/sqlite/sql/write_credential.sql @@ -0,0 +1,19 @@ +insert into credential ( + credential_public_id + , identity_id + , partial_key + , type + , data + , created_at + , revoked_at + , deleted_at +) values ( + $1 + , (select identity_id from identity where identity_public_id = $2) + , $3 + , $4 + , $5 + , $6 + , $7 + , $8 +); |