> ## Documentation Index
> Fetch the complete documentation index at: https://docs.anyshift.io/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL Integration

> Let Annie query your PostgreSQL databases read-only during investigations.

# PostgreSQL Integration

Connect a PostgreSQL database so Annie can query it read-only and correlate application data, slow queries, and database health with your infrastructure graph.

> [!WARNING]
>
> The PostgreSQL integration is currently in **beta**. Supported tools and
> database health checks may expand as we harden the connector.

## What Annie can do

* **Discover the schema** — list schemas, tables, views, sequences, and extensions, and read columns, constraints, and indexes
* **Run SQL** — execute read-only `SELECT` queries to answer questions about your application data
* **Explain queries** — show execution plans with `EXPLAIN`
* **Find slow queries** — rank the slowest or most resource-hungry queries from `pg_stat_statements`
* **Check database health** — indexes, connections, vacuum, sequences, replication, buffer cache, and constraints
* **Recommend indexes** — for your overall workload or for specific queries (uses the `hypopg` extension)

## Quickstart

1. [Create a dedicated read-only role](#create-a-read-only-role) for Annie.
2. [Allow Anyshift's outbound IPs](#network-access) to reach your database.
3. Go to the [Anyshift integrations page](https://app.anyshift.io/integrations).
4. In the **Analytics** section, open **PostgreSQL** and click **Add connection**.
5. Fill in the form:
   * **Name** — a label for this connection, for example `Production PostgreSQL`
   * **Host** — the public hostname or IP of your database, for example `db.example.com`
   * **Port** — defaults to `5432`
   * **Database** — the database Annie should query
   * **Username** and **Password** — the read-only role you created
   * **SSL mode** — keep the default `require` (see [SSL mode](#ssl-mode))
6. Click **Save PostgreSQL connection**.

On save, Anyshift connects to the database and runs `SELECT 1` (10-second timeout). The connection shows **Active** when it succeeds and **Error** when it fails. Only active connections are available to Annie.

### Multiple connections

You can add as many connections as you need, for example one per database or environment. Annie sees each connection as a separate PostgreSQL server and reports which one it queried.

## Security model

Annie's PostgreSQL access is strictly read-only:

* Queries run through Annie's PostgreSQL MCP server in **restricted mode**: every statement runs in a read-only transaction with a statement timeout. Restricted mode is enforced server-side and cannot be turned off.
* `INSERT`, `UPDATE`, `DELETE`, and DDL are refused.
* Your password is encrypted at rest with AWS KMS and is never exposed to the AI agent.
* Only publicly reachable hosts are accepted. Hosts that resolve to private, loopback, link-local, or other non-public addresses are rejected.

We still recommend a dedicated read-only role, so the database itself enforces the same limits.

### Create a read-only role

Run the following as an administrator: creating the role needs `CREATEROLE` (or superuser), and the grants must come from the owner of the database, schemas, and tables, or from a role allowed to grant on them. Replace the password, database, and schema names with your own, and repeat the schema grants for every schema Annie should read.

```sql theme={null}
-- Login role for Annie
CREATE ROLE annie_ro LOGIN PASSWORD 'change-me-to-a-strong-password';

-- Allow connecting to the database
GRANT CONNECT ON DATABASE app_db TO annie_ro;

-- Read access to existing objects in the schema
GRANT USAGE ON SCHEMA public TO annie_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO annie_ro;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO annie_ro;

-- Read access to tables and sequences created later.
-- Run as the role that creates the tables, or add FOR ROLE <owner>.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO annie_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO annie_ro;

-- Optional: let Annie see query statistics from every role (pg_stat_statements)
GRANT pg_read_all_stats TO annie_ro;

-- Every session starts read-only
ALTER ROLE annie_ro SET default_transaction_read_only = on;
```

> [!NOTE]
>
> Slow-query analysis and workload index recommendations need the `pg_stat_statements` extension, and index recommendations need `hypopg`. Annie works without them and tells you when an extension is missing. On managed services, enable them through the provider's parameter group or extension settings, then run `CREATE EXTENSION` in the database.

### Network access

Your database must be reachable from the internet on its PostgreSQL port. If it sits behind a firewall, security group, or IP allowlist, allow inbound traffic from Anyshift's outbound IP addresses:

| IP Address |
| - |
| 52.73.42.120 |
| 18.211.221.82 |
| 18.209.252.79 |

See [Network Requirements](/pages/privacy_security/network.md) for the downloadable list.

### SSL mode

| SSL mode | Behavior |
| - | - |
| `require` (default, recommended) | Always encrypts the connection |
| `verify-ca`, `verify-full` | Also verify the server certificate |
| `prefer`, `allow`, `disable` | May send credentials and data unencrypted; avoid on the internet |

> [!WARNING]
>
> `verify-ca` and `verify-full` work when the server certificate chains to a publicly trusted CA. Amazon RDS and Aurora certificates use AWS's own CAs, which the save-time check does not trust yet, so use `require` for them. Self-signed or private-CA certificates are not supported in these modes; use `require`.

## Example questions

* "Which tables in the `orders` database grew the most, and do they have the indexes they need?"
* "What are the slowest queries right now, and why is the top one slow?"
* "Run a health check on the production database: bloat, unused indexes, vacuum, and connections."
* "How many orders failed per hour since yesterday's deploy?"
* "Explain this query and suggest an index: `SELECT * FROM events WHERE account_id = $1 ORDER BY created_at DESC LIMIT 50`"

## Troubleshooting

### "Invalid PostgreSQL host"

The connection is not saved. The message tells you why:

* **"host resolves to a non-public address"** — the host resolves to a private, loopback, link-local, or other internal address. Annie only connects to publicly reachable databases. Use the database's public endpoint.
* **"could not resolve host"** — the hostname does not exist in public DNS. Check the spelling, or use the public IP.

### Status Error with a timeout

Anyshift could not reach the database within 10 seconds. Usually a firewall, security group, or network ACL blocks the connection:

* [Allow Anyshift's outbound IPs](#network-access) on the PostgreSQL port
* Make sure the database is publicly accessible, for example **Publicly accessible** on Amazon RDS
* Check the host and port

### Status Error with "password authentication failed"

* Check the username and password
* Make sure your `pg_hba.conf` (or provider settings) allows password logins from Anyshift's IPs
* Make sure the role has `LOGIN` and `CONNECT` on the database

### SSL errors

* **"SSL is not enabled on the server"** — the server does not accept TLS. Enable SSL on the server; switch to a weaker mode only if you accept unencrypted traffic.
* **Certificate verification errors** — with `verify-full`, make sure **Host** is a DNS name listed in the server certificate (not an IP address or alias it doesn't cover). If the certificate does not chain to a publicly trusted CA (for example Amazon RDS, self-signed or private CA), switch to `require` (see [SSL mode](#ssl-mode)).

### Annie says a table is missing or permission is denied

Grant `USAGE` on the schema and `SELECT` on its tables to the role, as in [Create a read-only role](#create-a-read-only-role).

### Annie doesn't use PostgreSQL

* Verify the connection shows **Active** on the integrations page
* Make sure Annie's access to this connection is not disabled in your integration settings
* After fixing a connection in **Error**, update it so Anyshift re-tests it


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.