Coolify Postgres SSL

# Setting up PostgreSQL SSL in Coolify

This guide walks you through enabling SSL for PostgreSQL databases in Coolify.

## Step 1: Enable SSL in Coolify

1. Go to your PostgreSQL resource in Coolify
2. Scroll to **SSL Configuration**
3. Check **Enable SSL**
4. Click **Save**

## Step 2: Add the SSL Directory Mount

1. Go to **Storages** in the sidebar
2. Click **+ Add** → **Directory Mount**
3. Configure:
   - **Source:** `/data/coolify/databases/YOUR_DATABASE_ID/ssl`
   - **Destination:** `/var/lib/postgresql/certs`
4. Save

## Step 3: Find the Certificate Path

SSH into your server and locate the certificates:

```bash
sudo find /data/coolify -name "server.key" 2>/dev/null
```

This returns something like:

```
/data/coolify/databases/s0gwg8g8ww0wkg0wsg08o4ok/ssl/server.key
```

Use this path in subsequent steps.

## Step 4: Find the Correct UID for PostgreSQL

Different PostgreSQL images use different user IDs:

```bash
docker run --rm postgres:17-alpine id postgres
```

| Image | UID |
|-------|-----|
| `postgres:17-alpine` | 70 |
| `postgres` (standard) | 999 |

## Step 5: Fix Certificate Permissions

Replace `YOUR_DATABASE_ID` with your actual database ID and use the correct UID from Step 4:

```bash
sudo chown 70:70 /data/coolify/databases/YOUR_DATABASE_ID/ssl/server.key
sudo chown 70:70 /data/coolify/databases/YOUR_DATABASE_ID/ssl/server.crt
sudo chmod 600 /data/coolify/databases/YOUR_DATABASE_ID/ssl/server.key
sudo chmod 644 /data/coolify/databases/YOUR_DATABASE_ID/ssl/server.crt
```

## Step 6: Redeploy PostgreSQL

Click **Redeploy** in Coolify and wait for the database to start.

## Step 7: Create User and Database (If Needed)

If your database was initialized before setting up the username, create them manually:

```bash
docker exec -it CONTAINER_NAME psql -U postgres -c "CREATE USER myuser WITH PASSWORD 'mypassword' SUPERUSER;"
docker exec -it CONTAINER_NAME psql -U postgres -c "CREATE DATABASE mydb OWNER myuser;"
```

## Step 8: Connect from Your Application

### Mount the CA Certificate

In your application's **Storages**, add a **Directory Mount**:

- **Source:** `/data/coolify/ssl/coolify-ca.crt`
- **Destination:** `/etc/ssl/certs/coolify-ca.crt`

### Connection String

```
postgresql://user:pass@host:5432/db?sslmode=verify-full&sslrootcert=/etc/ssl/certs/coolify-ca.crt
```

## Troubleshooting

### "Permission denied" error

The certificate files have incorrect ownership. Re-run Step 4 to confirm the UID, then fix permissions in Step 5.

### "No such file or directory" error

The SSL directory isn't mounted. Verify the mount was added in Step 2 and redeploy.

### "role does not exist" error

The database user wasn't created. Run the commands in Step 7.

### "database does not exist" error

Create the database using the command in Step 7.