111 lines
4 KiB
SQL
111 lines
4 KiB
SQL
begin transaction;
|
|
|
|
create domain NixPath as bytea
|
|
check (length(value) = 20);
|
|
create domain SHA256Hash as bytea
|
|
check (length(value) = 16);
|
|
create domain Ed25519Public as bytea
|
|
check (length(value) = 32);
|
|
create domain SignatureBytes as bytea
|
|
check (length(value) = 64);
|
|
|
|
create table PathName (
|
|
hash NixPath not null primary key,
|
|
-- Split off into a separate PathName table since a reference only requires
|
|
-- a PathName exists but not necessarily a full NarInfo
|
|
name text not null
|
|
);
|
|
|
|
-- Yoinked from https://git.lix.systems/lix-project/lix/src/e34833c0253340f47dc0add8609eb86cf9cba19b/src/libstore/binary-cache-store.cc#L151-L158
|
|
create type Compression as enum (
|
|
'none',
|
|
'bzip2',
|
|
'zstd',
|
|
'xz',
|
|
'lzip',
|
|
'lz4',
|
|
'br'
|
|
);
|
|
|
|
-- There are no non sha256 CA hashes in the wild and we can honestly just tell anyone sending us one to go away
|
|
create type CAType as enum (
|
|
'textsha256',
|
|
'fixedrecsha256',
|
|
'fixedflatsha256'
|
|
);
|
|
|
|
create table NarInfo (
|
|
-- URL not needed (derivable from file_hash and compression)
|
|
file_size bigint not null,
|
|
nar_size bigint not null,
|
|
|
|
hash NixPath primary key not null references PathName(hash),
|
|
file_hash SHA256Hash not null,
|
|
nar_hash SHA256Hash not null,
|
|
ca_hash SHA256Hash,
|
|
|
|
compression Compression not null,
|
|
ca_type CAType
|
|
);
|
|
|
|
create table Reference (
|
|
target NixPath not null references PathName(hash),
|
|
by_narinfo NixPath not null references NarInfo(hash),
|
|
primary key (target, by_narinfo)
|
|
);
|
|
|
|
create index reference_by_target on Reference using hash (target);
|
|
create index reference_by_referencer on Reference using hash (by_narinfo);
|
|
|
|
create table KeyName (
|
|
id serial primary key,
|
|
name text not null
|
|
);
|
|
|
|
comment on table KeyName is 'The Nix name for a key, which is often, but not strictly, related to the domain name';
|
|
|
|
-- FIXME: partitioning the system for security? e.g. can we make it so that the app *cannot* mess with public key validity periods?
|
|
create table BuilderPublicKey (
|
|
id integer primary key references KeyName(id),
|
|
public_key Ed25519Public not null,
|
|
comment text,
|
|
valid_from timestamptz,
|
|
valid_until timestamptz
|
|
);
|
|
|
|
comment on table BuilderPublicKey is 'Public keys which iron-seal is willing to re-sign paths signed with';
|
|
comment on column BuilderPublicKey.valid_from is 'Signature time when the public key is first trusted for new signatures. If null, from a time in the indefinite past.';
|
|
comment on column BuilderPublicKey.valid_until is 'Signature time when the public key is last trusted for new signatures. If null, to a time in the indefinite future.';
|
|
|
|
create table OwnPublicKey (
|
|
id integer primary key references KeyName(id),
|
|
public_key Ed25519Public not null,
|
|
comment text,
|
|
valid_from timestamptz,
|
|
valid_until timestamptz
|
|
);
|
|
|
|
comment on table OwnPublicKey is 'Public keys which iron-seal will sign paths with during their validity period';
|
|
comment on column OwnPublicKey.valid_from is 'Signature time when the public key will first be used for new signatures. If null, from a time in the indefinite past.';
|
|
comment on column OwnPublicKey.valid_until is 'Signature time when the public key will last be used for new signatures. If null, to a time in the indefinite future.';
|
|
|
|
create table Signature (
|
|
path NixPath references NarInfo(hash),
|
|
|
|
id serial primary key,
|
|
signer integer not null references KeyName(id),
|
|
sig_bytes SignatureBytes not null,
|
|
signed_at timestamptz not null
|
|
);
|
|
|
|
comment on column Signature.signed_at is 'When the path was signed. For s3-sourced paths we have a reliable idea of it if we take the narinfo date as the signature date (and can assume there is no backdating by trusting s3)';
|
|
|
|
create unique index signature_path_only_signed_once on Signature (signer, path);
|
|
|
|
-- Definitely want to be able to look up signatures by store path
|
|
create index signature_by_path on Signature using hash (path);
|
|
-- Definitely want to be able to look up paths signed by a key
|
|
create index signature_by_signer on Signature (signer);
|
|
|
|
rollback;
|