AI
intermediate7 min read

PostgreSQL

Connecting PostgreSQL

PostgreSQL is the most popular database used with AI for Database. This guide covers everything from creating a read-only user to advanced configuration like SSH tunnels and SSL certificates.

Prerequisites

  • PostgreSQL 12 or later
  • A user with CREATEROLE privileges (to create the read-only user)
  • Network access from AI for Database to your PostgreSQL server (port 5432 by default)

Step 1: Create a Read-Only User

Connect to your database as a superuser or a user with CREATEROLE and run:

sql
-- Create the user
CREATE USER aifordb_reader WITH PASSWORD 'a-strong-random-password';

-- Grant connect access
GRANT CONNECT ON DATABASE your_database TO aifordb_reader;

-- Grant schema usage and read access
GRANT USAGE ON SCHEMA public TO aifordb_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO aifordb_reader;

-- Ensure future tables are also readable
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO aifordb_reader;

If you use multiple schemas, repeat the GRANT statements for each one, or use a schema filter in the connection settings to limit visibility.

Step 2: Configure Connection in AI for Database

Navigate to Settings > Connections > Add Connection and select PostgreSQL. Fill in:

FieldExample
Hostdb.example.com
Port5432
Databaseproduction
Usernameaifordb_reader
Password(your password)
SSL Moderequire

Step 3: SSL Configuration

For production databases, we recommend require or verify-full SSL mode.

If your PostgreSQL server uses a self-signed certificate, upload the CA certificate in the Advanced section. AI for Database supports PEM-formatted certificates.

SSH Tunnel

If your PostgreSQL instance is in a private subnet (e.g., AWS RDS in a VPC), you can connect through an SSH tunnel:

  1. 1Set up a bastion host that can reach the database.
  2. 2In the connection form, enable SSH Tunnel.
  3. 3Enter the bastion host, SSH port (22), SSH username, and upload your private key.

AI for Database will open a secure tunnel through the bastion to reach your database.

Common Issues

Connection refused: Ensure pg_hba.conf allows connections from the AI for Database IP range. For cloud databases (RDS, Cloud SQL), check the security group or authorized network settings.

Permission denied for table: Run the GRANT SELECT statements above. If new tables were created after the initial grants, run ALTER DEFAULT PRIVILEGES or re-grant.

SSL error: Ensure the server supports SSL and that the correct certificate is uploaded. Try require mode before verify-full.

PreviousConnect Your DatabaseNextMySQL