Connect to a managed PostgreSQL database, safely
Create a database, connect from a VM over the private network and from your laptop over TLS, and give each app its own user.
A managed database is only useful once your code can reach it, and only safe if nothing else can. This tutorial sets up a PostgreSQL cluster the way you would want it in production.
Step 1: create the database
Open Databases → Create database, pick PostgreSQL, a version and a size, and put it in the same project as the VMs that will use it. Leave Public endpoint off for now. Name it, for example shop-db, and pick a weekly maintenance window when traffic is low.
bn db options
bn db create shop-db --engine postgres --plan db-2 --wait
It is ready in a few minutes. The Overview shows the host, port, user, password and SSL mode.
Step 2: connect from a VM
VMs in the same project reach the database on its private address, so the traffic never touches the internet. On the VM, install the PostgreSQL client and connect with TLS:
sudo apt install -y postgresql-client
psql "postgresql://bnadmin:<password>@<private host>:5432/defaultdb?sslmode=require"
bn db uri shop-db prints the full connection string, password included, so you can paste it into a config file or environment variable.
Step 3: connect from your laptop
To work from outside ByteNests, turn on the Public endpoint and add your own IP address under Trusted sources. Nothing else on the internet can connect.
bn db allow shop-db 203.0.113.10
psql "$(bn db uri shop-db)"
For full certificate checking, Download CA certificate and use sslmode=verify-full&sslrootcert=ca.pem in the connection string. Remove your address from trusted sources when you no longer need it.
Step 4: one user per app
The bnadmin user can do everything. Apps should not use it. On Users & databases, add a user and a database for each app, for example shop with its own shop database. If an app’s password ever leaks, you reset one user and nothing else breaks.
App Platform does this for you: attach the database when you create the app and it gets its own user and a DATABASE_URL.
Step 5: use the connection pooler
Each PostgreSQL connection uses memory on the server. Frameworks that open many short connections, or apps with many instances, should connect through PgBouncer on port 25061 instead of 5432. bn db uri shop-db --pooled prints that string.
In transaction pooling, session features such as SET for the whole session and some prepared-statement setups behave differently. Check your driver’s notes for PgBouncer.
Backups and recovery
Backups run daily and are kept for 7 days. To recover from a bad migration, use Restore or fork: it builds a new cluster from a backup while the original keeps running, so you can copy back just the data you need.