From c4c17e3e046a627a7d2353c734bc69f05d4fc3b1 Mon Sep 17 00:00:00 2001 From: Dennis Fink Date: Tue, 17 Nov 2026 21:06:05 +0100 Subject: feat(core): add configurable SQLite pragmas Apply validated SQLite settings through SQLAlchemy's connection event so every SQLite connection and bind receives consistent foreign key, WAL, timeout, and security defaults. Allow deployments to override supported PRAGMAs safely while skipping file-only settings for in-memory databases and leaving other database backends untouched. --- webmentions_ssg/config.py | 75 +++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 75 insertions(+) (limited to 'webmentions_ssg/config.py') diff --git a/webmentions_ssg/config.py b/webmentions_ssg/config.py index 28ab43d..be54989 100644 --- a/webmentions_ssg/config.py +++ b/webmentions_ssg/config.py @@ -13,6 +13,25 @@ from .hostname import normalize_hostname MIN_SECRET_KEY_LENGTH = 32 +# Only supported, validated PRAGMAs may be passed to SQLite's connect event. +# None means "leave SQLite's own default unchanged". Override individual +# entries through SQLITE_PRAGMAS in the deployment config. +DEFAULT_SQLITE_PRAGMAS: dict[str, bool | int | str | None] = { + "foreign_keys": True, + "journal_mode": "WAL", + "busy_timeout": 5000, # Milliseconds; matches sqlite3's default timeout. + "trusted_schema": False, + "synchronous": None, # Keep SQLite's durability default unless requested. + "cache_size": None, # Positive = pages; negative = approximate KiB. + "wal_autocheckpoint": None, # Pages; SQLite defaults to 1000. + "journal_size_limit": None, # Bytes; -1 removes the size limit. +} + +SQLITE_JOURNAL_MODES = frozenset({"DELETE", "TRUNCATE", "PERSIST", "WAL"}) +SQLITE_SYNCHRONOUS_MODES = frozenset({"OFF", "NORMAL", "FULL", "EXTRA"}) +SQLITE_INT_MAX = 2**31 - 1 +SQLITE_INT64_MAX = 2**63 - 1 + class DefaultConfig: # Application @@ -34,6 +53,7 @@ class DefaultConfig: # Database SQLALCHEMY_DATABASE_URI = "sqlite:///webmentions-ssg.db" + SQLITE_PRAGMAS = DEFAULT_SQLITE_PRAGMAS # Logging LOG_FILE = "/var/log/webmentions-ssg.log" @@ -194,6 +214,7 @@ def validated_config_overrides( unsafe. """ normalized: dict[str, object] = {} + normalized["SQLITE_PRAGMAS"] = validate_sqlite_pragmas(config["SQLITE_PRAGMAS"]) for key in ("ALLOWED_TARGET_HOSTNAMES", "IGNORED_TARGET_HOSTNAMES"): try: value = config[key] @@ -258,3 +279,57 @@ def validated_config_overrides( "running in production (memory:// and blackhole:// are unsafe)." ) return normalized + + +def validate_sqlite_pragmas(value: object) -> dict[str, bool | int | str | None]: + """Merge user overrides with safe defaults and reject invalid PRAGMAs. + + SQLite doesn't accept bind parameters in PRAGMA assignments. A strict + allowlist and type checks ensure only known values enter the SQL statements. + """ + if not isinstance(value, dict): + raise TypeError("SQLITE_PRAGMAS must be a dictionary.") + + pragmas = DEFAULT_SQLITE_PRAGMAS.copy() + for raw_name, setting in value.items(): + # Flask's from_prefixed_env uses the casing in environment variable + # names, usually upper case. Accept both forms without exposing SQL. + if not isinstance(raw_name, str): + raise ValueError(f"SQLITE_PRAGMAS: unsupported PRAGMA {raw_name!r}.") + + if (name := raw_name.lower()) not in pragmas: + raise ValueError(f"SQLITE_PRAGMAS: unsupported PRAGMA {raw_name!r}.") + + if name in {"foreign_keys", "trusted_schema"}: + if type(setting) is not bool and setting is not None: + raise ValueError(f"SQLITE_PRAGMAS[{name!r}] must be a boolean or None.") + elif name in {"journal_mode", "synchronous"}: + allowed = ( + SQLITE_JOURNAL_MODES + if name == "journal_mode" + else SQLITE_SYNCHRONOUS_MODES + ) + if setting is not None: + if not isinstance(setting, str) or setting.upper() not in allowed: + raise ValueError( + f"SQLITE_PRAGMAS[{name!r}] must be one of " + f"{', '.join(sorted(allowed))}, or None." + ) + setting = setting.upper() + elif setting is not None: + if type(setting) is not int: + raise ValueError( + f"SQLITE_PRAGMAS[{name!r}] must be an integer or None." + ) + if name == "cache_size": + valid = -SQLITE_INT_MAX <= setting <= SQLITE_INT_MAX + elif name == "journal_size_limit": + valid = -1 <= setting <= SQLITE_INT64_MAX + else: + valid = 0 <= setting <= SQLITE_INT_MAX + if not valid: + raise ValueError(f"SQLITE_PRAGMAS[{name!r}] is out of range.") + + pragmas[name] = setting + + return pragmas -- cgit v1.3.1