PostgreSQL Setup

postgresql://[user]:[password]@[host]:[port]/[database]
postgresql://[user]:[password]@[host]:[port]/[database]?sslmode=require

DATABASE_URL=postgresql://vane_user:secret@localhost:5432/vane

CREATE TABLE companies (
  company_id    TEXT PRIMARY KEY,
  spiffe_id     TEXT NOT NULL UNIQUE,
  registered_at TEXT NOT NULL,
  metadata      TEXT
);

CREATE TABLE keys (
  company_id  TEXT PRIMARY KEY REFERENCES companies(company_id),
  public_key  TEXT NOT NULL,
  private_key TEXT NOT NULL  -- AES-256-GCM encrypted if VANE_MASTER_KEY is set
);

CREATE TABLE api_keys (
  key        TEXT PRIMARY KEY,
  company_id TEXT NOT NULL REFERENCES companies(company_id),
  label      TEXT,
  created_at TEXT NOT NULL
);

CREATE TABLE agents (
  agent_id      TEXT NOT NULL,
  company_id    TEXT NOT NULL REFERENCES companies(company_id),
  spiffe_id     TEXT NOT NULL UNIQUE,
  registered_at TEXT NOT NULL,
  metadata      TEXT,
  PRIMARY KEY (agent_id, company_id)
);

CREATE TABLE records (
  company_id TEXT    NOT NULL REFERENCES companies(company_id),
  idx        INTEGER NOT NULL,
  timestamp  TEXT    NOT NULL,
  payload    TEXT    NOT NULL,
  delegation TEXT,
  hash       TEXT    NOT NULL,
  signature  TEXT    NOT NULL,
  PRIMARY KEY (company_id, idx)
);

-- Rejects any UPDATE or DELETE on the records table
CREATE OR REPLACE FUNCTION records_append_only()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'records table is append-only: % is not permitted', TG_OP;
END;
$$;

CREATE TRIGGER records_no_update
  BEFORE UPDATE ON records
  FOR EACH ROW EXECUTE FUNCTION records_append_only();

CREATE TRIGGER records_no_delete
  BEFORE DELETE ON records
  FOR EACH ROW EXECUTE FUNCTION records_append_only();

CREATE TABLE revoked_passports (
  jti        TEXT NOT NULL,
  company_id TEXT NOT NULL REFERENCES companies(company_id),
  revoked_at TEXT NOT NULL,
  reason     TEXT,
  PRIMARY KEY (jti, company_id)
);

CREATE TABLE oauth_clients (
  client_id     TEXT PRIMARY KEY,
  client_secret TEXT NOT NULL,  -- SHA-256 hashed
  company_id    TEXT NOT NULL REFERENCES companies(company_id),
  created_at    TEXT NOT NULL
);

CREATE TABLE oauth_tokens (
  token      TEXT PRIMARY KEY,
  company_id TEXT NOT NULL REFERENCES companies(company_id),
  expires_at BIGINT NOT NULL,  -- Unix epoch milliseconds
  created_at TEXT NOT NULL
);

-- Create the database
CREATE DATABASE vane;

-- Create the user
CREATE USER vane_user WITH PASSWORD 'a-very-strong-password';

-- Grant only what's needed (no superuser, no createdb)
GRANT CONNECT ON DATABASE vane TO vane_user;
\c vane
GRANT USAGE ON SCHEMA public TO vane_user;
GRANT CREATE ON SCHEMA public TO vane_user;
GRANT SELECT, INSERT, DELETE ON ALL TABLES IN SCHEMA public TO vane_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO vane_user;

DATABASE_URL="postgresql://vane_user:secret@db.example.com:5432/vane?sslmode=require"