# Azure Database for PostgreSQL

Set up the collector for Azure Database for PostgreSQL flexible server.  Run the collector
container yourself anywhere that can reach the server on port 5432, such as an Azure VM, Azure Container
Instances, or a Kubernetes cluster with the [Helm chart](/docs/connect/kubernetes/).

With the `azure_flexible` provider, the collector reads the server from Azure Resource Manager,
finds its read replicas, and reads the server's Azure Monitor metrics.  It connects to PostgreSQL
with a password.

## Before you start

1.  Create a collector identity.  The agent ID, tenant ID and secret come from the app, as described in
   [What you need first](/docs/connect/infrastructure-as-code/#what-you-need-first).
2.  Create the database login.  Use the grants on
   [Database grants](/docs/connect/prepare-postgresql/#database-grants).  With `pg_monitor` alone,
   the collector reports metrics and the table list, but `pg_dump` fails with
   `permission denied for table`, so the schema arrives without its `CREATE` statements.
3.  Check the server parameters.  Three Azure defaults need changing:

   | Parameter | Azure default | Set it to |
   |---|---|---|
   | `azure.extensions` | empty | include `PG_STAT_STATEMENTS` |
   | `pg_stat_statements.track` | `none` | `top` or `all` |
   | `track_io_timing` | `off` | `on` |

   `shared_preload_libraries` already lists `pg_stat_statements` by default, so no restart is
   needed unless someone removed it.  The other three parameters are dynamic.  Read the current values first:

   ```sh
   az postgres flexible-server parameter list -g <resource-group> -s <server> \
     --query "[?name=='azure.extensions'||name=='shared_preload_libraries'||name=='pg_stat_statements.track'||name=='track_io_timing'].{name:name,value:value}" -o table
   ```

   Azure refuses `CREATE EXTENSION` for any extension not listed in `azure.extensions`; see
   Microsoft's
   [azure.extensions](https://learn.microsoft.com/en-us/azure/postgresql/parameters/parameters-customized-options#azureextensions).
   The value is a comma-separated list, so preserve the entries already in it.
4.  Create the extension in the `postgres` database while connected as the server admin:

   ```sql
   CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
   ```

   The collector reads statement statistics from `postgres`, so creating the extension only in your
   application database is not enough.  See
   [pg_stat_statements](/docs/connect/prepare-postgresql/#pg_stat_statements).
5.  Give the collector's Azure identity the `Reader` role on the server.  The collector reads the
   server and its replicas before connecting, so without the role it collects nothing; see
   [Without an Azure role](#without-an-azure-role).  The identity is selected from the environment:

   - AKS workload identity, when `AZURE_FEDERATED_TOKEN_FILE` is set.
   - A service principal, when `AZURE_CLIENT_SECRET` is set, with `AZURE_TENANT_ID` and
     `AZURE_CLIENT_ID`.
   - Otherwise, the managed identity of the VM or container host.  Set `client_id` in the provider
     block to select a user-assigned identity.

   ```sh
   az role assignment create --assignee <identity client id> --role Reader \
     --scope $(az postgres flexible-server show -g <resource-group> -n <server> --query id -o tsv)
   ```

6.  Allow the collector through the server's firewall.  The server accepts connections only from
   addresses allowed by its networking rules.

## Install

### The config

```toml
[dbgorilla]
agent_id  = "<agent id from the app>"
tenant_id = "<tenant id from the app>"
secret    = "${DBG_SERVER_SECRET}"

[[component]]
name   = "orders"
engine = "postgres"

[component.provider]
type        = "azure_flexible"
# az postgres flexible-server show -g <resource-group> -n <server> --query id -o tsv
resource_id = "/subscriptions/<subscription>/resourceGroups/<resource-group>/providers/Microsoft.DBforPostgreSQL/flexibleServers/<server>"

[component.auth]
method   = "password"
user     = "dbg_readonly"
password = "${DBG_DB_PASSWORD}"

[component.connect]
host      = "<server>.postgres.database.azure.com"
port      = 5432
databases = ["orders"]

[topology]
interval = "60s"

[commands]
enabled = true
allowed = ["explain", "collect_statistics"]   # see https://www.dbgorilla.com/docs/reference/collector-commands/#database-commands
```

Point `resource_id` at the primary server.  The collector discovers its read replicas from there.

### Run it

```sh
docker run -d --name dbg-collector --restart unless-stopped \
  -v "$PWD/collector.toml:/etc/dbg-collector/collector.toml:ro" \
  --env-file secrets.env \
  dbgorillapublic.azurecr.io/dbg-collector:0.16.0 \
  --config-file /etc/dbg-collector/collector.toml
```

`secrets.env` holds `DBG_SERVER_SECRET` and `DBG_DB_PASSWORD`.  For a service principal, add
`AZURE_TENANT_ID`, `AZURE_CLIENT_ID` and `AZURE_CLIENT_SECRET`.

### Certificates

The server requires TLS: `require_secure_transport` is `on` by default, and it refuses unencrypted
connections with `no pg_hba.conf entry … no encryption`.

Leave `ssl_mode` and `ca_cert` unset.  The server's certificate names its
`<server>.postgres.database.azure.com` host and chains to DigiCert Global Root G2, which the collector image already trusts.  To pin the root instead, download
[DigiCert Global Root G2](https://cacerts.digicert.com/DigiCertGlobalRootG2.crt.pem), mount it,
and point `ca_cert` at it.  Microsoft lists the roots it may move to on
[TLS in Azure Database for PostgreSQL](https://learn.microsoft.com/en-us/azure/postgresql/security/security-tls).

### Without an Azure role

If the collector's identity cannot read the server, discovery fails on every pass and the
component is skipped.  The log names the missing permission:

```text
discovery failed and no prior topology; skipping component this pass
… ARM get flexible server "<server>" failed (403 Forbidden): … does not have authorization to perform action 'Microsoft.DBforPostgreSQL/flexibleServers/read' …
```

To collect from the database without Azure access, use `type = "self_hosted"` with the same
`[component.connect]` block, and turn off the host-metrics probe because Azure does not answer it:

```toml
[component.provider]
type = "self_hosted"

[component.provider.node_exporter]
mode = "disabled"
```

The collector then sends the schema and PostgreSQL metrics.  Azure Monitor metrics and replica
discovery require the `azure_flexible` provider.

## Check it is working

```sh
docker logs -f dbg-collector
```

A healthy start logs `opamp handshake complete` and `discovered component`.  Use these messages to troubleshoot:

- `failed during probe: timed out after 10s` on a `self_hosted` component: add the
  `node_exporter` block above.
- `ManagedIdentityCredential authentication failed.  The requested identity has not been assigned to this resource`:
  the `client_id` names an identity this host does not have.
- `pg_stat_statements is not queryable in the connect database`: create the extension in
  `postgres`, as in [Before you start](#before-you-start).

## Upgrade

Change the image tag in `docker run` and recreate the container.  The collector's identity is in
`collector.toml`, so the new container is the same collector.

## Remove

Remove the container with `docker rm -f dbg-collector`.  The database remains in DBGorilla with
the status Disconnected.  To delete it, follow
[Removing the database from DBGorilla](/docs/administer/manage-collectors/#removing-the-database-from-dbgorilla).

## Next

The collector's other settings are on
[Collector configuration](/docs/reference/collector-configuration/).