Skip to content

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:

RoleVariableWhat it is
Application roleDATABASE_URLThe role the running application connects as. It must not own the tables and must not have BYPASSRLS.
Schema ownerDATABASE_MIGRATION_URLThe 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 ​

VariableDefaultMeaning
DB_PROVIDERSet to postgres.
DATABASE_URLpostgresql://user:password@host:5432/cognipeer, the application role.
DATABASE_MIGRATION_URLDATABASE_URLThe schema owner that runs migrations.
PG_APP_ROLEuser of DATABASE_URLRole granted table access by the migrations.
PG_POOL_MAX10Connections per process. Size it against the server's max_connections and the number of replicas.
PG_STATEMENT_TIMEOUT_MS60000Time 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_S0Recycle a connection after this many seconds. 0 = never.
DEPLOYMENT_MODEsee belowsaas 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.

Studio · Pulse · Console · Agent SDK and more — the Cognipeer documentation hub