PostgreSQL
Enterprise edition
PostgreSQL is a database provider of the Enterprise edition. The community edition accepts DB_PROVIDER values sqlite and mongodb only, and refuses postgres at start-up. Contact Cognipeer support before moving an existing deployment to PostgreSQL.
Cognipeer can store its data in SQLite, MongoDB or PostgreSQL, chosen with DB_PROVIDER (sqlite, mongodb or postgres). With PostgreSQL, the whole installation uses one database and one schema; tenants are kept apart by row-level security (RLS) in the database, not by separate databases.
Roles
Use two roles, so that the role the application runs as cannot change or drop the security policies:
| Role | Variable | What it is |
|---|---|---|
| Application role | DATABASE_URL | The role the running application connects as. It must not own the tables and must not have BYPASSRLS. |
| Schema owner | DATABASE_MIGRATION_URL | The role that owns the schema and runs the migrations at start-up. When empty, DATABASE_URL is used: acceptable for development only, because that role owns the policies and could drop them. |
PG_APP_ROLE names the role the migrations grant table access to. When empty it is the user of DATABASE_URL.
Settings
| Variable | Default | Meaning |
|---|---|---|
DB_PROVIDER | Set to postgres. | |
DATABASE_URL | postgresql://user:password@host:5432/cognipeer, the application role. | |
DATABASE_MIGRATION_URL | DATABASE_URL | The schema owner that runs migrations. |
PG_APP_ROLE | user of DATABASE_URL | Role granted table access by the migrations. |
PG_POOL_MAX | 10 | Connections per process. Size it against the server's max_connections and the number of replicas. |
PG_STATEMENT_TIMEOUT_MS | 60000 | Time limit per statement and on every wait for a writer lock, in milliseconds, digits only. 0 turns it off. Migrations at start-up always run without it. |
PG_SSL | (unset) | require (verify the certificate and host), no-verify or disable. When set it is the only TLS policy and the TLS parameters in the URLs are ignored (start-up warns). When unset, the URLs decide. For a private CA with require, use NODE_EXTRA_CA_CERTS. |
PG_MAX_LIFETIME_S | 0 | Recycle a connection after this many seconds. 0 = never. |
DEPLOYMENT_MODE | see below | saas or onprem. |
An invalid value stops the start-up and names the setting.
Set DEPLOYMENT_MODE=onprem when you host it yourself
DEPLOYMENT_MODE defaults to saas for MongoDB and PostgreSQL, and to onprem for SQLite. A self-hosted installation on PostgreSQL should set DEPLOYMENT_MODE=onprem explicitly.
Moving from MongoDB
A copy tool for MongoDB to PostgreSQL exists for a planned cut-over: it is resumable, runs a preflight and verifies the copy. There is no automatic switch: changing DB_PROVIDER on its own does not move any data. Ask Cognipeer support for the cut-over procedure before you plan it.
Related
- Configuration lists every environment variable.
- Installation covers the database choices.
- Monitoring: Diagnostics shows the database provider and the schema version of a running installation.

