# Bind a managed database

Bind an app to a managed Postgres database so that every rollout generates DATABASE_URL and the PG variables from the database's current endpoint and credential.

Every rollout generates `DATABASE_URL`, `PGHOST`, `PGPORT`, `PGDATABASE`, `PGUSER`, `PGPASSWORD` and
`PGSSLMODE` (`require`), built from the database's current endpoint and the credential's current
version, so a password rotation reaches the app on its next rollout without anybody editing a
variable. A private database whose endpoint answers with several addresses also gets
`PGCONNECT_TIMEOUT` (3 seconds). With `envPrefix` `ANALYTICS_` the variables are
`ANALYTICS_DATABASE_URL` and so on, for a second database; an app has one database binding per
prefix, and binding again with the same prefix replaces it. A variable of the same name cannot be
set by hand while the binding exists (`409`, `Env "DATABASE_URL" is generated by the app's database
binding`). Replace app-id with the app's id and `ewdb-0123456789ab` with the database's id.
An app on pooled hosting can instead have a database on its pool host, and a binding without a
prefix takes precedence over it; see [Use a database on the pool host](use-a-pool-host-database.md).

## Prerequisites

- A [managed Postgres](/docs/rds) database that is available, has an endpoint, and has its master
  password filed in Secrets.
- Permission for `deploy:ManageDatabaseBinding` on the app and `rds:ModifyDBInstance` on the
  database. To bind a branch, also `rds:GetDBBranchCredentials`. To redeploy in the same step,
  `deploy:DeployApp`.
- Bind before the first deploy, so that the app's migrations create every table as the app's own
  role.

## Bind the database

**Console**
1. Open the app, then choose the **Environment** tab. The **Database** section is shown only with
   `deploy:ManageDatabaseBinding`.
2. For **Database instance**, choose the database. A database that cannot be bound yet is listed
   but cannot be chosen, with the reason beside it.
3. For **Credential**, choose **A role for this app (recommended)** or **The master credential**.
4. (Optional) For **Database name**, enter the database. Left empty, a role binding uses the app's
   subdomain label with `-` replaced by `_`, and a master binding uses the instance's database.
5. (Optional) For **Variable prefix**, enter a prefix such as `ANALYTICS_`.
6. (Optional) Clear **Connect through the pooler (PgBouncer, transaction mode)**.
7. Choose **Bind**, or **Bind & redeploy** (shown only with `deploy:DeployApp`). The binding is
   listed with its status and the variables it generates.

Binding a branch (`dbBranchId`) is available in the CLI, API and Terraform only.

**CLI**
```sh
dcloud deploy database bind app-id --db-instance-id ewdb-0123456789ab \
  --database orders --mode app_role --redeploy
```

**API**
```http
PUT /deploy/api/apps/3f1c2b4a-5d6e-4f70-8a91-b2c3d4e5f601/database
{ "dbInstanceId": "ewdb-0123456789ab", "database": "orders", "mode": "app_role" }
```

**Terraform**
```hcl
resource "dcloud_app_database_binding" "main" {
  app_id         = dcloud_app.web.id
  db_instance_id = dcloud_db_instance.main.id
  database       = "orders"
  pooled         = true
  redeploy       = true
}
```

The binding is refused when:

- the database is not available, has no endpoint, or its master password is not in Secrets (409,
  for example `The database is modifying; bind it once it is available`);
