Run on managed PostgreSQL
In this guide you run a Hatamoto site on a managed PostgreSQL cluster, where the most privileged account you get is an admin role that is not a superuser. At the end the site migrates, plugins that use their own database work, and backups and restores run with that same admin role.
Before you start
- PostgreSQL 16 or newer, and the connection details of the admin user the provider gives you. It needs the attributes
NOSUPERUSER CREATEROLE CREATEDB. Hatamoto never needs more. - The
psqlclient, and a checkout or download ofdeploy/sql/role-admin-bootstrap.sqlfrom the Hatamoto repository. - If the provider restricts extensions,
citextanduuid-osspmust be allowed. Hatamoto's migrations create both, as the owner of the database, and need nothing else beyond ordinary table rights. - The provider's CA certificate as a file on the Hatamoto host.
Hosts, users and passwords below are examples. Use ~/.pgpass or PGPASSWORD instead of passwords in URLs.
Steps
- Create the application role and the database. The order matters: the admin role must be allowed to create objects in the database, and a database owned by the application role needs that right granted. Connect as the admin role (
admin) to thepostgresdatabase:CREATE ROLE site_app LOGIN PASSWORD 'change-me'; GRANT site_app TO admin WITH SET TRUE; CREATE DATABASE site OWNER site_app;GRANT ... WITH SET TRUElets the admin role create the database on behalf ofsite_app. The membership inherits by default, and that is what gives the admin roleCREATEon the database, with the right to grant it, fromsite_app, the database owner. Keep the membership inheriting if you rely on that (an admin that does not inherit needsCREATE ... WITH GRANT OPTIONgranted to it directly). The bootstrap reads the database id from the core table as the admin role if it can, else assite_app: after the first start onlysite_appcan read that table, so rerunning the bootstrap by hand needsSETonsite_app(theWITH SET TRUEabove), not inheritance. Restoring a backup as the admin role still requires an inheriting membership. - Run the bootstrap once, as the admin role, in the new database, naming the application role. This is the same script a superuser runs, and it is safe to rerun:
psql -X -v ON_ERROR_STOP=1 -v app_role=site_app \ "postgres://admin@db.example.com:5432/site?sslmode=verify-full&sslrootcert=/etc/ssl/provider-ca.pem" \ -f deploy/sql/role-admin-bootstrap.sqlIt creates a role that can create plugin roles and nothing else, and the application role gains no attribute. A managed provider usually does not let the admin role grant
SETon thetemp_file_limitparameter. The script then skips that grant and prints a notice, and plugin roles run without a temporary file size cap. This is not an error. The Data card on the plugin's settings page tells you when the cap is not applied, and the server logs one warning,plugindb: temp_file_limit not applied. - Point Hatamoto at the database as the application role, with verified TLS.
sslmode=verify-fullchecks the certificate and the host name, andsslrootcertnames the CA file:DATABASE_URL="postgres://site_app@db.example.com:5432/site?sslmode=verify-full&sslrootcert=/etc/ssl/provider-ca.pem"The setting reaches the main pool, migrations, plugin pools,
backup createandbackup restore. WithDATABASE_DIRECT_URLset, migrations, plugin pools and backups use that URL instead, with whatever TLS options you put in it (nothing is inherited fromDATABASE_URL, so repeat them). - Connect directly, or through PgBouncer (the next step). A pooler in session mode also works. Transaction mode needs
DATABASE_DIRECT_URL: without it the migration lock and the import lock do not hold and plugin roles cannot log in, so use a direct connection or session mode instead. Other transaction-mode poolers are untested. - Optional: put PgBouncer in front of Postgres. Hatamoto is safe behind PgBouncer in transaction pooling mode (
pool_mode = transaction) when:- PgBouncer is 1.21 or newer and sets
max_prepared_statementsabove 0 (for example100). Hatamoto keeps the default prepared statement mode of its Postgres driver, which needs it. DATABASE_URLpoints at PgBouncer andDATABASE_DIRECT_URLpoints at Postgres itself, with the same database name and user. WithoutDATABASE_DIRECT_URL, everything usesDATABASE_URL, which is only safe on a direct connection or a session-mode pooler. Hatamoto refuses to start if the two URLs name a different user or database.- PgBouncer lists the application role and its password in its
auth_file. - If PgBouncer rejects connection settings the driver sends, add them to
ignore_startup_parameters(extra_float_digitsis the usual one). Ignored parameters, such asoptions=-c ..., are silently dropped for pooled connections.
Work that needs a real Postgres session uses
DATABASE_DIRECT_URL: migrations, the import lock, the attribution backfill lock,backup createandbackup restore, the pre-update backup, and plugin databases. Plugin databases stay off the pooler on purpose, because each plugin logs in as its own role and a pooler cannot be told when a plugin password changes. Hatamoto uses noLISTENorNOTIFYand no other session features on the pooled connections.Count the connections on Postgres itself: PgBouncer's
default_pool_sizefor each database and user pair, plus up to 2 direct connections for every loaded plugin that usesdb(each plugin role is limited to 4), plus 1 for each running import or backfill, plus 1 for a runningpg_dump, plus 2 while migrations run. Postgresmax_connectionsmust cover the sum. Plugin roles must be allowed to log in to Postgres directly (see thepg_hba.confrule in the configuration reference). - PgBouncer is 1.21 or newer and sets
- Size the pools. The main pool keeps
DATABASE_MIN_CONNECTIONS(default 5) connections open and grows toDATABASE_MAX_CONNECTIONS(default 25). Every plugin that usesdbadds a pool of up to 2 connections. With many sites on one cluster, add these up per site and compare the total with the cluster'smax_connections. For small sites, lower both, for exampleDATABASE_MIN_CONNECTIONS=1andDATABASE_MAX_CONNECTIONS=5. To let an unvisited site release its connections (this also turns on the idle back-off, which only runs when the minimum is0), useDATABASE_MIN_CONNECTIONS=0andDATABASE_MAX_CONN_IDLE_TIME=2m: after two minutes without a request the background checks stop running on their own and share one beat every 5 minutes (see Worker and background), so the pool is empty between beats. See the configuration reference. - Plugin roles log in from the Hatamoto host with a password. If the provider controls login rules instead of
pg_hba.conf, make sure the application database accepts password logins for new roles from that host. - Start Hatamoto. It runs its migrations as the application role.
- To restore a backup, give the admin role to the restore command. Create the empty target database in the same order as in step 1, then:
hatamoto backup restore 20261025T101500Z-manual \ --admin-url "postgres://admin@db.example.com:5432/postgres?sslmode=verify-full&sslrootcert=/etc/ssl/provider-ca.pem" \ --config-out /opt/hatamoto/config/hatamoto.envThe command accepts a superuser or a role with
CREATEROLEandCREATEDBthat can create objects in the target database, and refuses anything else before it writes.
Check it worked
Run hatamoto migrate status: every migration is applied. Run curl -s http://127.0.0.1:8090/ready. In psql as the application role, SELECT rolcreaterole FROM pg_roles WHERE rolname = current_user; returns f. Grant a plugin that uses db: it loads without a data_error, and its settings page shows the temporary file note only if the provider refused the grant.
If a plugin answers unavailable and mentions role-admin-bootstrap, step 2 has not run in this database, or ran as a role that lacks CREATEROLE, or CREATE on the database with the grant option (inherited from site_app). Rerun it after fixing the grants.