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.

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.
| Boundary | Control to verify | Acceptance evidence |
|---|---|---|
| PostgreSQL connection | Restricted network reach and approved authentication with encrypted transport where required | The runtime connects only from planned application hosts with the expected certificate and account policy. |
| PostgreSQL permissions | Separate runtime and migration roles, scoped grants, and row policies where needed | A tenant sees its own synthetic rows and cannot read another tenant's rows or change schema. |
| SQLite on a shared host | Dedicated service identity and permissions for the database directory, side files, and copies | An unrelated local test account cannot read the database or its backup. |
| Backup and recovery | Restricted backup storage and a separately controlled recovery identity | An 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
- 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.
- 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.
- 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.
- 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.