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

# PostgreSQL

> Connect PostgreSQL databases to CloudThinker for query analysis, index optimization, and performance monitoring

Connect your PostgreSQL databases to enable [Tony](/guide/agents/tony) (Database Engineer) to analyze queries, optimize performance, and monitor database health.

## Supported platforms

| Platform                          | Support                        |
| --------------------------------- | ------------------------------ |
| **Self-hosted PostgreSQL**        | 12.x, 13.x, 14.x, 15.x, 16.x   |
| **AWS RDS PostgreSQL**            | All versions                   |
| **AWS Aurora PostgreSQL**         | All versions                   |
| **Google Cloud SQL**              | All PostgreSQL versions        |
| **Azure Database for PostgreSQL** | Flexible Server, Single Server |

## Prerequisites

* A PostgreSQL instance reachable from CloudThinker over the network.
* Admin access to create a dedicated user and grant privileges.
* The `pg_stat_statements` extension installed if you want query-level analysis (required for slow-query data).

## Setup

<Steps>
  <Step title="Connect as admin">
    Connect to your PostgreSQL instance using an admin account:

    ```bash theme={null}
    psql -h your-host -U postgres -d your-database
    ```
  </Step>

  <Step title="Create a read-only user">
    Create a dedicated user for CloudThinker:

    ```sql theme={null}
    CREATE USER cloudthinker_readonly WITH PASSWORD 'your-secure-password';
    ```
  </Step>

  <Step title="Grant connection and schema access">
    Allow connection and schema usage:

    ```sql theme={null}
    GRANT CONNECT ON DATABASE your_database TO cloudthinker_readonly;
    GRANT USAGE ON SCHEMA public TO cloudthinker_readonly;
    GRANT USAGE ON SCHEMA information_schema TO cloudthinker_readonly;
    ```
  </Step>

  <Step title="Grant SELECT permissions">
    Grant SELECT on all tables and views, including future ones:

    ```sql theme={null}
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO cloudthinker_readonly;
    GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO cloudthinker_readonly;

    ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO cloudthinker_readonly;
    ```
  </Step>

  <Step title="Enable pg_stat_statements">
    This extension is required for query analysis:

    ```sql theme={null}
    -- Enable extension (requires superuser)
    CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

    -- Grant access to the user
    GRANT SELECT ON pg_stat_statements TO cloudthinker_readonly;
    ```

    For RDS/Aurora, add to your parameter group:

    ```
    shared_preload_libraries = 'pg_stat_statements'
    ```
  </Step>

  <Step title="Configure network access">
    Ensure CloudThinker can reach your database:

    * Add CloudThinker IPs to your security group or firewall
    * For RDS: enable public access or use VPC peering
  </Step>

  <Step title="Add the connection in CloudThinker">
    Navigate to **Connections → PostgreSQL** and enter:

    * **Host**: your database hostname or IP
    * **Port**: database port (default: `5432`)
    * **Database**: database name
    * **Username**: `cloudthinker_readonly`
    * **Password**: the password you set above
    * **SSL mode**: `require` (recommended)

    Click **Connect**. CloudThinker shows a **Connected** status once it succeeds.
  </Step>
</Steps>

## Connection details

| Field                  | Description                                                         | Default   |
| ---------------------- | ------------------------------------------------------------------- | --------- |
| **Host**               | Database hostname or IP                                             | —         |
| **Port**               | PostgreSQL port                                                     | `5432`    |
| **Database**           | Database name                                                       | —         |
| **Username**           | Dedicated user, e.g. `cloudthinker_readonly`                        | —         |
| **Password**           | User password                                                       | —         |
| **SSL mode**           | `disable`, `allow`, `prefer`, `require`, `verify-ca`, `verify-full` | `require` |
| **Connection timeout** | Seconds to wait for connection                                      | `10`      |
| **Statement timeout**  | Max query execution time in ms                                      | `30000`   |

Connection string format:

```
postgresql://cloudthinker_readonly:your-secure-password@your-host:5432/your-database
```

## Required permissions

### Minimum

