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

# PostgreSQL

> Connect AnomalyArmor to PostgreSQL databases including RDS, Aurora, and Supabase

<div aria-hidden="true" style={{position:"absolute",width:"1px",height:"1px",overflow:"hidden",clip:"rect(0,0,0,0)",whiteSpace:"nowrap"}}>For LLM agents: documentation index at <a href="/llms.txt" tabIndex={-1}>/llms.txt</a>, full text at <a href="/llms-full.txt" tabIndex={-1}>/llms-full.txt</a>. Append .md to any page URL for plain markdown.</div>
Connect AnomalyArmor to any PostgreSQL-compatible database. This guide covers self-hosted PostgreSQL as well as managed services like Amazon RDS, Aurora, and Supabase.

<img src="https://mintcdn.com/anomalyarmor/-pFpKEip0ftEEXe9/images/diagrams/postgresql-connection-light.svg?fit=max&auto=format&n=-pFpKEip0ftEEXe9&q=85&s=1a188845b04c2545fc3a7f8c714821ce" alt="PostgreSQL Connection Architecture showing TLS encryption and metadata-only access" className="block dark:hidden" width="800" height="320" data-path="images/diagrams/postgresql-connection-light.svg" />

<img src="https://mintcdn.com/anomalyarmor/-pFpKEip0ftEEXe9/images/diagrams/postgresql-connection-dark.svg?fit=max&auto=format&n=-pFpKEip0ftEEXe9&q=85&s=105dfdaf88b5b6d1c977e297ecbb77ab" alt="PostgreSQL Connection Architecture showing TLS encryption and metadata-only access" className="hidden dark:block" width="800" height="320" data-path="images/diagrams/postgresql-connection-dark.svg" />

## Supported Versions & Platforms

| Platform         | Minimum Version | Notes                       |
| ---------------- | --------------- | --------------------------- |
| PostgreSQL       | 12+             | Self-hosted or any cloud    |
| Amazon RDS       | 12+             | All instance classes        |
| Amazon Aurora    | PostgreSQL 12+  | Cluster and serverless      |
| Supabase         | Any             | Direct connection or pooler |
| Google Cloud SQL | 12+             | Public or private IP        |
| Azure Database   | 12+             | Single server or flexible   |
| Heroku Postgres  | Any             | Requires SSL                |

## Connection Settings

| Field               | Description            | Example                 |
| ------------------- | ---------------------- | ----------------------- |
| **Connection Name** | Friendly identifier    | `Production PostgreSQL` |
| **Host**            | Hostname or IP address | `db.example.com`        |
| **Port**            | Database port          | `5432`                  |
| **Database**        | Database name          | `myapp_production`      |
| **Username**        | Database user          | `anomalyarmor`          |
| **Password**        | User password          | `••••••••`              |
| **SSL Mode**        | SSL configuration      | `require`               |

### SSL Mode Options

SSL (Secure Sockets Layer) encrypts the connection between AnomalyArmor and your database, preventing eavesdropping on sensitive data like credentials and query results.

**Why use SSL?**

* **Security**: Encrypts all data in transit, protecting against network sniffing
* **Compliance**: Required for SOC2, HIPAA, PCI-DSS, and other security standards
* **Cloud providers**: Most managed databases (RDS, Aurora, Cloud SQL) require or strongly recommend SSL

**When SSL may not be needed:**

* Local development databases on `localhost`
* Databases on a private network with no external access
* Testing environments with non-sensitive data

#### Choosing an SSL Mode

| Mode          | Security Level | Description                                                                      |
| ------------- | -------------- | -------------------------------------------------------------------------------- |
| `disable`     | None           | No encryption. Data sent in plain text.                                          |
| `allow`       | Low            | Uses SSL only if server requires it.                                             |
| `prefer`      | Medium         | Tries SSL first, falls back to unencrypted if unavailable.                       |
| `require`     | High           | Always uses SSL, but doesn't verify the server certificate.                      |
| `verify-ca`   | Higher         | Uses SSL and verifies the server certificate is signed by a trusted CA.          |
| `verify-full` | Highest        | Uses SSL, verifies CA, and confirms the server hostname matches the certificate. |

#### Recommendations by Environment

