Connect to the database (SQL)¶

What this is: opening a direct SQL connection to the rack's PostgreSQL from your laptop, using psql or a GUI client like DataGrip. When you'd do it: inspecting or debugging live data — principals, chat history, track hits — that the app doesn't surface in its UI. How long it takes: a couple of minutes.

Who can do this: a developer or infra operator whose SSH public key is in ssh_authorized_keys on the rack. This is not an operator task — it needs rack access, not a Directory login.

Postgres runs as a container on core-01 and is not published on any host port. Its only clients are the Directory and Web containers on the same Docker network, which reach it by the alias postgres. There is therefore no host:port to point a client at from your laptop — every route in goes over SSH.

Which database¶

One Postgres instance on core-01, two databases, one role:

Database	Used by	Role
`waypoint_directory`	Directory	`waypoint`
`waypoint`	Web	`waypoint`

waypoint is the instance's superuser (it is POSTGRES_USER), so unlike a managed service there is no separate app-user / superuser split and only one password.

The shape of it¶

flowchart LR
    A["psql / DataGrip<br/>on your laptop"] -->|"SSH"| B["core-01"]
    B -->|"docker network"| C["postgres<br/>container"]

Before you start¶

Your SSH key is on the rack — run bin/bootstrap-controller.sh in the infrastructure repo, get the printed public key merged into ssh_authorized_keys, and let foundation.yml converge.
You can reach core-01.bedrock.lan over the rack LAN or an explicitly provisioned management path that routes the rack address and resolves the internal name. The Cloudflare phone/device path does not expose .lan or SSH.
You know the sudo password for ubuntu (it is ansible_become_password in the rack vault) — needed only for the GUI path below.

Steps¶

The quick way — `psql` on the box¶

ssh ubuntu@core-01.bedrock.lan
docker exec -it postgres psql -U waypoint -d waypoint_directory

No password: connections over the container's local socket are trusted, which is the same route roles/service/directory uses for its own migrations. Swap -d waypoint_directory for -d waypoint to reach the web tier's database.

The GUI way — an SSH tunnel¶

The container publishes no port, so the tunnel has to target the container's address on the Docker network rather than the host:

PGIP=$(ssh ubuntu@core-01.bedrock.lan \
  "docker inspect -f '{{range .NetworkSettings.Networks}}{{.IPAddress}}{{end}}' postgres")

ssh -N -L 5433:$PGIP:5432 ubuntu@core-01.bedrock.lan

Leave that running in its own terminal. Then fetch the password, which is generated on the first converge and stored root-only on the box:

ssh ubuntu@core-01.bedrock.lan "sudo cat /etc/bedrock/postgres-secrets.env"

Connect from DataGrip — new data source → PostgreSQL:

Field	Value
Host	`localhost`
Port	`5433`
Database	`waypoint_directory` or `waypoint`
User	`waypoint`
Password	from `postgres-secrets.env` above
SSL	off — the SSH tunnel already encrypts

The equivalent JDBC URL:

jdbc:postgresql://localhost:5433/waypoint_directory

How to know it worked¶

psql gives you a prompt, and \l lists both waypoint and waypoint_directory.
\dt inside waypoint_directory lists the Directory's tables.
DataGrip's Test Connection returns green and the schema tree populates.

If something goes wrong¶

Permission denied (publickey). Your key is not on the rack yet. It is live only once the PR adding it to ssh_authorized_keys is merged and foundation.yml has converged — authorized_keys is written to exactly that list.
Error: No such container: postgres. Postgres is not running on core-01. Check with docker compose -f /etc/bedrock/compose/docker-compose.yml ps and re-run playbooks/core.yml.
connection refused on localhost:5433. The tunnel died, or the container was recreated and took a new address on the Docker network. Re-read $PGIP and restart the tunnel.
password authentication failed. The database password is the one in /etc/bedrock/postgres-secrets.env on the box — not anything in the vault. The vault holds the sudo password, which is what gets you permission to read that file.
sudo prompts and you don't have the password. It is ansible_become_password in the rack vault: ansible-vault view inventories/rack/group_vars/all/vault.yml.
Client insists on SSL. Turn it off. The SSH tunnel already encrypts, and layering TLS over localhost breaks the connection for no gain.

A note on the password¶

It is generated once, on the first converge, and written with force: false — so re-running Ansible never changes it. Nothing else holds a copy, which means the file on core-01 is the only record. Changing it is a deliberate act: rotate it in Postgres and in that file together, then re-run playbooks/core.yml so the app containers pick the new value up.