```sql theme={null}
GRANT CONNECT ON DATABASE dbname TO cloudthinker_readonly;
GRANT USAGE ON SCHEMA public TO cloudthinker_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO cloudthinker_readonly;
```

### Recommended (full analysis)

```sql theme={null}
-- All of the above, plus:
GRANT SELECT ON pg_stat_statements TO cloudthinker_readonly;
GRANT SELECT ON pg_stat_activity TO cloudthinker_readonly;
GRANT SELECT ON pg_stat_user_tables TO cloudthinker_readonly;
GRANT SELECT ON pg_stat_user_indexes TO cloudthinker_readonly;
GRANT SELECT ON pg_statio_user_tables TO cloudthinker_readonly;
GRANT pg_read_all_stats TO cloudthinker_readonly;  -- PostgreSQL 14+
```

<Tip>
  Start with the minimum grants and add the recommended stats grants to unlock query-level analysis and cache hit reporting.
</Tip>

## Agent capabilities

Once connected, Tony can:

| Capability                 | Description                                     |
| -------------------------- | ----------------------------------------------- |
| **Query analysis**         | Identify slow queries, analyze execution plans  |
| **Index recommendations**  | Find missing indexes, identify unused indexes   |
| **Performance metrics**    | Monitor connections, I/O, cache hit rates       |
| **Table statistics**       | Analyze table bloat, dead tuples, vacuum status |
| **Replication monitoring** | Check lag and streaming status on replicas      |

### Verify the connection

```text theme={null}
@tony #report check PostgreSQL connection health and list active database sessions
```

### Example prompts

```text theme={null}
@tony #report analyze slow queries on the production PostgreSQL instance
@tony #recommend find missing indexes on the orders table
@tony #dashboard show database performance metrics including cache hit rates
```

## Troubleshooting

<Accordion title="Connection refused">
  * Verify host and port are correct
  * Check your security group or firewall allows CloudThinker IPs
  * For RDS: ensure "Publicly accessible" is enabled or use VPC peering
  * Confirm PostgreSQL is listening on the correct interface
</Accordion>

<Accordion title="Authentication failed">
  * Verify username and password are correct
  * Check `pg_hba.conf` allows the connection method
  * Ensure the user has `CONNECT` privilege on the database
</Accordion>

<Accordion title="Missing pg_stat_statements">
  * Verify the extension is installed: `SELECT * FROM pg_extension WHERE extname = 'pg_stat_statements';`
  * Check `shared_preload_libraries` includes `pg_stat_statements`
  * Restart PostgreSQL after changing `shared_preload_libraries`
  * For RDS: modify the parameter group and reboot the instance
</Accordion>

<Accordion title="Permission denied">
  * Verify the user has `SELECT` on the required tables
  * Grant `pg_read_all_stats` role for PostgreSQL 14+
  * Check schema permissions with `\dn+` in psql
</Accordion>

## Security

* **Least privilege** — grant only the permissions the agents need for your use case; start read-only and widen later.
* **Read-only by default** — use read-only credentials unless you want agents to make changes through this connection.
* **Rotate credentials** — rotate keys and tokens on your normal schedule; CloudThinker picks up the new value when you update the connection.
* **Revoke on offboarding** — remove the credential at the provider when you delete a connection or a teammate leaves.

- **SSL required** — always use SSL mode `require` or higher to encrypt data in transit.
- **Minimal grants** — grant only `SELECT` to the CloudThinker user; never grant write access.

## Related

<CardGroup cols={2}>
  <Card title="Tony Agent" icon="database" href="/guide/agents/tony">
    Database-focused optimization agent
  </Card>

  <Card title="MySQL Connection" icon="https://mintcdn.com/cloudthinker/aLd-ttc-SCW-aFky/images/icons/mysql.svg?fit=max&auto=format&n=aLd-ttc-SCW-aFky&q=85&s=29b74ea2b3ffacd21682ca898f6a2e43" href="/guide/connections/mysql" width="24" height="24" data-path="images/icons/mysql.svg">
    Similar setup for MySQL databases
  </Card>
</CardGroup>