| Environment                                  | Recommended Mode             | Reason                                                      |
| -------------------------------------------- | ---------------------------- | ----------------------------------------------------------- |
| **Local development**                        | `prefer` or `disable`        | Convenience for local testing                               |
| **Cloud databases** (RDS, Aurora, Cloud SQL) | `require`                    | SSL is available; certificate verification often not needed |
| **Production with compliance**               | `verify-ca` or `verify-full` | Maximum security for sensitive data                         |
| **Heroku, Supabase**                         | `require`                    | These platforms require SSL                                 |

<Tip>
  When in doubt, start with `prefer`. It provides encryption when available without blocking connections if SSL isn't configured on your database.
</Tip>

<Warning>
  Never use `disable` for production databases or any database containing sensitive data.
</Warning>

## SSH Tunnel (Bastion Host)

For databases behind firewalls, AnomalyArmor supports SSH tunnel connections through a bastion host. This is common in enterprise environments where databases are not directly accessible from the internet.

### When to Use SSH Tunnel

* Database is in a private subnet with no public IP
* Firewall rules prevent direct connections
* Security policy requires bastion host access

### SSH Tunnel Settings

Enable **SSH Tunnel** in the connection form to reveal these fields:

| Field                         | Description               | Example                              |
| ----------------------------- | ------------------------- | ------------------------------------ |
| **SSH Host**                  | Bastion server hostname   | `bastion.example.com`                |
| **SSH Port**                  | SSH port (usually 22)     | `22`                                 |
| **SSH Username**              | SSH user on bastion       | `ec2-user`                           |
| **Authentication Method**     | Key or Password           | `Key`                                |
| **SSH Private Key**           | PEM-formatted private key | `-----BEGIN RSA PRIVATE KEY-----...` |
| **Key Passphrase** (Optional) | For encrypted keys        | `••••••••`                           |
| **SSH Password**              | If using password auth    | `••••••••`                           |

### Key-Based Authentication (Recommended)

1. Generate an SSH key pair (or use existing):
   ```bash theme={null}
   ssh-keygen -t rsa -b 4096 -f anomalyarmor_key
   ```

2. Add the public key to the bastion host's `~/.ssh/authorized_keys`

3. In AnomalyArmor, paste the contents of the private key file or click **Upload Key File**

<Tip>
  Key-based authentication is more secure and doesn't require password rotation.
</Tip>

### Password Authentication

If your bastion host uses password authentication:

1. Set **Authentication Method** to `Password`
2. Enter the SSH password

<Warning>
  Key-based authentication is more secure than passwords. Use password auth only if key auth is not available.
</Warning>

### Connection Flow with SSH Tunnel

<img src="https://mintcdn.com/anomalyarmor/-pFpKEip0ftEEXe9/images/diagrams/ssh-tunnel-connection-light.svg?fit=max&auto=format&n=-pFpKEip0ftEEXe9&q=85&s=f3da12e42704cceec3c91802f8bc033d" alt="SSH Tunnel Connection Flow" className="block dark:hidden" width="900" height="280" data-path="images/diagrams/ssh-tunnel-connection-light.svg" />

<img src="https://mintcdn.com/anomalyarmor/CZXBGa_D1aE9spAI/images/diagrams/ssh-tunnel-connection-dark.svg?fit=max&auto=format&n=CZXBGa_D1aE9spAI&q=85&s=6c704646591eadd4ee34469416d14e46" alt="SSH Tunnel Connection Flow" className="hidden dark:block" width="900" height="280" data-path="images/diagrams/ssh-tunnel-connection-dark.svg" />

1. AnomalyArmor connects to your bastion host via SSH
2. An encrypted tunnel is established to your database
3. Database traffic flows securely through the tunnel
4. The tunnel closes automatically after each operation

## Creating a Read-Only User

Create a dedicated user with minimal permissions.

<Tip>
  **Quick Setup**: [View the PostgreSQL permissions script](/downloads/postgresql-permissions) for a ready-to-use SQL template with all necessary grants.
</Tip>

```sql theme={null}
-- Create the user
CREATE USER anomalyarmor WITH PASSWORD 'your-secure-password';

-- Grant connection access
GRANT CONNECT ON DATABASE your_database TO anomalyarmor;

-- Grant schema access (repeat for each schema)
GRANT USAGE ON SCHEMA public TO anomalyarmor;
GRANT USAGE ON SCHEMA analytics TO anomalyarmor;

-- Grant read access to existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO anomalyarmor;
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO anomalyarmor;

-- Grant access to future tables (recommended)
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO anomalyarmor;

ALTER DEFAULT PRIVILEGES IN SCHEMA analytics
GRANT SELECT ON TABLES TO anomalyarmor;
```

