PostgreSQL

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

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:

-- 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