# Managed Postgres on one box

> How basin turns a single VPS into a tiny Neon. A database and a role per project, a wildcard PgBouncer, verified TLS, and the Postgres 16 permission change that broke it on day one.

URL: https://sayanbiswas.in/blog/basin-managed-postgres-on-one-box/

10 oct 2026 · 5 min read · (postgres)(infra)(self-hosting)

How basin turns a single VPS into a tiny Neon. A database and a role per project, a wildcard PgBouncer, verified TLS, and the Postgres 16 permission change that broke it on day one.

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](https://sayanbiswas.in/p/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:

```sql
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:

```text
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:

```sql
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:

```ini
[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:

```sql
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.

*Diagram:* Caddy obtains a Let's Encrypt certificate and a daily timer copies it to PgBouncer, which serves verified TLS to apps on port 6543 and pools connections to Postgres, which only listens on localhost.

## 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`:

```text
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.