### Verifying Permissions

Test that the user can access metadata:

```sql theme={null}
-- Should return tables
SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' LIMIT 5;

-- Should return columns
SELECT column_name, data_type FROM information_schema.columns
WHERE table_schema = 'public' LIMIT 5;
```

## Provider-Specific Instructions

<Tabs>
  <Tab title="Amazon RDS">
    ### Amazon RDS PostgreSQL

    **Connection Details**:

    * **Host**: Your RDS endpoint (e.g., `mydb.abc123.us-east-1.rds.amazonaws.com`)
    * **Port**: `5432` (default)
    * **SSL Mode**: `require`

    **Security Group Configuration**:

    1. Go to **AWS Console → RDS → Your Instance → Security Groups**
    2. Edit inbound rules
    3. Add rule:
       * Type: `PostgreSQL`
       * Port: `5432`
       * Source: AnomalyArmor IPs (see Settings → Security)

    <img src="https://mintcdn.com/anomalyarmor/-pFpKEip0ftEEXe9/images/diagrams/security-group-rules-light.svg?fit=max&auto=format&n=-pFpKEip0ftEEXe9&q=85&s=be7476718e42b9880b108b9facb90b9e" alt="AWS Security Group Rules" className="block dark:hidden" width="700" height="200" data-path="images/diagrams/security-group-rules-light.svg" />

    <img src="https://mintcdn.com/anomalyarmor/-pFpKEip0ftEEXe9/images/diagrams/security-group-rules-dark.svg?fit=max&auto=format&n=-pFpKEip0ftEEXe9&q=85&s=7db7e8e715ccb368c852f443f2633ad0" alt="AWS Security Group Rules" className="hidden dark:block" width="700" height="200" data-path="images/diagrams/security-group-rules-dark.svg" />

    **Parameter Group** (if using verify-ca or verify-full):

    * Ensure `rds.force_ssl = 1`
    * Download RDS CA certificate bundle

    <Note>
      RDS instances in private subnets require NAT Gateway or VPC peering for AnomalyArmor access. Contact us for Enterprise VPC peering options.
    </Note>
  </Tab>

  <Tab title="Amazon Aurora">
    ### Amazon Aurora PostgreSQL

    **Connection Details**:

    * **Host**: Cluster endpoint (reader or writer)
    * **Port**: `5432` (default)
    * **SSL Mode**: `require`

    **Choosing the Right Endpoint**:

    | Endpoint             | Use Case                                     |
    | -------------------- | -------------------------------------------- |
    | **Cluster (writer)** | If you need real-time schema changes         |
    | **Reader**           | Recommended - no impact on production writes |

    <img src="https://mintcdn.com/anomalyarmor/pPIiSU0b3Ixsp9az/images/diagrams/aurora-cluster-endpoints-light.svg?fit=max&auto=format&n=pPIiSU0b3Ixsp9az&q=85&s=432e896378f401655153d1adb6d9b34c" alt="Aurora cluster writer and reader endpoints" className="block dark:hidden" width="700" height="270" data-path="images/diagrams/aurora-cluster-endpoints-light.svg" />

    <img src="https://mintcdn.com/anomalyarmor/pPIiSU0b3Ixsp9az/images/diagrams/aurora-cluster-endpoints-dark.svg?fit=max&auto=format&n=pPIiSU0b3Ixsp9az&q=85&s=790c76600320d3f4ca793c5980e53c23" alt="Aurora cluster writer and reader endpoints" className="hidden dark:block" width="700" height="270" data-path="images/diagrams/aurora-cluster-endpoints-dark.svg" />

    **Aurora Serverless v2**:

    * Use the cluster endpoint
    * Ensure minimum ACU allows connections during discovery
    * Consider scheduling discovery during active hours
  </Tab>

  <Tab title="Supabase">
    ### Supabase PostgreSQL

    **Connection Options**:

    | Method                   | Host                      | Port   | When to Use            |
    | ------------------------ | ------------------------- | ------ | ---------------------- |
    | **Direct**               | `db.xxx.supabase.co`      | `5432` | Standard setup         |
    | **Pooler (Transaction)** | `xxx.pooler.supabase.com` | `5432` | High connection limits |
    | **Pooler (Session)**     | `xxx.pooler.supabase.com` | `6543` | If direct fails        |

    **Finding Your Credentials**:

    1. Go to **Supabase Dashboard → Settings → Database**
    2. Copy the connection string or individual fields
    3. Use the database password (not the API key)

    **SSL Configuration**:

    * SSL Mode: `require`
    * Supabase enforces SSL by default

    <Warning>
      Don't use the Supabase API key as the password. Use the actual database password from Settings → Database.
    </Warning>
  </Tab>

  <Tab title="Self-Hosted">
    ### Self-Hosted PostgreSQL

    **Connection Details**:

    * **Host**: Your server's hostname or IP
    * **Port**: `5432` (or custom port)
    * **SSL Mode**: Depends on your setup

    **Firewall Configuration**:

    Allow inbound connections from AnomalyArmor IPs:

    ```bash theme={null}
    # iptables example
    iptables -A INPUT -p tcp --dport 5432 -s 34.xxx.xxx.xxx -j ACCEPT
    iptables -A INPUT -p tcp --dport 5432 -s 34.xxx.xxx.xxx -j ACCEPT
    ```

    **pg\_hba.conf Configuration**:

    Add entries for AnomalyArmor:

    ```
    # TYPE  DATABASE  USER           ADDRESS         METHOD
    hostssl all       anomalyarmor   34.xxx.xxx.xxx/32  scram-sha-256
    hostssl all       anomalyarmor   34.xxx.xxx.xxx/32  scram-sha-256
    ```

    **SSL Setup** (if not already configured):

    ```bash theme={null}
    # Generate self-signed certificate (for testing)
    openssl req -new -x509 -days 365 -nodes \
      -out server.crt -keyout server.key

    # Set permissions
    chmod 600 server.key
    chown postgres:postgres server.key server.crt

    # Enable in postgresql.conf
    ssl = on
    ssl_cert_file = '/path/to/server.crt'
    ssl_key_file = '/path/to/server.key'
    ```
  </Tab>

  <Tab title="Google Cloud SQL">
    ### Google Cloud SQL for PostgreSQL

    **Connection Methods**:

    | Method              | Description                                 |
    | ------------------- | ------------------------------------------- |
    | **Public IP**       | Add AnomalyArmor IPs to authorized networks |
    | **Cloud SQL Proxy** | For private IP instances (self-managed)     |

    **Public IP Setup**:

    1. Go to **Cloud Console → SQL → Your Instance → Connections**
    2. Under **Authorized Networks**, click **Add Network**
    3. Add each AnomalyArmor IP

    **Connection Details**:

    * **Host**: Public IP from instance overview
    * **Port**: `5432`
    * **SSL Mode**: `require`

    <Note>
      Cloud SQL requires SSL by default. If you need `verify-ca`, download the server certificate from the instance details.
    </Note>
  </Tab>
