Reading collection

PostgreSQL and SQLite Database Security Controls

Test service roles, transport policy, file access, and backup handling with disposable data and a permission matrix.

Editorial update

Decision in brief

Use separate PostgreSQL roles for application access, migrations, and administration, and test each runtime identity with only the grants it needs. For SQLite, restrict access at the operating-system and application layers, including side files and exported copies. Give backup and restore its own access policy and rehearse it with synthetic data.

Separate database roles: App — Workload access. Migration — Schema changes. Recovery — Own policy.
Test the least access needed with synthetic data. For SQLite, apply access controls to the database, side files and exports.

Match each control to what it protects

PostgreSQL client authentication determines which connections are allowed. Object privileges determine what a connected role can do. Row-level security can constrain table rows for application roles, and owners or roles with row-security bypass require special care. Test through the same identity the application uses.

SQLite data is local to the process and files, so directory permissions must cover the database, journal or WAL side files, and backups. Use parameter binding for values. If a trusted application intentionally supports user-authored SQL against a controlled dataset, consider the SQLite authorizer as an additional way to restrict permitted operations. Keep disk encryption and transport protection separate in your access plan.

BoundaryControl to verifyAcceptance evidence
PostgreSQL connectionRestricted network reach and approved authentication with encrypted transport where requiredThe runtime connects only from planned application hosts with the expected certificate and account policy.
PostgreSQL permissionsSeparate runtime and migration roles, scoped grants, and row policies where neededA tenant sees its own synthetic rows and cannot read another tenant's rows or change schema.
SQLite on a shared hostDedicated service identity and permissions for the database directory, side files, and copiesAn unrelated local test account cannot read the database or its backup.
Backup and recoveryRestricted backup storage and a separately controlled recovery identityAn approved operator can restore a clean copy and application credentials cannot read or delete stored backups.

Two HN cases for checking the boundary

In an Ask HN post, scrubs asked separately about PostgreSQL at-rest protection and TLS key handling. Treat this as two control checks during rollout. Identify what protects traffic and what protects copied database files and backups.

In the Ardent launch discussion, znnajdla pointed out that a copy of live database data can still contain tokens with access to external systems. Generate synthetic integration rows, remove external credentials, and block outbound traffic in the sandbox unless a specific test requires an approved destination.

Run an allow-and-deny test with synthetic accounts

  1. Inventory application, migration, backup, and human operator identities. Create a disposable database with fake tenants, fixture records, and unrelated test users. Write down the operation each identity should be able to perform.
  2. Run the normal application paths and verify the planned denials using the intended runtime role or filesystem identity. Check SQLite backup and side-file readability separately from the main file.
  3. Rotate a test credential and verify legitimate clients reconnect with the new identity. Restore a backup into an isolated environment with integration credentials removed and check the original permission decisions again.
  4. Approve only if all expected actions work, restricted actions are denied, and the owner can restore within target. Stop if the runtime identity needs administrative access for normal requests or if sensitive fixtures appear in logs and exported artifacts.

Related articles