---
title: "PostgreSQL - Documentation"
description: "Step-by-step guide to connecting PostgreSQL, including user creation, permissions, SSL configuration, and SSH tunnels."
canonical: "https://www.aifordatabase.com/docs/postgresql/"
---

# PostgreSQL

intermediate 7 min read

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

| Field | Example |
| --- | --- |
| Host | `db.example.com` |
| Port | `5432` |
| Database | `production` |
| Username | `aifordb_reader` |
| Password | `(your password)` |
| SSL Mode | `require` |

### 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`.

Source: https://www.aifordatabase.com/docs/postgresql/