</Tabs>

## Connection Pooling Considerations

If you use a connection pooler (PgBouncer, Pgpool):

### PgBouncer

* **Transaction mode**: Works with AnomalyArmor
* **Session mode**: Recommended for best compatibility
* **Statement mode**: May have issues with complex queries

<Tip>
  Connect directly to PostgreSQL, not through PgBouncer, unless you have connection limit constraints.
</Tip>

### Connection Limits

AnomalyArmor uses **1-2 connections** during discovery. If you're near your connection limit:

1. Use a read replica for monitoring
2. Schedule discovery during off-peak hours
3. Increase `max_connections` if possible

## What We Query

AnomalyArmor runs these types of queries:

```sql theme={null}
-- Tables and views
SELECT * FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- Columns
SELECT * FROM information_schema.columns
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');

-- Constraints
SELECT * FROM information_schema.table_constraints;

-- Freshness (for timestamp columns)
SELECT MAX(your_timestamp_column) FROM your_table;
```

**Impact**: Minimal. These are lightweight metadata queries.

## Troubleshooting

<AccordionGroup>
  <Accordion title="Connection refused">
    **Causes**:

    * Firewall blocking the connection
    * Wrong hostname or port
    * Database not running

    **Solutions**:

    1. Verify AnomalyArmor IPs are allowlisted
    2. Check security group rules (for RDS/Aurora)
    3. Test connectivity: `nc -zv hostname 5432`
    4. Verify database is accepting connections
  </Accordion>

  <Accordion title="Password authentication failed">
    **Causes**:

    * Wrong password
    * User doesn't exist
    * pg\_hba.conf not configured

    **Solutions**:

    1. Verify password (copy-paste to avoid typos)
    2. Confirm user exists: `SELECT usename FROM pg_user;`
    3. Check pg\_hba.conf allows the connection method
    4. Try resetting the password
  </Accordion>

  <Accordion title="SSL connection required">
    **Causes**:

    * Database requires SSL but connection uses `disable`
    * Wrong SSL mode for the server

    **Solutions**:

    1. Set SSL Mode to `require`
    2. For RDS/Aurora/Supabase: SSL is required
    3. For self-hosted: Enable SSL or allow non-SSL (not recommended)
  </Accordion>

  <Accordion title="Permission denied for relation">
    **Causes**:

    * User lacks SELECT permission
    * Schema permission missing

    **Solutions**:

    ```sql theme={null}
    -- Grant schema access
    GRANT USAGE ON SCHEMA public TO anomalyarmor;

    -- Grant table access
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO anomalyarmor;
    ```
  </Accordion>

  <Accordion title="No tables found in discovery">
    **Causes**:

    * User can't see tables in information\_schema
    * Schema filter excluding all schemas

    **Solutions**:

    1. Test as the user: `SELECT * FROM information_schema.tables LIMIT 5;`
    2. Check schema filter settings in AnomalyArmor
    3. Verify tables exist in the expected schemas
  </Accordion>

  <Accordion title="SSH tunnel connection failed">
    **Causes**:

    * Invalid SSH credentials
    * Bastion host not reachable
    * SSH port blocked

    **Solutions**:

    1. Test SSH connection manually: `ssh -i key.pem user@bastion.example.com`
    2. Verify SSH host and port are correct
    3. Check that AnomalyArmor IPs can reach the bastion host
    4. Ensure the SSH user has permission to forward connections
  </Accordion>

  <Accordion title="SSH authentication failed">
    **Causes**:

    * Invalid private key format
    * Wrong passphrase for encrypted key
    * Public key not added to bastion

    **Solutions**:

    1. Verify key is in PEM format (starts with `-----BEGIN`)
    2. For encrypted keys, ensure passphrase is correct
    3. Check `~/.ssh/authorized_keys` on bastion includes your public key
    4. Verify SSH user exists on the bastion host
  </Accordion>
