PostgreSQL

​

✅ What You’ll Need

• PostgreSQL Database (version 9.6 or higher)
• Database Host and Port (usually 5432)
• Database Name
• Admin access to create a read-only user

​

1️⃣ Create a Read-Only Database User

​

Step 1: Create the User

CREATE USER hunch_readonly WITH PASSWORD 'your_secure_password_here';

​

Step 2: Grant Read-Only Permissions

GRANT pg_read_all_data TO hunch_readonly

-- Grant connection permission to the database
GRANT CONNECT ON DATABASE your_database_name TO hunch_readonly;

-- Grant usage on the schema (usually 'public')
GRANT USAGE ON SCHEMA public TO hunch_readonly;

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

-- Grant read permissions on all future tables (optional but recommended)
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO hunch_readonly;

-- If you have views, grant read permissions on them too
GRANT SELECT ON ALL TABLES IN SCHEMA public TO hunch_readonly;


-- [Optional] Use with care
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO Read_Only_User;

Note for Managed PostgreSQL Solutions: If you’re using a managed PostgreSQL service like Supabase, AWS RDS, Google Cloud SQL, or similar platforms, you may need to follow additional steps to create a read-only user.
These platforms often have specific procedures and limitations for user management.
Please refer to your platform’s documentation for the correct method to create a read-only user for your specific managed PostgreSQL instance.

​

Step 3: Verify the User (Optional)

# Connect as the hunch_readonly user and try a simple query
psql -h your_host -p 5432 -U hunch_readonly -d your_database_name -c "SELECT current_user, current_database();"

​

2️⃣ Configure SSL (Recommended)

​

Client SSL Modes

• require - Always use SSL (recommended for production)
• verify-ca - Verify the server certificate against CA
• verify-full - Verify the server certificate and hostname
• disable - No SSL (only for development/testing)

​

2️⃣ Connect PostgreSQL to Hunch

1. Log into Hunch.dev
2. Click on your organization name > Connect Data

3. Click Configure on the PostgreSQL row

4. Fill in the connection details:
   • Host: Your PostgreSQL server hostname or IP address
   • Port: Your PostgreSQL port (default: 5432)
   • Database: The name of your database
   • User: The read-only username you created (e.g., hunch_readonly)
   • Password: The password for the read-only user
   • SSL Mode: Select the appropriate SSL mode for your environment

5. Click Submit

​

🎉 Done! You’re now connected

​

🔒 Security Best Practices

Important: Hunch only requires read access to your PostgreSQL data. We recommend:

• Always use a dedicated read-only user (never use admin credentials)
• Enable SSL encryption for production databases

​

🚨 Troubleshooting

​

Common Connection Issues

1. Connection Refused: Check that PostgreSQL is running and accessible from Hunch’s servers
2. Authentication Failed: Verify the username and password are correct
3. SSL Connection Error: Ensure your SSL configuration matches the selected SSL mode
4. Permission Denied: Verify the user has the necessary permissions on the database and schema