Managed Postgres on one box

I keep starting small projects that need a database. Most of them live on Vercel, most of them are tiny, and paying for or juggling a managed Postgres per project stopped making sense once I had a VPS sitting there doing very little. What I wanted from Neon was never branching or autoscaling. It was the button: make a database, hand me a connection string, keep it away from my other databases.

So I built basin, a control plane for one Postgres instance. This post is about the decisions underneath it, because almost all of them are Postgres and PgBouncer settings rather than code.

a project is a database and a role

The unit of isolation is the cheapest one Postgres has. Creating a project runs, in order:

CREATE ROLE myapp LOGIN PASSWORD '…' CONNECTION LIMIT 20;
CREATE DATABASE myapp OWNER myapp;
REVOKE CONNECT ON DATABASE myapp FROM PUBLIC;
GRANT CONNECT ON DATABASE myapp TO myapp;

The role owns its database, so the app can migrate, create extensions it’s allowed to and generally behave as if it had a server of its own. Revoking CONNECT from PUBLIC matters more than it looks: by default every role can connect to every database, and that’s the wall that keeps one project’s credentials from opening another’s.

CONNECTION LIMIT is the other half. A serverless app with a leak can open connections until the server falls over. With a limit per role, the worst it can do is exhaust its own share, and the dashboard shows each project’s usage against that limit so I see it coming.

If creating the database fails, the role is dropped again, so a half-made project never lingers. Deleting does the reverse: terminate the project’s backends, hand the database back to the control plane’s role, drop the database, drop the role.

the control plane isn’t a superuser

The API connects as console_admin, a role with CREATEDB and CREATEROLE and nothing else (plus pg_signal_backend, so it can end a project’s connections before dropping it). If the dashboard is ever compromised, the attacker can make and drop project databases, which is bad, but they can’t read pg_authid or touch the rest of the cluster.

This is where Postgres 16 bit me. Since 16, a CREATEROLE user doesn’t automatically get to act as the roles it creates. So CREATE DATABASE myapp OWNER myapp fails with:

ERROR:  must be able to SET ROLE "myapp"

The fix is one setting, which makes Postgres grant the creating role SET and INHERIT on every role it creates:

ALTER ROLE console_admin SET createrole_self_grant = 'set, inherit';

It’s a sensible change, closing a real privilege hole, and it’s easy to miss if your mental model of CREATEROLE is from before 16.

one pooler for every project

Serverless functions open a lot of short connections, so everything goes through PgBouncer in transaction mode. The obvious way to set that up is a line per database in pgbouncer.ini and a line per user in userlist.txt, which would mean the control plane editing config files and reloading the pooler on every create. Instead:

[databases]
* = host=127.0.0.1 port=5432

[pgbouncer]
pool_mode = transaction
auth_type = scram-sha-256
auth_user = pgbouncer
auth_query = SELECT username, password FROM pgbouncer.get_auth($1)

The * entry forwards any database name to Postgres. auth_query makes PgBouncer ask Postgres for a user’s SCRAM verifier when they log in, so the only user it needs to know about in advance is its own. The lookup goes through a SECURITY DEFINER function that returns a single login role’s verifier, and only the pgbouncer role may call it:

CREATE FUNCTION pgbouncer.get_auth(p_usename text)
RETURNS TABLE(username text, password text)
LANGUAGE sql SECURITY DEFINER SET search_path = pg_catalog AS $$
  SELECT rolname::text, rolpassword::text
  FROM pg_authid WHERE rolname = p_usename AND rolcanlogin
$$;
REVOKE ALL ON FUNCTION pgbouncer.get_auth(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION pgbouncer.get_auth(text) TO pgbouncer;

The result is that the pooler never changes. A project works through it the moment its role exists, and a rotated password works on the next connection.

TLS you can verify

The public Postgres port is firewalled shut. The only way in from outside is PgBouncer on 6543, with client_tls_sslmode = require, and every connection string basin hands out ends in sslmode=verify-full. That means the client checks the certificate against the hostname, which needs a real certificate, not a self-signed one.

Caddy already gets Let’s Encrypt certificates for the dashboard, so it gets one for the database hostname too. A systemd timer copies that cert into PgBouncer’s directory once a day and reloads the pooler only if it changed. On a fresh server PgBouncer starts on a self-signed placeholder until the first real cert lands.

passwords are shown once

basin never stores a project’s password. It’s generated, set on the role, shown in the dashboard once, and forgotten. Postgres keeps only the SCRAM verifier. If I lose one, the answer is a rotation, which is an ALTER ROLE … PASSWORD and takes effect on the next connection.

Backups are pg_dump in custom format, without owners or ACLs, streamed to the browser as a download. That makes them easy to restore anywhere, including into a fresh basin project.

deploys and new servers

Nothing builds on the server. A push to main builds the frontend on GitHub Actions and pipes a tarball over SSH to a key that’s pinned to one command in authorized_keys:

command="/usr/local/bin/deploy-basin.sh",no-port-forwarding,no-pty ssh-ed25519 …

That script unpacks the artifacts, syncs them into place without touching the .env or the virtualenv, restarts the API and fails the deploy if it doesn’t come back up. A leaked deploy key can redeploy basin and nothing else.

Standing up a new server is an Ansible playbook: hardened Postgres, PgBouncer with the auth function and TLS, Caddy, the API service, the firewall and the deploy key. Secrets are generated once and cached on my laptop. A separate script migrates every project from an old server to a new one, keeping each role’s password verifier so existing connection strings keep working after DNS moves. I ran the playbook against a throwaway VM before trusting it, and it caught a broken Ansible setting on the first run.

what it doesn’t do

basin is deliberately small. There’s one admin login, no branching, no point-in-time recovery and no automatic backups yet. Everything shares one Postgres version and one machine’s disk, so it’s for side projects and internal tools, not for anything that needs a pager. For those, it’s been the right amount of database: one click, an isolated database, and a connection string that works from anywhere.