PostgreSQL
Versions
TotalBase supports PostgreSQL 15, 16, 17 and 18; 18 is recommended for new installations. It checks the major version on every start and refuses any other. Each supported version is tested against its latest minor release. A managed PostgreSQL (Amazon RDS, Cloud SQL, Azure, Supabase, Neon…) works the same way.
The role
TotalBase never needs a superuser. It connects as the role in POSTGRES_USER, and can do exactly what that role can, never more:
TotalBase's capabilities ≤ POSTGRES_USER's capabilities
A change the role isn't allowed to make fails with PostgreSQL's own error. TotalBase has no way around it, by design.
What the role needs:
| To | The role needs |
|---|---|
| Run at all | to connect, and to create the totalbase schema (or own it) |
| Read your data through the API | SELECT (and INSERT, UPDATE, DELETE) on the tables you expose, EXECUTE on functions |
| Create and change tables from the admin | CREATE on those schemas, and to own the tables it changes |
| Run maintenance | to own the tables it vacuums and reindexes |
The simplest setup is a role that owns the database:
CREATE ROLE totalbase LOGIN PASSWORD 'change-me';
CREATE DATABASE acme OWNER totalbase;
Over an existing database whose tables belong to another role, TotalBase reads and writes what it's granted, and shows the tables it doesn't own as such: structure changes and maintenance are off for them. See Tables TotalBase's role doesn't own.
Connection
| Variable | |
|---|---|
POSTGRES_HOST, POSTGRES_PORT, POSTGRES_DB, POSTGRES_USER, POSTGRES_PASSWORD | the connection |
POSTGRES_SSL | off (the default), require (encrypted, certificate not verified) or verify-full (verified) |
POSTGRES_SSL_CA | a PEM file to verify the server's certificate against, with verify-full; else the system's authorities |
A managed PostgreSQL usually wants require or verify-full.
The connection pool (size, timeouts) is a setting: Settings › General › Connector. Keep its maximum under PostgreSQL's max_connections, minus what other clients use. Every statement runs under a timeout (30 s by default, TOTALBASE_STATEMENT_TIMEOUT_MS), so no query runs unbounded.
Extensions
TotalBase needs no extension of its own. Use any your application needs (btree_gist, pg_trgm, postgis…): creating one usually needs a superuser or the database owner, so do it before handing the database to TotalBase, or in the SQL Console if the role may.