# PostgreSQL

- URL: https://betteranalyst.com/docs/connectors/postgresql
- Updated: 2026-11-12
- Author: Better Analyst

Step-by-step guide to connecting PostgreSQL to Better Analyst, including read-only user setup, SSL configuration, and cloud provider instructions.

Connect your PostgreSQL database to analyze data using natural language queries and create visualizations.

## Prerequisites

- PostgreSQL 10 or higher
- Database credentials
- Network access from Better Analyst servers
- A user with SELECT permissions

## Connection Details

| Field | Description | Example |
| - | - | - |
| Host | Server hostname or IP | `db.example.com` |
| Port | PostgreSQL port (default 5432) | `5432` |
| Database | Database name | `analytics` |
| Username | Database user | `readonly_user` |
| Password | User password | `********` |
| SSL Mode | SSL connection mode | `require` |

## Setup Steps

### 1. Create a Read-Only User

```sql
CREATE USER formulabot WITH PASSWORD 'your-secure-password';
GRANT CONNECT ON DATABASE your_database TO formulabot;
GRANT USAGE ON SCHEMA public TO formulabot;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO formulabot;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO formulabot;
```

### 2. Configure Network Access

Allow connections from Better Analyst:

In `pg_hba.conf`:

```
host    your_database    formulabot    0.0.0.0/0    md5
```

Or, tighter than `0.0.0.0/0`, allowlist Better Analyst's IP ranges `74.220.50.0/24` and `74.220.58.0/24` in your cloud provider's security settings.

### 3. Connect in Better Analyst

1. Go to **Connectors** > **Add Connector**
2. Select **PostgreSQL**
3. Enter your connection details
4. Set SSL Mode (recommended: `require`)
5. Click **Test Connection**
6. Save when successful

## SSL Configuration

PostgreSQL supports several SSL modes:

| Mode | Description |
| - | - |
| `disable` | No SSL (not recommended) |
| `allow` | Prefer SSL, fall back to unencrypted |
| `prefer` | Prefer SSL, fall back to unencrypted |
| `require` | Require SSL, skip verification |
| `verify-ca` | Require SSL, verify CA certificate |
| `verify-full` | Require SSL, verify CA and hostname |

Use `require` or higher for production databases. Cloud databases like AWS RDS and Heroku require SSL by default.

## Cloud Providers

### AWS RDS

1. Enable public accessibility or use VPC peering
2. Add Better Analyst IPs to your security group
3. SSL is enabled by default

### Heroku Postgres

1. Get credentials from Heroku dashboard
2. SSL is required (`require` mode)
3. Credentials may rotate - update if connection fails

### Supabase

1. Use the dedicated [Supabase connector](https://betteranalyst.com/docs/connectors/supabase) when possible
2. Prefer the session pooler connection string from the Supabase Connect dialog
3. SSL is required

### DigitalOcean

1. Add trusted sources in the database settings
2. Download the CA certificate if using verify modes
3. SSL is available

## Example Questions

- "Show me all users who signed up this month"
- "What's the total revenue by product category?"
- "List orders with a value over $1000"
- "Create a time series of daily active users"

## Schemas

If your database uses multiple schemas:

1. Grant access to relevant schemas:

```sql
GRANT USAGE ON SCHEMA marketing TO formulabot;
GRANT SELECT ON ALL TABLES IN SCHEMA marketing TO formulabot;
```

2. Reference tables with schema prefix in questions:
   - "Show data from marketing.campaigns table"

## Troubleshooting

### Connection Timeout

- Verify host and port
- Check firewall/security group rules
- Ensure the database is running

### SSL Certificate Error

- Try a less strict SSL mode for testing
- Verify CA certificate if using `verify-ca`
- Check certificate hasn't expired

### Permission Denied

- Verify user has SELECT on the tables
- Check schema permissions (USAGE)
- Grant permissions on new tables if needed

## Next Steps

- [Connect Supabase](https://betteranalyst.com/docs/connectors/supabase)
- [Connect MSSQL](https://betteranalyst.com/docs/connectors/mssql)
- [Connect BigQuery](https://betteranalyst.com/docs/connectors/bigquery)