# PostgreSQL

Connect to PostgreSQL databases for analytics

PostgreSQL is a powerful, open-source relational database known for reliability, feature robustness, and extensibility. Scoop connects directly to PostgreSQL, enabling AI-powered analysis on your data.

## Connection Details

| Setting | Value |
|---------|-------|
| Default Port | 5432 |
| Connection Type | TCP/IP |
| SSL | Recommended |

## Prerequisites

Before connecting Scoop to PostgreSQL:

1. **PostgreSQL server** accessible from the internet (or via VPN/SSH tunnel)
2. **Read-only user** with SELECT permissions
3. **Network access** from Scoop's IP addresses

## Create a Read-Only User

Connect to your PostgreSQL database and run:

```sql
-- Create the read-only user
CREATE USER scoop_user WITH PASSWORD 'your_secure_password';

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

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO scoop_user;

-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO scoop_user;

-- Grant SELECT on future tables (recommended)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO scoop_user;
```

Replace `your_database` and the password with your actual values. If your tables are in a different schema, replace `public` with that schema name.

## Whitelist Scoop IP Addresses

### For Cloud-Hosted PostgreSQL

**Amazon RDS:**
1. Go to your RDS instance's Security Group
2. Edit inbound rules
3. Add a rule allowing PostgreSQL (port 5432) from Scoop's IP addresses

**Google Cloud SQL:**
1. Go to your Cloud SQL instance
2. Click **Connections** > **Networking**
3. Add Scoop's IP addresses to **Authorized Networks**

**Azure Database for PostgreSQL:**
1. Go to your server's **Connection Security**
2. Add firewall rules for Scoop's IP addresses

### For Self-Hosted PostgreSQL

Edit your `pg_hba.conf` file to allow connections from Scoop's IP addresses:

```
host    your_database    scoop_user    SCOOP_IP/32    scram-sha-256
```

Contact Scoop support for the current list of IP addresses to whitelist.

## Connect in Scoop

1. Navigate to **Datasets** and click **New Dataset**
2. Select **Database** as your source
3. Choose **PostgreSQL** from the database type dropdown
4. Enter your connection details:
   - **Server Host**: Your database hostname or IP
   - **Port**: 5432 (or your custom port)
   - **Database**: Your database name
   - **Username**: scoop_user
   - **Password**: The user's password
5. Click **Test Connection** to verify
6. Enter your SQL query and proceed

## Connection URL Format

If using a JDBC URL directly:

```
jdbc:postgresql://hostname:5432/database_name
```

With SSL:
```
jdbc:postgresql://hostname:5432/database_name?ssl=true&sslmode=require
```

## Best Practices

- **Enable SSL** for encrypted connections
- **Use connection pooling** for better performance with high query volumes
- **Create indexes** on frequently filtered columns
- **Use EXPLAIN ANALYZE** to optimize slow queries

## Troubleshooting

### Connection Refused
- Verify PostgreSQL is configured to accept remote connections (`listen_addresses` in `postgresql.conf`)
- Check that `pg_hba.conf` allows connections from Scoop's IP addresses
- Ensure the correct port is open in your firewall

### Authentication Failed
- Verify the username and password are correct
- Check that the user exists: `SELECT usename FROM pg_user;`
- Verify `pg_hba.conf` authentication method matches your connection

### Permission Denied
- Verify GRANT statements were executed successfully
- Check current permissions: `\dp table_name` in psql
- Ensure the user has USAGE on the schema