SHA256
rewirite to new name
This commit is contained in:
+852
-110
@@ -1,136 +1,878 @@
|
||||
"""SQLite storage layer: stores ONLY ciphertext records.
|
||||
|
||||
Record layout (all client-provided fields, opaque to the server):
|
||||
id INTEGER PRIMARY KEY server surrogate key
|
||||
uuid TEXT UNIQUE NOT NULL client-generated record id
|
||||
ciphertext BLOB NOT NULL XChaCha20-Poly1305 payload (base64 in API)
|
||||
nonce BLOB NOT NULL random 24-byte XChaCha nonce (base64 in API)
|
||||
salt BLOB NOT NULL per-record Argon2id salt (base64 in API)
|
||||
version INTEGER NOT NULL monotonic counter, bump on every update
|
||||
created_at TEXT NOT NULL first seen server time (UTC ISO)
|
||||
updated_at TEXT NOT NULL last update server time (UTC ISO)
|
||||
|
||||
The server never sees plaintext: no field contains decrypted data and
|
||||
nothing is logged except the UUID/version bookkeeping.
|
||||
"""
|
||||
|
||||
import sqlite3
|
||||
from datetime import datetime, timezone
|
||||
import uuid
|
||||
import time
|
||||
import hashlib
|
||||
import hmac
|
||||
import os
|
||||
|
||||
DB_PATH = "instance/crushdb.sqlite3"
|
||||
DB_PATH = os.environ.get("WHO2_DB_PATH", "instance/who2.sqlite")
|
||||
|
||||
RIGHTS = {
|
||||
"view_private": "Can view private articles",
|
||||
"manage_persons": "Can manage persons",
|
||||
"manage_articles": "Can manage articles",
|
||||
"manage_groups": "Can manage groups",
|
||||
"manage_accounts": "Can manage accounts",
|
||||
}
|
||||
|
||||
|
||||
def _connect():
|
||||
conn = sqlite3.connect(DB_PATH)
|
||||
conn.row_factory = sqlite3.Row
|
||||
conn.execute("PRAGMA foreign_keys = ON")
|
||||
return conn
|
||||
ADMIN_RIGHT = "manage_accounts"
|
||||
|
||||
|
||||
def init_db():
|
||||
"""Create tables if they do not exist yet."""
|
||||
import os
|
||||
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
|
||||
with _connect() as conn:
|
||||
conn.executescript(
|
||||
"""
|
||||
CREATE TABLE IF NOT EXISTS records (
|
||||
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||||
uuid TEXT NOT NULL UNIQUE,
|
||||
ciphertext BLOB NOT NULL,
|
||||
nonce BLOB NOT NULL,
|
||||
salt BLOB NOT NULL,
|
||||
version INTEGER NOT NULL DEFAULT 1,
|
||||
created_at TEXT NOT NULL,
|
||||
updated_at TEXT NOT NULL
|
||||
);
|
||||
CREATE INDEX IF NOT EXISTS idx_records_uuid ON records(uuid);
|
||||
"""
|
||||
def _grant_all_rights(conn, account_id: str) -> None:
|
||||
for right_name in RIGHTS:
|
||||
conn.execute(
|
||||
"INSERT OR IGNORE INTO account_rights (account_id, right_name) VALUES (?,?)",
|
||||
(account_id, right_name),
|
||||
)
|
||||
|
||||
|
||||
def bootstrap_admin(account_id: str) -> None:
|
||||
"""Grant all rights to an account so it can manage the system.
|
||||
|
||||
In this model an account has no rights by default, so bootstrap grants a starting
|
||||
set so the first admin can then grant rights to others.
|
||||
"""
|
||||
conn = _conn()
|
||||
try:
|
||||
_grant_all_rights(conn, account_id)
|
||||
conn.commit()
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def is_admin(account: dict | None) -> bool:
|
||||
return has_right(account, ADMIN_RIGHT)
|
||||
|
||||
|
||||
def ensure_admin() -> None:
|
||||
"""Guarantee that at least one account holds the admin right.
|
||||
|
||||
WHO2_ADMIN_EMAIL promotes that account when it exists. Otherwise the oldest
|
||||
account is promoted, so an existing database gets exactly one admin.
|
||||
"""
|
||||
admin_email = (os.environ.get("WHO2_ADMIN_EMAIL") or "").strip()
|
||||
conn = _conn()
|
||||
try:
|
||||
if admin_email:
|
||||
row = conn.execute(
|
||||
"SELECT id FROM accounts WHERE lower(email) = lower(?)", (admin_email,)
|
||||
).fetchone()
|
||||
if row:
|
||||
_grant_all_rights(conn, row["id"])
|
||||
conn.commit()
|
||||
return
|
||||
existing = conn.execute(
|
||||
"SELECT account_id FROM account_rights WHERE right_name = ? LIMIT 1",
|
||||
(ADMIN_RIGHT,),
|
||||
).fetchone()
|
||||
if existing:
|
||||
return
|
||||
# rowid breaks ties between accounts created in the same second, so the
|
||||
# earliest-inserted account is reliably the one that gets promoted.
|
||||
oldest = conn.execute("SELECT id FROM accounts ORDER BY created_at, rowid LIMIT 1").fetchone()
|
||||
if oldest:
|
||||
_grant_all_rights(conn, oldest["id"])
|
||||
conn.commit()
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
ARTICLE_TYPES = [
|
||||
"contact_details",
|
||||
"notes",
|
||||
"profile_text",
|
||||
"documents",
|
||||
"links",
|
||||
"phone_numbers",
|
||||
"addresses",
|
||||
"social_profiles",
|
||||
]
|
||||
|
||||
|
||||
def _now():
|
||||
return datetime.now(timezone.utc).isoformat()
|
||||
return int(time.time())
|
||||
|
||||
|
||||
def _row_to_dict(row):
|
||||
if row is None:
|
||||
return None
|
||||
return {
|
||||
"uuid": row["uuid"],
|
||||
"ciphertext": row["ciphertext"],
|
||||
"nonce": row["nonce"],
|
||||
"salt": row["salt"],
|
||||
"version": row["version"],
|
||||
"created_at": row["created_at"],
|
||||
"updated_at": row["updated_at"],
|
||||
}
|
||||
def _conn():
|
||||
conn = sqlite3.connect(DB_PATH)
|
||||
conn.row_factory = sqlite3.Row
|
||||
conn.execute("PRAGMA journal_mode=WAL")
|
||||
return conn
|
||||
|
||||
|
||||
def list_records():
|
||||
with _connect() as conn:
|
||||
rows = conn.execute(
|
||||
"SELECT * FROM records ORDER BY id ASC"
|
||||
).fetchall()
|
||||
return [_row_to_dict(r) for r in rows]
|
||||
def _hash_password(password: str, salt: str) -> str:
|
||||
key = (salt + password).encode("utf-8")
|
||||
return hashlib.sha256(key).hexdigest()
|
||||
|
||||
|
||||
def get_record(uuid):
|
||||
with _connect() as conn:
|
||||
row = conn.execute(
|
||||
"SELECT * FROM records WHERE uuid = ?", (uuid,)
|
||||
).fetchone()
|
||||
return _row_to_dict(row)
|
||||
|
||||
|
||||
def create_record(uuid, ciphertext, nonce, salt):
|
||||
"""Insert a new encrypted record. Returns the stored dict with server metadata."""
|
||||
now = _now()
|
||||
with _connect() as conn:
|
||||
cur = conn.execute(
|
||||
def init_db():
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.executescript(
|
||||
"""
|
||||
CREATE TABLE IF NOT EXISTS accounts (
|
||||
id TEXT PRIMARY KEY,
|
||||
email TEXT NOT NULL UNIQUE,
|
||||
password_hash TEXT NOT NULL,
|
||||
password_salt TEXT NOT NULL,
|
||||
created_at INTEGER NOT NULL
|
||||
);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS persons (
|
||||
id TEXT PRIMARY KEY,
|
||||
name TEXT NOT NULL DEFAULT '',
|
||||
created_at INTEGER NOT NULL
|
||||
);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS person_accounts (
|
||||
person_id TEXT NOT NULL,
|
||||
account_id TEXT NOT NULL,
|
||||
PRIMARY KEY (person_id, account_id),
|
||||
FOREIGN KEY (person_id) REFERENCES persons(id),
|
||||
FOREIGN KEY (account_id) REFERENCES accounts(id)
|
||||
);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS articles (
|
||||
id TEXT PRIMARY KEY,
|
||||
person_id TEXT NOT NULL,
|
||||
article_type TEXT NOT NULL CHECK(article_type IN (
|
||||
'contact_details','notes','profile_text','documents',
|
||||
'links','phone_numbers','addresses','social_profiles'
|
||||
)),
|
||||
title TEXT NOT NULL DEFAULT '',
|
||||
content TEXT NOT NULL DEFAULT '',
|
||||
visibility TEXT NOT NULL DEFAULT 'private'
|
||||
CHECK(visibility IN ('private','group','public')),
|
||||
created_at INTEGER NOT NULL,
|
||||
updated_at INTEGER NOT NULL,
|
||||
FOREIGN KEY (person_id) REFERENCES persons(id)
|
||||
);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS groups (
|
||||
id TEXT PRIMARY KEY,
|
||||
person_id TEXT NOT NULL,
|
||||
name TEXT NOT NULL DEFAULT '',
|
||||
created_at INTEGER NOT NULL,
|
||||
FOREIGN KEY (person_id) REFERENCES persons(id)
|
||||
);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS group_members (
|
||||
group_id TEXT NOT NULL,
|
||||
person_id TEXT NOT NULL,
|
||||
PRIMARY KEY (group_id, person_id),
|
||||
FOREIGN KEY (group_id) REFERENCES groups(id),
|
||||
FOREIGN KEY (person_id) REFERENCES persons(id)
|
||||
);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS account_rights (
|
||||
account_id TEXT NOT NULL,
|
||||
right_name TEXT NOT NULL,
|
||||
PRIMARY KEY (account_id, right_name),
|
||||
FOREIGN KEY (account_id) REFERENCES accounts(id)
|
||||
);
|
||||
|
||||
CREATE TABLE IF NOT EXISTS article_group_access (
|
||||
article_id TEXT NOT NULL,
|
||||
group_id TEXT NOT NULL,
|
||||
PRIMARY KEY (article_id, group_id),
|
||||
FOREIGN KEY (article_id) REFERENCES articles(id),
|
||||
FOREIGN KEY (group_id) REFERENCES groups(id)
|
||||
);
|
||||
|
||||
CREATE INDEX IF NOT EXISTS idx_articles_person ON articles(person_id);
|
||||
CREATE INDEX IF NOT EXISTS idx_group_members_group ON group_members(group_id);
|
||||
CREATE INDEX IF NOT EXISTS idx_article_group_access_article ON article_group_access(article_id);
|
||||
"""
|
||||
INSERT INTO records (uuid, ciphertext, nonce, salt, version, created_at, updated_at)
|
||||
VALUES (?, ?, ?, ?, 1, ?, ?)
|
||||
""",
|
||||
(uuid, ciphertext, nonce, salt, now, now),
|
||||
)
|
||||
row_id = cur.lastrowid
|
||||
row = conn.execute("SELECT * FROM records WHERE id = ?", (row_id,)).fetchone()
|
||||
return _row_to_dict(row)
|
||||
conn.commit()
|
||||
finally:
|
||||
conn.close()
|
||||
ensure_admin()
|
||||
|
||||
|
||||
def update_record(uuid, ciphertext, nonce, salt, expected_version):
|
||||
"""Replace ciphertext only if the client's version matches the stored one.
|
||||
def create_account(email: str, password: str) -> dict:
|
||||
if not email or "@" not in email:
|
||||
raise ValueError("Invalid email address")
|
||||
if not password:
|
||||
raise ValueError("Password required")
|
||||
salt = uuid.uuid4().hex
|
||||
password_hash = _hash_password(password, salt)
|
||||
account_id = uuid.uuid4().hex
|
||||
conn = _conn()
|
||||
try:
|
||||
is_first = conn.execute("SELECT COUNT(*) AS c FROM accounts").fetchone()["c"] == 0
|
||||
conn.execute(
|
||||
"INSERT INTO accounts (id, email, password_hash, password_salt, created_at) VALUES (?,?,?,?,?)",
|
||||
(account_id, email, password_hash, salt, _now()),
|
||||
)
|
||||
if is_first:
|
||||
# The very first account becomes the bootstrap admin.
|
||||
_grant_all_rights(conn, account_id)
|
||||
conn.commit()
|
||||
except sqlite3.IntegrityError as e:
|
||||
raise ValueError("An account with this email already exists") from e
|
||||
finally:
|
||||
conn.close()
|
||||
return get_account_by_id(account_id)
|
||||
|
||||
Returns the updated record dict, or None if:
|
||||
- no record with that uuid exists
|
||||
- expected_version does not match (someone else updated / replay attack)
|
||||
|
||||
def authenticate(email: str, password: str) -> dict | None:
|
||||
conn = _conn()
|
||||
try:
|
||||
row = conn.execute(
|
||||
"SELECT id, email, password_hash, password_salt FROM accounts WHERE email = ?",
|
||||
(email,),
|
||||
).fetchone()
|
||||
if not row:
|
||||
return None
|
||||
expected = _hash_password(password, row["password_salt"])
|
||||
if not hmac.compare_digest(expected, row["password_hash"]):
|
||||
return None
|
||||
rights = [
|
||||
r["right_name"] for r in conn.execute(
|
||||
"SELECT right_name FROM account_rights WHERE account_id = ?",
|
||||
(row["id"],),
|
||||
).fetchall()
|
||||
]
|
||||
person = conn.execute(
|
||||
"SELECT person_id FROM person_accounts WHERE account_id = ? ORDER BY person_id LIMIT 1",
|
||||
(row["id"],),
|
||||
).fetchone()
|
||||
return {
|
||||
"id": row["id"],
|
||||
"email": row["email"],
|
||||
"rights": rights,
|
||||
"person_id": person["person_id"] if person else None,
|
||||
"is_admin": ADMIN_RIGHT in rights,
|
||||
}
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def has_right(account: dict | None, right: str) -> bool:
|
||||
if account is None:
|
||||
return False
|
||||
if right in account.get("rights", []):
|
||||
return True
|
||||
if "id" in account:
|
||||
conn = _conn()
|
||||
try:
|
||||
row = conn.execute(
|
||||
"SELECT 1 FROM account_rights WHERE account_id = ? AND right_name = ?",
|
||||
(account["id"], right),
|
||||
).fetchone()
|
||||
return row is not None
|
||||
finally:
|
||||
conn.close()
|
||||
return False
|
||||
|
||||
|
||||
def create_person(account: dict | None, name: str = "") -> dict:
|
||||
"""Create a person. Admins only; persons are never auto-linked to the creator."""
|
||||
if account is None or not has_right(account, "manage_persons"):
|
||||
raise PermissionError("Only admins can create persons")
|
||||
if name is None:
|
||||
name = ""
|
||||
person_id = uuid.uuid4().hex
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute(
|
||||
"INSERT INTO persons (id, name, created_at) VALUES (?,?,?)",
|
||||
(person_id, name, _now()),
|
||||
)
|
||||
conn.commit()
|
||||
return {"id": person_id, "name": name}
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def _find_account_by_email(email: str) -> dict | None:
|
||||
conn = _conn()
|
||||
try:
|
||||
row = conn.execute(
|
||||
"SELECT id, email FROM accounts WHERE lower(email) = lower(?)", (email,)
|
||||
).fetchone()
|
||||
if not row:
|
||||
return None
|
||||
return {"id": row["id"], "email": row["email"]}
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def link_account_to_person(account: dict | None, person_id: str, account_id: str) -> dict | None:
|
||||
"""Link an account (email) to a person. Admins only.
|
||||
|
||||
The specification allows several accounts per person but only one person per
|
||||
account, so an account already linked elsewhere is rejected.
|
||||
"""
|
||||
with _connect() as conn:
|
||||
if account is None or not has_right(account, "manage_persons"):
|
||||
raise PermissionError("Only admins can link accounts to persons")
|
||||
conn = _conn()
|
||||
try:
|
||||
linked = conn.execute(
|
||||
"SELECT person_id FROM person_accounts WHERE account_id = ?", (account_id,)
|
||||
).fetchall()
|
||||
for row in linked:
|
||||
if row["person_id"] != person_id:
|
||||
raise ValueError("This email is already linked to another person")
|
||||
conn.execute(
|
||||
"INSERT OR IGNORE INTO person_accounts (person_id, account_id) VALUES (?,?)",
|
||||
(person_id, account_id),
|
||||
)
|
||||
conn.commit()
|
||||
return get_person(person_id)
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def create_person_with_account(account: dict | None, name: str = "",
|
||||
email: str | None = None, password: str | None = None) -> dict:
|
||||
"""Admin flow: create a person and optionally give them an email address.
|
||||
|
||||
If the email has no account yet, an account is created for it (a password is
|
||||
then required). Otherwise the existing account is linked to the new person.
|
||||
"""
|
||||
if account is None or not has_right(account, "manage_persons"):
|
||||
raise PermissionError("Only admins can create persons")
|
||||
person = create_person(account, name)
|
||||
email = (email or "").strip()
|
||||
if not email:
|
||||
return get_person(person["id"])
|
||||
target = _find_account_by_email(email)
|
||||
if target is None:
|
||||
if not password:
|
||||
raise ValueError("A password is required to create the account for " + email)
|
||||
target = create_account(email, password)
|
||||
link_account_to_person(account, person["id"], target["id"])
|
||||
return get_person(person["id"])
|
||||
|
||||
|
||||
def list_persons(account: dict | None) -> list[dict]:
|
||||
show_emails = is_admin(account)
|
||||
conn = _conn()
|
||||
try:
|
||||
rows = conn.execute("SELECT id, name, created_at FROM persons ORDER BY name, id").fetchall()
|
||||
out = []
|
||||
for r in rows:
|
||||
emails = []
|
||||
if show_emails:
|
||||
emails = [
|
||||
a["email"] for a in conn.execute(
|
||||
"SELECT a.email FROM accounts a JOIN person_accounts pa ON pa.account_id = a.id WHERE pa.person_id = ?",
|
||||
(r["id"],),
|
||||
).fetchall()
|
||||
]
|
||||
out.append({
|
||||
"id": r["id"],
|
||||
"name": r["name"],
|
||||
"emails": sorted(set(emails)),
|
||||
"created_at": r["created_at"],
|
||||
})
|
||||
return out
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def get_person(person_id: str) -> dict | None:
|
||||
conn = _conn()
|
||||
try:
|
||||
row = conn.execute("SELECT id, name, created_at FROM persons WHERE id = ?", (person_id,)).fetchone()
|
||||
if not row:
|
||||
return None
|
||||
person_accounts = [
|
||||
a["email"] for a in conn.execute(
|
||||
"SELECT a.email FROM accounts a JOIN person_accounts pa ON pa.account_id = a.id WHERE pa.person_id = ?",
|
||||
(person_id,),
|
||||
).fetchall()
|
||||
]
|
||||
groups = [
|
||||
{"id": g["id"], "name": g["name"]}
|
||||
for g in conn.execute("SELECT id, name FROM groups WHERE person_id = ?", (person_id,)).fetchall()
|
||||
]
|
||||
return {
|
||||
"id": row["id"],
|
||||
"name": row["name"],
|
||||
"emails": sorted(set(person_accounts)),
|
||||
"groups": groups,
|
||||
"created_at": row["created_at"],
|
||||
}
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def update_person(account: dict | None, person_id: str, name: str | None = None) -> dict | None:
|
||||
person = get_person(person_id)
|
||||
if person is None:
|
||||
return None
|
||||
conn = _conn()
|
||||
try:
|
||||
if name is not None:
|
||||
conn.execute("UPDATE persons SET name = ? WHERE id = ?", (name if name is not None else "", person_id))
|
||||
conn.commit()
|
||||
return get_person(person_id)
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def delete_person(account: dict | None, person_id: str) -> bool:
|
||||
person = get_person(person_id)
|
||||
if person is None:
|
||||
return False
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute("DELETE FROM articles WHERE person_id = ?", (person_id,))
|
||||
conn.execute("DELETE FROM article_group_access WHERE article_id IN (SELECT id FROM articles WHERE person_id = ?)", (person_id,))
|
||||
conn.execute("DELETE FROM group_members WHERE group_id IN (SELECT id FROM groups WHERE person_id = ?)", (person_id,))
|
||||
conn.execute("DELETE FROM groups WHERE person_id = ?", (person_id,))
|
||||
conn.execute("DELETE FROM person_accounts WHERE person_id = ?", (person_id,))
|
||||
conn.execute("DELETE FROM persons WHERE id = ?", (person_id,))
|
||||
conn.commit()
|
||||
return True
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def create_article(account: dict, person_id: str, article_type: str, title: str, content: str,
|
||||
visibility: str = "private", group_ids: list[str] | None = None) -> dict:
|
||||
if article_type not in ARTICLE_TYPES:
|
||||
raise ValueError("Invalid article type")
|
||||
if visibility not in ("private", "group", "public"):
|
||||
raise ValueError("Invalid visibility")
|
||||
if account is None:
|
||||
raise PermissionError("Not authenticated")
|
||||
if not has_right(account, "manage_articles"):
|
||||
if article_type != "notes":
|
||||
raise PermissionError("Normal users can only add notes")
|
||||
if person_id != account.get("person_id"):
|
||||
raise PermissionError("You can only add notes to your own person")
|
||||
article_id = uuid.uuid4().hex
|
||||
now = _now()
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute(
|
||||
"INSERT INTO articles (id, person_id, article_type, title, content, visibility, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?)",
|
||||
(article_id, person_id, article_type, title, content, visibility, now, now),
|
||||
)
|
||||
if group_ids:
|
||||
for gid in group_ids:
|
||||
conn.execute("INSERT INTO article_group_access (article_id, group_id) VALUES (?,?)", (article_id, gid))
|
||||
conn.commit()
|
||||
return get_article(article_id)
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def get_article(article_id: str) -> dict | None:
|
||||
conn = _conn()
|
||||
try:
|
||||
row = conn.execute("SELECT * FROM articles WHERE id = ?", (article_id,)).fetchone()
|
||||
if not row:
|
||||
return None
|
||||
allowed_groups = [
|
||||
g["id"] for g in conn.execute(
|
||||
"SELECT g.id FROM groups g JOIN article_group_access aga ON aga.group_id = g.id WHERE aga.article_id = ?",
|
||||
(article_id,),
|
||||
).fetchall()
|
||||
]
|
||||
return {
|
||||
"id": row["id"],
|
||||
"person_id": row["person_id"],
|
||||
"article_type": row["article_type"],
|
||||
"title": row["title"],
|
||||
"content": row["content"],
|
||||
"visibility": row["visibility"],
|
||||
"created_at": row["created_at"],
|
||||
"updated_at": row["updated_at"],
|
||||
"allowed_groups": allowed_groups,
|
||||
}
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def list_articles(account: dict | None, person_id: str | None = None) -> list[dict]:
|
||||
conn = _conn()
|
||||
try:
|
||||
query = "SELECT a.* FROM articles a"
|
||||
params: list = []
|
||||
if person_id:
|
||||
query += " WHERE a.person_id = ?"
|
||||
params.append(person_id)
|
||||
query += " ORDER BY a.updated_at DESC, a.created_at DESC"
|
||||
rows = conn.execute(query, params).fetchall()
|
||||
out = []
|
||||
for r in rows:
|
||||
article = {
|
||||
"id": r["id"],
|
||||
"person_id": r["person_id"],
|
||||
"article_type": r["article_type"],
|
||||
"title": r["title"],
|
||||
"visibility": r["visibility"],
|
||||
"created_at": r["created_at"],
|
||||
"updated_at": r["updated_at"],
|
||||
}
|
||||
allowed_groups = [
|
||||
g["id"] for g in conn.execute(
|
||||
"SELECT g.id FROM groups g JOIN article_group_access aga ON aga.group_id = g.id WHERE aga.article_id = ?",
|
||||
(r["id"],),
|
||||
).fetchall()
|
||||
]
|
||||
article["allowed_groups"] = allowed_groups
|
||||
if _can_read_article(account, article):
|
||||
article["content"] = r["content"]
|
||||
else:
|
||||
article["content"] = ""
|
||||
out.append(article)
|
||||
return out
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def _can_read_article(account: dict | None, article: dict) -> bool:
|
||||
if article["visibility"] == "public":
|
||||
return True
|
||||
if account is None:
|
||||
return False
|
||||
# You always see the articles of your own person.
|
||||
if article["person_id"] == account.get("person_id"):
|
||||
return True
|
||||
if article["visibility"] == "group":
|
||||
for gid in article.get("allowed_groups", []):
|
||||
if _account_in_group(account, gid):
|
||||
return True
|
||||
return False
|
||||
if article["visibility"] == "private":
|
||||
return has_right(account, "view_private") and article["person_id"] == account.get("person_id")
|
||||
return False
|
||||
|
||||
|
||||
def _account_in_group(account: dict, group_id: str) -> bool:
|
||||
conn = _conn()
|
||||
try:
|
||||
row = conn.execute(
|
||||
"SELECT version FROM records WHERE uuid = ?", (uuid,)
|
||||
"SELECT 1 FROM group_members gm JOIN person_accounts pa ON pa.person_id = gm.person_id WHERE gm.group_id = ? AND pa.account_id = ?",
|
||||
(group_id, account["id"]),
|
||||
).fetchone()
|
||||
if row is None or row["version"] != expected_version:
|
||||
return None
|
||||
cur = conn.execute(
|
||||
"""
|
||||
UPDATE records
|
||||
SET ciphertext = ?, nonce = ?, salt = ?, version = version + 1, updated_at = ?
|
||||
WHERE uuid = ? AND version = ?
|
||||
""",
|
||||
(ciphertext, nonce, salt, _now(), uuid, expected_version),
|
||||
)
|
||||
if cur.rowcount == 0:
|
||||
return None
|
||||
row = conn.execute("SELECT * FROM records WHERE uuid = ?", (uuid,)).fetchone()
|
||||
return _row_to_dict(row)
|
||||
return row is not None
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def delete_record(uuid, expected_version):
|
||||
"""Delete a record only if the client's version matches. Returns True on success."""
|
||||
with _connect() as conn:
|
||||
cur = conn.execute(
|
||||
"DELETE FROM records WHERE uuid = ? AND version = ?",
|
||||
(uuid, expected_version),
|
||||
)
|
||||
return cur.rowcount > 0
|
||||
def update_article(account: dict, article_id: str, title: str | None = None,
|
||||
content: str | None = None, visibility: str | None = None,
|
||||
group_ids: list[str] | None = None) -> dict | None:
|
||||
a = get_article(article_id)
|
||||
if a is None:
|
||||
return None
|
||||
if a["person_id"] != account.get("person_id") and not has_right(account, "manage_articles"):
|
||||
raise PermissionError("Not allowed to edit this article")
|
||||
conn = _conn()
|
||||
try:
|
||||
if title is not None:
|
||||
conn.execute("UPDATE articles SET title = ? WHERE id = ?", (title, article_id))
|
||||
if content is not None:
|
||||
conn.execute("UPDATE articles SET content = ? WHERE id = ?", (content, article_id))
|
||||
if visibility is not None:
|
||||
conn.execute("UPDATE articles SET visibility = ? WHERE id = ?", (visibility, article_id))
|
||||
conn.execute("DELETE FROM article_group_access WHERE article_id = ?", (article_id,))
|
||||
if group_ids is not None:
|
||||
conn.execute("DELETE FROM article_group_access WHERE article_id = ?", (article_id,))
|
||||
for gid in group_ids:
|
||||
conn.execute("INSERT INTO article_group_access (article_id, group_id) VALUES (?,?)", (article_id, gid))
|
||||
conn.execute("UPDATE articles SET updated_at = ? WHERE id = ?", (_now(), article_id))
|
||||
conn.commit()
|
||||
return get_article(article_id)
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def delete_article(account: dict, article_id: str) -> bool:
|
||||
a = get_article(article_id)
|
||||
if a is None:
|
||||
return False
|
||||
if a["person_id"] != account.get("person_id") and not has_right(account, "manage_articles"):
|
||||
raise PermissionError("Not allowed to delete this article")
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute("DELETE FROM article_group_access WHERE article_id = ?", (article_id,))
|
||||
conn.execute("DELETE FROM articles WHERE id = ?", (article_id,))
|
||||
conn.commit()
|
||||
return True
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def create_group(account: dict, person_id: str, name: str = "") -> dict:
|
||||
if not has_right(account, "manage_groups") and account.get("person_id") != person_id:
|
||||
raise PermissionError("Not allowed to create groups for this person")
|
||||
group_id = uuid.uuid4().hex
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute("INSERT INTO groups (id, person_id, name, created_at) VALUES (?,?,?,?)",
|
||||
(group_id, person_id, name, _now()))
|
||||
conn.commit()
|
||||
return {"id": group_id, "person_id": person_id, "name": name}
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def list_groups(account: dict | None, person_id: str | None = None) -> list[dict]:
|
||||
conn = _conn()
|
||||
try:
|
||||
query = "SELECT id, person_id, name, created_at FROM groups"
|
||||
params: list = []
|
||||
if person_id:
|
||||
query += " WHERE person_id = ?"
|
||||
params.append(person_id)
|
||||
query += " ORDER BY name, id"
|
||||
rows = conn.execute(query, params).fetchall()
|
||||
visible = _visible_group_ids(conn, account)
|
||||
out = []
|
||||
for r in rows:
|
||||
if visible is not None and r["id"] not in visible:
|
||||
continue
|
||||
members = get_group_members(r["id"])
|
||||
out.append({
|
||||
"id": r["id"],
|
||||
"person_id": r["person_id"],
|
||||
"name": r["name"],
|
||||
"created_at": r["created_at"],
|
||||
"members": members,
|
||||
})
|
||||
return out
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def _visible_group_ids(conn, account: dict | None):
|
||||
"""Group ids the account may see, or None for unrestricted (admins)."""
|
||||
if account is None:
|
||||
return set()
|
||||
if has_right(account, "manage_groups"):
|
||||
return None
|
||||
person_id = account.get("person_id")
|
||||
if not person_id:
|
||||
return set()
|
||||
ids = {row["id"] for row in conn.execute("SELECT id FROM groups WHERE person_id = ?", (person_id,)).fetchall()}
|
||||
ids |= {row["group_id"] for row in conn.execute("SELECT group_id FROM group_members WHERE person_id = ?", (person_id,)).fetchall()}
|
||||
return ids
|
||||
|
||||
|
||||
def group_is_visible_to(account: dict | None, group_id: str) -> bool:
|
||||
conn = _conn()
|
||||
try:
|
||||
visible = _visible_group_ids(conn, account)
|
||||
return visible is None or group_id in visible
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def get_group(group_id: str) -> dict | None:
|
||||
conn = _conn()
|
||||
try:
|
||||
row = conn.execute("SELECT id, person_id, name, created_at FROM groups WHERE id = ?", (group_id,)).fetchone()
|
||||
if not row:
|
||||
return None
|
||||
return {
|
||||
"id": row["id"],
|
||||
"person_id": row["person_id"],
|
||||
"name": row["name"],
|
||||
"created_at": row["created_at"],
|
||||
"members": get_group_members(group_id),
|
||||
}
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def get_group_members(group_id: str) -> list[dict]:
|
||||
conn = _conn()
|
||||
try:
|
||||
rows = conn.execute(
|
||||
"SELECT p.id, p.name FROM persons p JOIN group_members gm ON gm.person_id = p.id WHERE gm.group_id = ?",
|
||||
(group_id,),
|
||||
).fetchall()
|
||||
return [{"id": r["id"], "name": r["name"]} for r in rows]
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def add_group_member(account: dict, group_id: str, person_id: str) -> dict | None:
|
||||
group = get_group(group_id)
|
||||
if group is None:
|
||||
return None
|
||||
if account is None:
|
||||
raise PermissionError("Not allowed to manage this group")
|
||||
allowed = has_right(account, "manage_groups") or group["person_id"] == account.get("person_id")
|
||||
if not allowed and account.get("person_id"):
|
||||
conn = _conn()
|
||||
try:
|
||||
allowed = conn.execute(
|
||||
"SELECT 1 FROM group_members WHERE group_id = ? AND person_id = ?",
|
||||
(group_id, account["person_id"]),
|
||||
).fetchone() is not None
|
||||
finally:
|
||||
conn.close()
|
||||
if not allowed:
|
||||
raise PermissionError("You can only invite people to groups you own or belong to")
|
||||
person = get_person(person_id)
|
||||
if person is None:
|
||||
raise ValueError("Person not found")
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute("INSERT OR IGNORE INTO group_members (group_id, person_id) VALUES (?,?)", (group_id, person_id))
|
||||
conn.commit()
|
||||
return get_group(group_id)
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def remove_group_member(account: dict, group_id: str, person_id: str) -> dict | None:
|
||||
group = get_group(group_id)
|
||||
if group is None:
|
||||
return None
|
||||
if group["person_id"] != account.get("person_id") and not has_right(account, "manage_groups"):
|
||||
raise PermissionError("Not allowed to manage this group")
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute("DELETE FROM group_members WHERE group_id = ? AND person_id = ?", (group_id, person_id))
|
||||
conn.commit()
|
||||
return get_group(group_id)
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def update_group(account: dict, group_id: str, name: str | None = None) -> dict | None:
|
||||
group = get_group(group_id)
|
||||
if group is None:
|
||||
return None
|
||||
if group["person_id"] != account.get("person_id") and not has_right(account, "manage_groups"):
|
||||
raise PermissionError("Not allowed to manage this group")
|
||||
conn = _conn()
|
||||
try:
|
||||
if name is not None:
|
||||
conn.execute("UPDATE groups SET name = ? WHERE id = ?", (name, group_id))
|
||||
conn.commit()
|
||||
return get_group(group_id)
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def delete_group(account: dict, group_id: str) -> bool:
|
||||
group = get_group(group_id)
|
||||
if group is None:
|
||||
return False
|
||||
if group["person_id"] != account.get("person_id") and not has_right(account, "manage_groups"):
|
||||
raise PermissionError("Not allowed to manage this group")
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute("DELETE FROM article_group_access WHERE group_id = ?", (group_id,))
|
||||
conn.execute("DELETE FROM group_members WHERE group_id = ?", (group_id,))
|
||||
conn.execute("DELETE FROM groups WHERE id = ?", (group_id,))
|
||||
conn.commit()
|
||||
return True
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def grant_right(account: dict, target_account_id: str, right_name: str):
|
||||
if not has_right(account, "manage_accounts"):
|
||||
raise PermissionError("Not allowed to manage account rights")
|
||||
if right_name not in RIGHTS:
|
||||
raise ValueError("Unknown right")
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute("INSERT OR IGNORE INTO account_rights (account_id, right_name) VALUES (?,?)",
|
||||
(target_account_id, right_name))
|
||||
conn.commit()
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def revoke_right(account: dict, target_account_id: str, right_name: str):
|
||||
if not has_right(account, "manage_accounts"):
|
||||
raise PermissionError("Not allowed to manage account rights")
|
||||
conn = _conn()
|
||||
try:
|
||||
conn.execute("DELETE FROM account_rights WHERE account_id = ? AND right_name = ?",
|
||||
(target_account_id, right_name))
|
||||
conn.commit()
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def list_rights(account: dict | None, account_id: str | None = None) -> list[dict]:
|
||||
if account is None or not has_right(account, "manage_accounts"):
|
||||
raise PermissionError("Only admins can view account rights")
|
||||
conn = _conn()
|
||||
try:
|
||||
query = "SELECT a.id, a.email, ar.right_name FROM accounts a LEFT JOIN account_rights ar ON ar.account_id = a.id"
|
||||
params: list = []
|
||||
if account_id:
|
||||
query += " WHERE a.id = ?"
|
||||
params.append(account_id)
|
||||
query += " ORDER BY a.email, a.id, ar.right_name"
|
||||
rows = conn.execute(query, params).fetchall()
|
||||
out: dict = {}
|
||||
for r in rows:
|
||||
key = r["id"]
|
||||
if key not in out:
|
||||
out[key] = {"id": r["id"], "email": r["email"], "rights": []}
|
||||
if r["right_name"]:
|
||||
out[key]["rights"].append(r["right_name"])
|
||||
return list(out.values())
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def attach_email_to_person(account: dict, person_id: str, email: str) -> dict | None:
|
||||
if account is None or not has_right(account, "manage_persons"):
|
||||
raise PermissionError("Only admins can change a person's emails")
|
||||
target = _find_account_by_email(email)
|
||||
if not target:
|
||||
raise ValueError("No account exists for " + email)
|
||||
return link_account_to_person(account, person_id, target["id"])
|
||||
|
||||
|
||||
def detach_email_from_person(account: dict, person_id: str, email: str) -> dict | None:
|
||||
if account is None or not has_right(account, "manage_persons"):
|
||||
raise PermissionError("Only admins can change a person's emails")
|
||||
conn = _conn()
|
||||
try:
|
||||
target = conn.execute(
|
||||
"SELECT id FROM accounts WHERE lower(email) = lower(?)", (email,)
|
||||
).fetchone()
|
||||
if not target:
|
||||
raise ValueError("No account exists for " + email)
|
||||
conn.execute("DELETE FROM person_accounts WHERE person_id = ? AND account_id = ?",
|
||||
(person_id, target["id"]))
|
||||
conn.commit()
|
||||
return get_person(person_id)
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
|
||||
def get_account_by_id(account_id: str) -> dict | None:
|
||||
conn = _conn()
|
||||
try:
|
||||
row = conn.execute("SELECT id, email FROM accounts WHERE id = ?", (account_id,)).fetchone()
|
||||
if not row:
|
||||
return None
|
||||
rights = [
|
||||
r["right_name"] for r in conn.execute(
|
||||
"SELECT right_name FROM account_rights WHERE account_id = ?",
|
||||
(account_id,),
|
||||
).fetchall()
|
||||
]
|
||||
person = conn.execute(
|
||||
"SELECT person_id FROM person_accounts WHERE account_id = ? ORDER BY person_id LIMIT 1",
|
||||
(account_id,),
|
||||
).fetchone()
|
||||
return {
|
||||
"id": row["id"],
|
||||
"email": row["email"],
|
||||
"rights": rights,
|
||||
"person_id": person["person_id"] if person else None,
|
||||
"is_admin": ADMIN_RIGHT in rights,
|
||||
}
|
||||
finally:
|
||||
conn.close()
|
||||
|
||||
Reference in New Issue
Block a user