- the app's machines cannot reach a private database (409, see below);
- the app's environment already sets one of the generated variables (409, `Remove DATABASE_URL, …
from the environment first: the binding generates them`);
- the branch is not available (409) or is not a branch of that database (404);
- Postgres refuses to create the role or grant it (502, `The database refused: <message>`); the
  password filed in Secrets is rolled back;
- the app is being deleted (409).

## Credential modes

- **`app_role`** (default) creates a Postgres role `app_<slug>` (`app_<slug>_<prefix>` with a
  prefix) for this app alone and files its password as the secret `APP_<SLUG>_DB_PASSWORD`
  (`APP_<SLUG>_<PREFIX>_DB_PASSWORD`), granted to `deploy`.
  - If the database does not exist, it is created and owned by that role.
  - If it exists but holds no tables, views or sequences yet (the database an instance is created
    with, say), the role becomes its owner and the owner of schema `public`. Objects that belong to
    an extension do not count. Later `ALTER TABLE` migrations need that ownership.
  - If it already holds data, the role is granted on it: connect, schema `public`, existing tables
    and sequences, and default privileges for what the master creates later.
  - Binding again converges rather than failing.
- **`master`** hands the app the database's master credential. It follows master-password
  rotations: rotating the master password on the database redeploys every app bound this way.

`pooled: true` (default) connects through PgBouncer (`6432`, transaction mode), and `false` connects
straight to `5432`. Use `false` for an ORM that needs session state, such as prepared statements or
advisory locks. `dbBranchId` connects to a [branch](/docs/rds/guide/branches) of the database
instead: the branch's master user and port, no pooler.

## Which address the app gets

The address is chosen at every rollout.

| The app's machine                                           | The database                      | `DATABASE_URL` / `PGHOST` |
| ----------------------------------------------------------- | --------------------------------- | ------------------------- |
| in the same network (app machines join the default network) | any, with a private endpoint      | the private endpoint      |
| in another network, not attached to one, or not created yet | public                            | the public endpoint       |
| in another network, or not attached to one                  | private                           | refused                   |
| any                                                         | private, with no private endpoint | refused                   |

A private database created without `vpcId` is in the default network, so creating it private and
binding an app needs no further step. The private endpoint is in the database's TLS certificate, so
`sslmode=verify-full` works against it as against the public one. A private database's public
endpoint admits only the control plane, never an app's machine (a private cluster's load balancer
has no public interface at all), so instead of handing it out the binding is refused: when binding
(`409`, saying why) and, for an existing binding, at the next rollout. The **Database** panel
says why on such a binding, and marks a private database with no private endpoint (one
created before private endpoints existed) as not bindable.

## Trusting the database's certificate

The database's certificate is signed by the platform's own CA, and every app container trusts it
without any change to the image. The CA is mounted read-only at `/etc/ews/ca/platform-ca.pem` into
every container (the app, each compose service, the release command, scheduled jobs), and these
are set:

| Variable              | Set when                                     | Effect                                                                                                       |
| --------------------- | -------------------------------------------- | ------------------------------------------------------------------------------------------------------------ |
| `NODE_EXTRA_CA_CERTS` | always                                       | Node adds the CA to its built-in roots, so node-postgres (`pg`) connects with `DATABASE_URL` as it is        |
| `PGSSLROOTCERT`       | the app has a binding without an `envPrefix` | libpq clients (`psql`, psycopg, Ruby `pg`), pgx and asyncpg verify the database against the CA (`verify-ca`) |

A variable the app sets itself is kept. `SSL_CERT_FILE` and `REQUESTS_CA_BUNDLE` are never set,
because they replace a runtime's trust store rather than add to it; outbound HTTPS keeps using the
image's own CAs. Containers get the CA from their next rollout on.

`PGSSLROOTCERT` applies to every libpq connection in the process, so it is set only beside the
unprefixed binding, which already sets libpq's defaults (`PGHOST`, `PGSSLMODE`). An app that also
connects with `sslmode=require` to another Postgres whose certificate is not from this platform
(Amazon RDS, Supabase) sets `sslrootcert` on that connection, or sets its own `PGSSLROOTCERT`.

An app that brings its own trust store (a Java truststore, a Go binary built with a fixed
`x509.CertPool`, a Node app that sets `ssl.ca` itself) has to add the CA to that store: read it from
`/etc/ews/ca/platform-ca.pem` at startup, or download it (`GET /rds/api/ca-bundle`). If the app sets
`NODE_EXTRA_CA_CERTS` to its own bundle, that bundle has to include the platform CA.

## See a binding's connection

Expand **Connection details** on a binding in the **Database** panel. It shows the host (private or
public, as the next rollout resolves it), the port (pooled or direct), the database and the role,
each copyable. **Reveal value** reads the password and shows the full `DATABASE_URL`, masked until
you show it, plus the same URL with `sslmode=verify-full` and a link to download the CA. Revealing
needs `secrets:GetSecretValue` on the role's secret (the master password's, for a `master` binding)
and is recorded on that secret's access log.

The same details, without the password, need `deploy:DescribeApp`:

```sh
dcloud deploy connections get app-id
```

Each binding names the secret that holds its password; read it with
`dcloud secrets secrets get-value <secret id>`. A binding the next rollout would refuse carries the
reason instead of a connection. A private host is reachable only inside the database's network.

## Rotate the password or unbind

- **Rotate password** on a role binding (`deploy.database.rotate`) files a new version, changes
  the role, and redeploys: by default in the CLI and API (`"redeploy": false` turns it off), and in the
  console only when you have `deploy:DeployApp`. Postgres keeps existing
  connections authenticated, so the old container keeps working until the new one is live. A
  `master` binding cannot be rotated here (409, `A master-mode binding follows the master password,
so rotate that on the database`).
- **Unbind** (`dcloud deploy database unbind app-id`, with `--env-prefix` for a prefixed
  binding) removes the variables from the next rollout. The role and its data are kept.

## Result

From the next rollout the app's containers have `DATABASE_URL` and the `PG*` variables, pointing at
the right endpoint with the current password.

## Related

- [Bind a cache](bind-a-cache.md)
- [Use a database on the pool host](use-a-pool-host-database.md)
- [Set environment variables](set-environment-variables.md)
- `deploy.database.bind` · `dcloud deploy database bind` · `dcloud_app_database_binding`