</AccordionGroup>

## Common Questions

### Which PostgreSQL-compatible services does AnomalyArmor support?

PostgreSQL 12+ self-hosted, Amazon RDS, Amazon Aurora (cluster and serverless), Supabase, Google Cloud SQL, Azure Database for PostgreSQL (single and flexible server), and Heroku Postgres. Any managed PostgreSQL that exposes the wire protocol on a network-reachable port works.

### What SSL Mode should I use for RDS or Aurora?

`require` is the right default - it encrypts traffic without pinning certificates. Use `verify-ca` or `verify-full` if your compliance program requires CA validation; you'll need to upload the RDS/Aurora CA bundle in connection settings. Never use `disable` for managed cloud databases.

### My PostgreSQL is only reachable through a bastion host. Can AnomalyArmor still connect?

Yes. Enable SSH tunnel mode on the connection, provide the bastion's host, port, user, and an SSH key, and AnomalyArmor tunnels to your PostgreSQL through it. The bastion needs outbound access to your database on port 5432.

### Does AnomalyArmor support PostgreSQL logical replication or CDC?

Not for primary monitoring. AnomalyArmor monitors via `information_schema` and bounded aggregates on a schedule - it does not read the WAL or consume replication slots. For freshness, it uses `MAX(timestamp_column)`, which works without replication.

### How do I give AnomalyArmor access to future tables without re-granting permissions?

Use `ALTER DEFAULT PRIVILEGES` so new tables created in the monitored schema automatically grant `SELECT` to the AnomalyArmor user:

```sql theme={null}
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO anomalyarmor;
```

Run it once per schema you want monitored. Existing tables still need an explicit `GRANT SELECT ON ALL TABLES` the first time.

## Next Steps

<CardGroup cols={2}>
  <Card title="Run Discovery" icon="magnifying-glass" href="/quickstart/run-first-discovery">
    Scan your PostgreSQL database
  </Card>

  <Card title="Set Up Alerts" icon="bell" href="/quickstart/set-up-first-alert">
    Get notified of schema changes
  </Card>
</CardGroup>