Files
ecs-relay-service/container-code/connector/relay/store.py

98 lines
3.8 KiB
Python

"""Connector-owned persistent metadata. Never stores message/file/token bodies."""
import sqlite3
import time
import psycopg
from psycopg.rows import dict_row
INTEGRITY_ERRORS = (sqlite3.IntegrityError, psycopg.IntegrityError)
class Postgres:
def __init__(self, url):
self.url = url
self.conn = psycopg.connect(url, autocommit=True, row_factory=dict_row)
self.transaction = None
def execute(self, sql, params=()):
if self.conn.closed or self.conn.broken:
self.conn = psycopg.connect(self.url, autocommit=True, row_factory=dict_row)
return self.conn.execute(sql.replace("?", "%s"), params)
def __enter__(self):
self.transaction = self.conn.transaction()
self.transaction.__enter__()
return self
def __exit__(self, *args):
return self.transaction.__exit__(*args)
def close(self):
self.conn.close()
class Store:
def __init__(self, path, database_url=""):
path.parent.mkdir(parents=True, exist_ok=True)
schema = """
CREATE TABLE IF NOT EXISTS devices(
host_id TEXT PRIMARY KEY, cert TEXT NOT NULL, fingerprint TEXT NOT NULL,
owner TEXT UNIQUE, revoked INTEGER NOT NULL DEFAULT 0);
CREATE TABLE IF NOT EXISTS turns(
turn_id TEXT PRIMARY KEY, owner TEXT NOT NULL, host_id TEXT NOT NULL,
status TEXT NOT NULL, created DOUBLE PRECISION NOT NULL);
CREATE TABLE IF NOT EXISTS audit(
id INTEGER PRIMARY KEY, ts DOUBLE PRECISION NOT NULL, actor TEXT NOT NULL,
action TEXT NOT NULL, resource TEXT NOT NULL, result TEXT NOT NULL);
"""
if database_url:
self.db = Postgres(database_url)
self.db.conn.execute(schema.replace("id INTEGER PRIMARY KEY", "id BIGSERIAL PRIMARY KEY"))
else:
self.db = sqlite3.connect(path, check_same_thread=False)
self.db.row_factory = sqlite3.Row
self.db.executescript("PRAGMA journal_mode=WAL;" + schema)
def device(self, host):
row = self.db.execute("SELECT * FROM devices WHERE host_id=?", (host,)).fetchone()
return dict(row) if row else None
def by_owner(self, owner):
row = self.db.execute("SELECT * FROM devices WHERE owner=? AND revoked=0", (owner,)).fetchone()
return dict(row) if row else None
def add_device(self, host, cert, fp):
with self.db:
self.db.execute("INSERT INTO devices(host_id,cert,fingerprint) VALUES(?,?,?)", (host, cert, fp))
def bind(self, host, owner):
with self.db:
# Conditional update also protects against two connectors racing for the same device.
count = self.db.execute("UPDATE devices SET owner=? WHERE host_id=? AND owner IS NULL AND revoked=0", (owner, host)).rowcount
if count != 1:
raise ValueError("device unavailable or already bound")
def unbind(self, owner):
with self.db:
self.db.execute("UPDATE devices SET owner=NULL WHERE owner=?", (owner,))
def revoke(self, host):
with self.db:
self.db.execute("UPDATE devices SET revoked=1 WHERE host_id=?", (host,))
def begin_turn(self, turn, owner, host):
with self.db:
return self.db.execute("INSERT INTO turns VALUES(?,?,?,?,?) ON CONFLICT(turn_id) DO NOTHING",
(turn, owner, host, "inflight", time.time())).rowcount == 1
def finish(self, turn, status):
with self.db:
self.db.execute("UPDATE turns SET status=? WHERE turn_id=?", (status, turn))
def audit(self, actor, action, resource, result):
with self.db:
self.db.execute("INSERT INTO audit(ts,actor,action,resource,result) VALUES(?,?,?,?,?)",
(time.time(), actor, action, resource, result))
def close(self):
self.db.close()