# Supabase Self-Hosted on AWS (RDS Proxy + Password Rotation)

Production deployment guide for self-hosted Supabase on EC2 with Docker Compose, using **Amazon RDS** behind **RDS Proxy** and **Secrets Manager password rotation**.

This document supersedes the generic RDS-endpoint steps in the original `supabase-aws-guide.docx` for the Datafy production account.

> **Open this guide:** `docs/supabase-aws-rds-proxy-setup.md` — open the **file**, not the `docs/` folder.

## Contents

1. [Step 1 — VPC and security groups](#step-1--vpc-and-security-groups)
2. [Step 2 — RDS PostgreSQL](#step-2--rds-postgresql)
3. [Step 3 — S3 bucket](#step-3--s3-bucket-for-storage)
4. [Step 4 — Amazon SES](#step-4--amazon-ses-for-email)
5. [Step 5 — EC2 instance](#step-5--ec2-instance)
6. [Connect to the database from EC2](#connect-ec2)
7. [Step 6 — Supabase Docker Compose](#step-6--supabase-docker-compose-rds-proxy)
8. [Step 7 — Nginx + HTTPS](#step-7--nginx--https)
9. [Step 8 — Secrets Manager](#step-8--secrets-manager-for-env)
10. [Step 9 — Backups and monitoring](#step-9--backups-and-monitoring)
11. [Step 10 — Verify deployment](#step-10--verify-deployment)

## Architecture


| Component       | AWS service                         | Purpose                                   |
| --------------- | ----------------------------------- | ----------------------------------------- |
| Compute         | EC2 (`t3.large`+)                   | Runs Supabase Docker containers           |
| Database        | RDS PostgreSQL 15                   | Managed Postgres (`datafy-prod-postgres`) |
| Connection pool | RDS Proxy (`datafy-prod-rds-proxy`) | Stable endpoint, credential handling      |
| Credentials     | Secrets Manager                     | Master DB password with 7-day rotation    |
| Object storage  | S3                                  | Supabase Storage                          |
| Email           | SES                                 | Auth emails, magic links, OTPs            |
| SSL / proxy     | Nginx + Let's Encrypt               | HTTPS in front of Kong                    |
| DNS             | Route 53                            | Domain → Elastic IP                       |


**Region:** `eu-west-1` (Ireland)

### Production values (Datafy)


| Setting                | Value                                                                                       |
| ---------------------- | ------------------------------------------------------------------------------------------- |
| RDS instance           | `datafy-prod-postgres`                                                                      |
| RDS Proxy              | `datafy-prod-rds-proxy`                                                                     |
| Proxy endpoint         | `datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com`                      |
| Proxy security group   | `sg-013487b46ffc5fce8`                                                                      |
| Proxy IAM role         | `arn:aws:iam::926753675240:role/datafy-prod-rds-proxy-role`                                 |
| Secrets Manager secret | `rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c` (contains `**username` and `password` only**) |
| DB host (use this)     | `datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com`                      |
| DB port                | `5432`                                                                                      |
| DB name                | `postgres`                                                                                  |
| VPC                    | `vpc-09bb94de5036359d8`                                                                     |
| TLS on proxy           | Enabled                                                                                     |
| S3 storage bucket      | `datafy-prod-supabase-storage` (`eu-west-1`)                                                |


> **Important:** Supabase connects to the **proxy endpoint**, not the direct RDS hostname. The Secrets Manager secret only stores `username` and `password` — host, port, and database name are configured separately (see table above).

---

## Prerequisites

- AWS account with permissions for EC2, RDS, RDS Proxy, S3, SES, Secrets Manager
- Registered domain (Route 53 or external DNS)
- AWS CLI configured on your workstation and EC2
- Basic familiarity with Linux, Docker, and the AWS Console

---

## Step 1 — VPC and security groups

Use the default VPC or a dedicated VPC. RDS must **never** be exposed to the public internet.

### EC2 security group (`supabase-ec2-sg`)


| Direction | Port        | Source / destination                           |
| --------- | ----------- | ---------------------------------------------- |
| Inbound   | 22 (SSH)    | Your IP only                                   |
| Inbound   | 80 (HTTP)   | `0.0.0.0/0` (Let's Encrypt / redirect)         |
| Inbound   | 443 (HTTPS) | `0.0.0.0/0`                                    |
| Inbound   | 8000 (Kong) | `0.0.0.0/0` (optional, if not using Nginx yet) |
| Outbound  | All         | `0.0.0.0/0`                                    |


### RDS security group (`supabase-rds-sg`)


| Direction | Port | Source                                                               |
| --------- | ---- | -------------------------------------------------------------------- |
| Inbound   | 5432 | RDS Proxy security group **and/or** EC2 SG (for direct admin access) |
| Outbound  | All  | `0.0.0.0/0`                                                          |


### RDS Proxy security group (`sg-013487b46ffc5fce8`)


| Direction | Port | Source                 |
| --------- | ---- | ---------------------- |
| Inbound   | 5432 | `supabase-ec2-sg` only |
| Outbound  | All  | `0.0.0.0/0`            |


> **Never** add `0.0.0.0/0` to the RDS or proxy security groups.

---

## Step 2 — RDS PostgreSQL

### Instance settings

- Engine: PostgreSQL 15.x
- Template: Production (Multi-AZ, automated backups)
- Instance class: `db.t3.medium` minimum; `db.t3.large` for heavier workloads
- Storage: 20 GB GP3 with autoscaling
- VPC: same as EC2
- Security group: `supabase-rds-sg`
- Public access: **No**
- Database name: `postgres`

### Parameter group (required for Realtime)


| Parameter                  | Value                       |
| -------------------------- | --------------------------- |
| `max_connections`          | `200`                       |
| `shared_preload_libraries` | `pg_stat_statements,pg_tle` |
| `wal_level`                | `logical`                   |
| `max_replication_slots`    | `10`                        |
| `max_wal_senders`          | `10`                        |
| `rds.logical_replication`  | `1`                         |
| `log_min_messages`         | `warning`                   |


Apply the parameter group and reboot if `shared_preload_libraries` was changed.

### Extensions and roles

After EC2 is set up (Step 5), connect from EC2 via the proxy — see **Step: Connect to the database from EC2** further down in this file.

**Important:** Supabase expects `pgcrypto` in the `**extensions`** schema. The pgjwt script calls `extensions.hmac()`.

If you previously ran `CREATE EXTENSION pgcrypto` in `public`, running `1-extensions.sql` will print **"already exists, skipping"** but will **not** move it — `2-pgjwt.sql` will then fail with `extensions.hmac does not exist`.

#### Step A — check where extensions are installed

```bash
psql "host=${PGHOST} port=${PGPORT} dbname=${PGDATABASE} user=${PGUSER} sslmode=require" -c "
SELECT e.extname, n.nspname AS schema
FROM pg_extension e
JOIN pg_namespace n ON e.extnamespace = n.oid
WHERE e.extname IN ('pgcrypto', 'uuid-ossp');"
```

You need `pgcrypto` in schema `**extensions**`. If it shows `**public**`, run the fix below before `2-pgjwt.sql`.

#### Step B — fix extensions schema (run once if pgcrypto is in `public`)

Connect with `psql`, then run:

```sql
DROP SCHEMA IF EXISTS jwt CASCADE;

CREATE SCHEMA IF NOT EXISTS extensions;

-- Move existing extensions (preferred if nothing depends on them in public)
ALTER EXTENSION pgcrypto SET SCHEMA extensions;
ALTER EXTENSION "uuid-ossp" SET SCHEMA extensions;
```

If `ALTER EXTENSION` errors, drop and recreate instead:

```sql
DROP SCHEMA IF EXISTS jwt CASCADE;
DROP EXTENSION IF EXISTS pgcrypto CASCADE;
DROP EXTENSION IF EXISTS "uuid-ossp" CASCADE;

CREATE SCHEMA IF NOT EXISTS extensions;
CREATE EXTENSION "uuid-ossp" WITH SCHEMA extensions;
CREATE EXTENSION pgcrypto WITH SCHEMA extensions;
```

Confirm `hmac` is available:

```sql
SELECT extensions.hmac('test'::bytea, 'secret'::bytea, 'sha256');
```

You should get a bytea result (not an error).

#### Step C — pg_stat_statements

```bash
psql "host=${PGHOST} port=${PGPORT} dbname=${PGDATABASE} user=${PGUSER} sslmode=require" -c "CREATE EXTENSION IF NOT EXISTS pg_stat_statements;"
```

#### Step D — pgjwt (`jwt` schema)

```bash
curl -fsSL -o ~/2-pgjwt.sql https://raw.githubusercontent.com/agblox/supabase-postgres/develop/rds/2-pgjwt.sql

psql "host=${PGHOST} port=${PGPORT} dbname=${PGDATABASE} user=${PGUSER} sslmode=require" -f ~/2-pgjwt.sql
```

Verify:

```sql
SELECT jwt.sign('{"role":"anon"}'::json, 'test-secret');
```

#### Fresh database (no prior extensions in `public`)

If extensions are not installed yet, you can use the agblox script:

```bash
curl -fsSL -o ~/1-extensions.sql https://raw.githubusercontent.com/agblox/supabase-postgres/develop/rds/1-extensions.sql
psql "host=${PGHOST} port=${PGPORT} dbname=${PGDATABASE} user=${PGUSER} sslmode=require" -f ~/1-extensions.sql
```

Then continue from Step C above.

Then create Supabase roles:

```sql
CREATE ROLE authenticator NOINHERIT LOGIN PASSWORD '<strong-password>';
CREATE ROLE anon NOLOGIN;
CREATE ROLE authenticated NOLOGIN;
CREATE ROLE service_role NOLOGIN BYPASSRLS;
GRANT anon TO authenticator;
GRANT authenticated TO authenticator;
GRANT service_role TO authenticator;
```

For a **full** Supabase schema on RDS (all required DB users and migrations), use [agblox/supabase-postgres](https://github.com/agblox/supabase-postgres) `provision.sh` against the **proxy endpoint**. **Do not** run this on an existing production database without a migration plan.

### RDS caveats

- `superuser` → use `rds_superuser` on RDS
- `replication` → use `rds_replication` on RDS
- `**pgjwt`** → not available as `CREATE EXTENSION` on RDS; use the `jwt` schema SQL script above (`[2-pgjwt.sql](https://github.com/agblox/supabase-postgres/blob/develop/rds/2-pgjwt.sql)`)
- `pg_graphql` and `pgsodium` are not available on RDS
- Realtime requires `wal_level=logical` and connections via the read/write endpoint (proxy default target role)

---

## Step 3 — S3 bucket for Storage

### Production bucket (already exists)


| Setting     | Value                          |
| ----------- | ------------------------------ |
| Bucket name | `datafy-prod-supabase-storage` |
| Region      | `eu-west-1` (Europe — Ireland) |
| Created     | 2026-06-22                     |


**Skip bucket creation** if this bucket already exists. Verify settings and create IAM credentials for Supabase only.

### 3.1 Verify bucket settings

In **S3 → `datafy-prod-supabase-storage`** confirm:

- **Block Public Access** — all four options enabled (Supabase uses signed URLs, not public objects)
- **Default encryption** — SSE-S3 or SSE-KMS (either is fine)
- **Bucket policy** — no public `Principal: "*"` read access required

CLI check for public access block:

```bash
aws s3api get-public-access-block \
  --bucket datafy-prod-supabase-storage \
  --region eu-west-1
```

If not set, apply it:

```bash
aws s3api put-public-access-block \
  --bucket datafy-prod-supabase-storage \
  --region eu-west-1 \
  --public-access-block-configuration \
    BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true
```

### 3.2 S3 access for Supabase Storage

Supabase Storage runs **inside a Docker container**, not directly on the EC2 host. It needs AWS credentials to talk to S3. You have two options:

#### Option A — EC2 IAM role (recommended if instance already has bucket access)

If your EC2 instance already has an IAM role that can access `datafy-prod-supabase-storage`, **you do not need a separate IAM user or access keys**.

Attach (or confirm) this policy on the **EC2 instance role**:

```json
{
  "Version": "2012-10-17",
  "Statement": [{
    "Effect": "Allow",
    "Action": ["s3:ListBucket", "s3:GetObject", "s3:PutObject", "s3:DeleteObject"],
    "Resource": [
      "arn:aws:s3:::datafy-prod-supabase-storage",
      "arn:aws:s3:::datafy-prod-supabase-storage/*"
    ]
  }]
}
```

In `supabase-project/.env`, set bucket/region only — **leave access keys empty**:

```env
STORAGE_BACKEND=s3
GLOBAL_S3_BUCKET=datafy-prod-supabase-storage
GLOBAL_S3_ENDPOINT=https://s3.eu-west-1.amazonaws.com
GLOBAL_S3_REGION=eu-west-1
AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
```

The Storage container uses the AWS SDK default credential chain. On EC2, that means **temporary credentials from the instance IAM role** (no long-lived keys in `.env`).

**Docker + IMDS note:** Containers must reach the EC2 instance metadata service (`169.254.169.254`). If Storage fails with credential errors, set the instance **metadata hop limit** to `2`:

```bash
aws ec2 modify-instance-metadata-options \
  --instance-id <your-ec2-instance-id> \
  --http-put-response-hop-limit 2 \
  --region eu-west-1
```

Verify from the host (should already work if `aws s3 ls` works):

```bash
aws sts get-caller-identity
aws s3 ls s3://datafy-prod-supabase-storage/ --region eu-west-1
```

After `docker compose up`, test from inside the storage container:

```bash
docker compose exec storage env | grep -E 'AWS_|S3|STORAGE'
```

#### Option B — IAM user + access keys (fallback)

Use this only if Option A does not work (e.g. containers cannot use instance metadata).

```bash
aws iam create-user --user-name supabase-storage

aws iam put-user-policy \
  --user-name supabase-storage \
  --policy-name supabase-s3-access \
  --policy-document '{
    "Version": "2012-10-17",
    "Statement": [{
      "Effect": "Allow",
      "Action": ["s3:*"],
      "Resource": [
        "arn:aws:s3:::datafy-prod-supabase-storage",
        "arn:aws:s3:::datafy-prod-supabase-storage/*"
      ]
    }]
  }'

aws iam create-access-key --user-name supabase-storage
```

Save the **AccessKeyId** and **SecretAccessKey** — shown once — and put them in `.env`:

```env
AWS_ACCESS_KEY_ID=<access-key>
AWS_SECRET_ACCESS_KEY=<secret-key>
```

### 3.3 Values for `.env` (Step 6)

**Using EC2 IAM role (preferred):**

```env
STORAGE_BACKEND=s3
GLOBAL_S3_BUCKET=datafy-prod-supabase-storage
GLOBAL_S3_ENDPOINT=https://s3.eu-west-1.amazonaws.com
GLOBAL_S3_REGION=eu-west-1
AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=
```

**Using IAM user keys (fallback):**

```env
STORAGE_BACKEND=s3
GLOBAL_S3_BUCKET=datafy-prod-supabase-storage
GLOBAL_S3_ENDPOINT=https://s3.eu-west-1.amazonaws.com
GLOBAL_S3_REGION=eu-west-1
AWS_ACCESS_KEY_ID=<from supabase-storage IAM user>
AWS_SECRET_ACCESS_KEY=<from supabase-storage IAM user>
```

### New bucket only (skip if using `datafy-prod-supabase-storage`)

```bash
aws s3api create-bucket \
  --bucket <your-bucket-name> \
  --region eu-west-1 \
  --create-bucket-configuration LocationConstraint=eu-west-1

aws s3api put-public-access-block \
  --bucket <your-bucket-name> \
  --region eu-west-1 \
  --public-access-block-configuration \
    BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true
```

---

## Step 4 — Amazon SES for email

1. SES → Verified Identities → verify your domain (add DKIM DNS records)
2. Request **production access** (sandbox only allows verified recipients)
3. SES → SMTP Settings → Create SMTP credentials (save username and password — shown once)

```env
SMTP_HOST=email-smtp.eu-west-1.amazonaws.com
SMTP_PORT=587
SMTP_USER=<ses-smtp-username>
SMTP_PASS=<ses-smtp-password>
SMTP_SENDER_NAME=Datafy
SMTP_ADMIN_EMAIL=noreply@yourdomain.com
```

---

## Step 5 — EC2 instance

### Launch

- AMI: Ubuntu Server 24.04 LTS
- Instance type: `t3.large` minimum
- Storage: 30 GB GP3 root volume
- Security group: `supabase-ec2-sg`
- Elastic IP: allocate and associate

### Install tooling

```bash
ssh -i your-key.pem ubuntu@<elastic-ip>

sudo apt update && sudo apt upgrade -y
curl -fsSL https://get.docker.com | sh
sudo usermod -aG docker ubuntu
newgrp docker

sudo apt install -y git nginx certbot python3-certbot-nginx awscli jq postgresql-client
```

### EC2 IAM role

Attach an instance role with at least:

- `secretsmanager:GetSecretValue` on the RDS secret (and later `supabase/production/env`)
- S3 access to `datafy-prod-supabase-storage` (see Step 3.2 Option A — lets Storage use the instance role instead of static keys)

```json
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": ["secretsmanager:GetSecretValue"],
      "Resource": [
        "arn:aws:secretsmanager:eu-west-1:926753675240:secret:rds!db-*",
        "arn:aws:secretsmanager:eu-west-1:926753675240:secret:supabase/production/env-*"
      ]
    },
    {
      "Effect": "Allow",
      "Action": ["s3:ListBucket", "s3:GetObject", "s3:PutObject", "s3:DeleteObject"],
      "Resource": [
        "arn:aws:s3:::datafy-prod-supabase-storage",
        "arn:aws:s3:::datafy-prod-supabase-storage/*"
      ]
    }
  ]
}
```

---



## Connect to the database from EC2

Use this whenever you need to run SQL on RDS (extensions, roles, debugging). Always connect through the **RDS Proxy**, not the direct RDS hostname.

### Prerequisites


| Requirement                                                      | Check                             |
| ---------------------------------------------------------------- | --------------------------------- |
| EC2 instance running in the same VPC as RDS                      | Step 5 complete                   |
| `postgresql-client` installed                                    | `psql --version`                  |
| `awscli` and `jq` installed                                      | `aws --version` && `jq --version` |
| EC2 IAM role can read the RDS secret                             | Step 5 IAM role                   |
| Proxy SG (`sg-013487b46ffc5fce8`) allows EC2 SG on port **5432** | Step 1                            |


### 1. SSH into the EC2 instance

From your local machine:

```bash
ssh -i your-key.pem ubuntu@<elastic-ip>
```

### 2. Install `psql` (if not already installed)

If you see `psql: not found`, install the PostgreSQL client:

```bash
sudo apt update
sudo apt install -y postgresql-client

# Verify
psql --version
```

You should see something like `psql (PostgreSQL 16.x ...)`.

### 3. Fetch credentials from Secrets Manager

The RDS managed secret stores `**username` and `password` only** — it does not include host, port, or database name. Those are fixed for this setup:


| Setting  | Value                                                                  | Source                       |
| -------- | ---------------------------------------------------------------------- | ---------------------------- |
| Host     | `datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com` | RDS Proxy (not in secret)    |
| Port     | `5432`                                                                 | Fixed                        |
| Database | `postgres`                                                             | Fixed                        |
| Username | e.g. `postgres`                                                        | Secrets Manager → `username` |
| Password | rotates every 7 days                                                   | Secrets Manager → `password` |


Fetch username and password (always get the latest before connecting):

```bash
SECRET_JSON=$(aws secretsmanager get-secret-value \
  --secret-id "rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c" \
  --region eu-west-1 \
  --query SecretString --output text)

export DB_USER=$(echo "$SECRET_JSON" | jq -r '.username')
export DB_PASS=$(echo "$SECRET_JSON" | jq -r '.password')

# Optional: confirm what the secret contains (username + password only)
echo "$SECRET_JSON" | jq '{username, password: "***"}'
```

Set connection variables — host/port/database are **not** in the secret:

```bash
export PGHOST=datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com
export PGPORT=5432
export PGDATABASE=postgres
export PGUSER="$DB_USER"
export PGPASSWORD="$DB_PASS"
```

### 4. Connect with psql

**Option A — connection string (recommended):**

```bash
psql "host=${PGHOST} port=${PGPORT} dbname=${PGDATABASE} user=${PGUSER} sslmode=require"
```

**Option B — short form (uses `PG`* env vars from above):**

```bash
psql
```

**Option C — one-liner without exporting variables:**

```bash
SECRET_JSON=$(aws secretsmanager get-secret-value \
  --secret-id "rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c" \
  --region eu-west-1 \
  --query SecretString --output text)

PGPASSWORD="$(echo "$SECRET_JSON" | jq -r '.password')" psql \
  "host=datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com \
   port=5432 dbname=postgres user=$(echo "$SECRET_JSON" | jq -r '.username') sslmode=require"
```

You should see a `postgres=>` prompt. Test with:

```sql
SELECT version();
\conninfo
\q
```

### 5. Run SQL from a file

Save your SQL (e.g. extensions and roles from Step 2) to a file on EC2, then:

```bash
psql "host=${PGHOST} port=${PGPORT} dbname=${PGDATABASE} user=${PGUSER} sslmode=require" \
  -f ~/setup-extensions.sql
```

Or paste SQL directly at the `postgres=>` prompt after connecting.

### 6. Helper script (optional)

Save on EC2 as `~/connect-db.sh` for quick access:

```bash
#!/bin/bash
set -euo pipefail

SECRET_ID="rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c"
PROXY_HOST="datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com"
DB_PORT=5432
DB_NAME=postgres
REGION="eu-west-1"

SECRET_JSON=$(aws secretsmanager get-secret-value \
  --secret-id "$SECRET_ID" \
  --region "$REGION" \
  --query SecretString --output text)

DB_USER=$(echo "$SECRET_JSON" | jq -r '.username')
DB_PASS=$(echo "$SECRET_JSON" | jq -r '.password')

PGPASSWORD="$DB_PASS" psql \
  "host=${PROXY_HOST} port=${DB_PORT} dbname=${DB_NAME} user=${DB_USER} sslmode=require" \
  "$@"
```

```bash
chmod +x ~/connect-db.sh
~/connect-db.sh                    # interactive session
~/connect-db.sh -c "SELECT 1;"     # run a single command
```

### Troubleshooting


| Symptom                              | Likely cause                                                | Fix                                                                                    |
| ------------------------------------ | ----------------------------------------------------------- | -------------------------------------------------------------------------------------- |
| `extension "pgjwt" is not available` | RDS does not ship pgjwt                                     | Skip `CREATE EXTENSION`; run `2-pgjwt.sql` to create the `jwt` schema                  |
| `extensions.hmac does not exist`     | `pgcrypto` still in `public` (`IF NOT EXISTS` skipped move) | `ALTER EXTENSION pgcrypto SET SCHEMA extensions` or drop/recreate — see Step B above   |
| `jwt.algorithm_sign does not exist`  | Cascading failure from missing `extensions.hmac`            | Fix `extensions` schema first (above), `DROP SCHEMA jwt CASCADE`, re-run `2-pgjwt.sql` |
| `psql: not found`                    | Client not installed                                        | `sudo apt update && sudo apt install -y postgresql-client`                             |
| `psql: command not found`            | Same as above                                               | `sudo apt install -y postgresql-client` then `psql --version`                          |
| `timeout` / `could not connect`      | Proxy SG blocking EC2                                       | Add inbound 5432 on proxy SG from EC2 SG                                               |
| `password authentication failed`     | Stale password                                              | Re-fetch from Secrets Manager (`export DB_PASS=...`)                                   |
| `AccessDeniedException` on secret    | EC2 IAM role missing permission                             | Attach `secretsmanager:GetSecretValue` on the secret ARN                               |
| `SSL error`                          | TLS required by proxy                                       | Add `sslmode=require` to the connection string                                         |
| Connected to wrong host              | Using RDS endpoint instead of proxy                         | Use `datafy-prod-rds-proxy.proxy-....rds.amazonaws.com`                                |


> **Note:** Do not connect to the direct RDS endpoint (`datafy-prod-postgres....rds.amazonaws.com`) from Supabase or routine admin work — use the proxy so credential rotation and connection pooling work correctly.

---

## Step 6 — Supabase Docker Compose (RDS Proxy)

This is the main difference from the original guide: use the **proxy hostname** and handle **password rotation**.

### Directory layout (official Supabase approach)

Follow the [official self-hosting docs](https://supabase.com/docs/guides/self-hosting/docker): clone the repo for reference, then copy compose files into a separate project directory. **All `docker compose` commands run from `supabase-project/`**, not from inside the cloned repo.

```
/home/ubuntu/
├── supabase/              # cloned repo (reference only — do not edit in place)
└── supabase-project/      # your working project
    ├── docker-compose.yml
    ├── .env
    └── volumes/
        └── api/
            └── kong.yml
```

### 6.1 Pre-flight — test proxy connectivity

Confirm you can reach the database before starting Docker. Full instructions are in **Connect to the database from EC2** (search this file for `connect-ec2` or use the Contents list at the top). Quick test:

```bash
SECRET_JSON=$(aws secretsmanager get-secret-value \
  --secret-id "rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c" \
  --region eu-west-1 \
  --query SecretString --output text)

DB_USER=$(echo "$SECRET_JSON" | jq -r '.username')
DB_PASS=$(echo "$SECRET_JSON" | jq -r '.password')

PGPASSWORD="$DB_PASS" psql \
  "host=datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com \
   port=5432 dbname=postgres user=${DB_USER} sslmode=require" \
  -c "SELECT 1 AS connected;"
```

If this fails, fix proxy security group or credentials before continuing.

### 6.2 Clone and prepare

Run from your EC2 home directory (e.g. `/home/ubuntu`):

```bash
# Get the code
git clone --depth 1 https://github.com/supabase/supabase

# Create your project directory (sibling to the clone)
mkdir supabase-project

# Copy compose files into your project
cp -rf supabase/docker/* supabase-project/

# Copy the example environment file
cp supabase/docker/.env.example supabase-project/.env

# Switch to your project directory — stay here for all docker compose commands
cd supabase-project
```

Optional (fresh DB only):

```bash
cd ~
git clone https://github.com/agblox/supabase-postgres
cd supabase-postgres/rds
./provision.sh \
  datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com \
  5432 postgres postgres <password-from-secrets-manager>
cd ~/supabase-project
```

### 6.3 Generate API keys

```bash
openssl rand -base64 40   # JWT_SECRET
```

Generate `ANON_KEY` and `SERVICE_ROLE_KEY` at:
[https://supabase.com/docs/guides/self-hosting/docker#generate-api-keys](https://supabase.com/docs/guides/self-hosting/docker#generate-api-keys)

From `~/supabase-project`, update `volumes/api/kong.yml` — replace default `anon` and `service_role` keys in the `consumers:` section.

### 6.4 Configure `.env`

```env
############################################################
# SECRETS
############################################################
POSTGRES_PASSWORD=<from-secrets-manager>
JWT_SECRET=<generated>
ANON_KEY=<generated>
SERVICE_ROLE_KEY=<generated>
DASHBOARD_USERNAME=admin
DASHBOARD_PASSWORD=<strong-password>

############################################################
# SITE / API
############################################################
SITE_URL=https://yourdomain.com
API_EXTERNAL_URL=https://yourdomain.com
SUPABASE_PUBLIC_URL=https://yourdomain.com

############################################################
# DATABASE — RDS Proxy (not direct RDS endpoint)
############################################################
POSTGRES_HOST=datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com
POSTGRES_PORT=5432
POSTGRES_DB=postgres

############################################################
# S3 STORAGE
############################################################
STORAGE_BACKEND=s3
GLOBAL_S3_BUCKET=datafy-prod-supabase-storage
GLOBAL_S3_ENDPOINT=https://s3.eu-west-1.amazonaws.com
GLOBAL_S3_REGION=eu-west-1
# Leave empty if using EC2 IAM role (recommended); set only if using IAM user keys
AWS_ACCESS_KEY_ID=
AWS_SECRET_ACCESS_KEY=

############################################################
# EMAIL — SES
############################################################
SMTP_ADMIN_EMAIL=noreply@yourdomain.com
SMTP_HOST=email-smtp.eu-west-1.amazonaws.com
SMTP_PORT=587
SMTP_USER=<ses-smtp-username>
SMTP_PASS=<ses-smtp-password>
SMTP_SENDER_NAME=Datafy
```

Fetch password into `.env` (run from `~/supabase-project`). Only `password` comes from the secret — host/port/database are set manually in `.env`:

```bash
cd ~/supabase-project

DB_PASS=$(aws secretsmanager get-secret-value \
  --secret-id "rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c" \
  --region eu-west-1 \
  --query SecretString --output text | jq -r '.password')

sed -i "s/^POSTGRES_PASSWORD=.*/POSTGRES_PASSWORD=${DB_PASS}/" .env
```

### 6.5 Disable containerized Postgres

In `~/supabase-project/docker-compose.yml`:

- Comment out the `db:` service block, **or** add `profiles: [local-only]` so it does not start by default
- Remove `db` from `depends_on` on other services

Supabase services read `POSTGRES_HOST` and `POSTGRES_PASSWORD` from `.env` and connect through the proxy.

### 6.6 Password rotation automation

RDS Proxy reads the rotated password from Secrets Manager automatically. Supabase `.env` and multiple DB roles **do not**.

Supabase uses one `POSTGRES_PASSWORD` for several database users:


| Service          | DB user                  |
| ---------------- | ------------------------ |
| Auth (GoTrue)    | `supabase_auth_admin`    |
| REST (PostgREST) | `authenticator`          |
| Storage          | `supabase_storage_admin` |
| Realtime         | `supabase_admin`         |
| Studio / meta    | `postgres`               |


Rotation schedule: every **7 days** (managed secret `rds!db-d6f075f5-...`).

Create `/home/ubuntu/start-supabase.sh`:

```bash
#!/bin/bash
set -euo pipefail

COMPOSE_DIR=/home/ubuntu/supabase-project
PROXY_HOST=datafy-prod-rds-proxy.proxy-c7aqcoiku4hr.eu-west-1.rds.amazonaws.com
DB_PORT=5432
DB_NAME=postgres
SECRET_ID="rds!db-d6f075f5-94d0-4ce6-8162-a3b20397d94c"
REGION=eu-west-1

cd "$COMPOSE_DIR"

SECRET_JSON=$(aws secretsmanager get-secret-value \
  --secret-id "$SECRET_ID" \
  --region "$REGION" \
  --query SecretString --output text)

DB_USER=$(echo "$SECRET_JSON" | jq -r '.username')
DB_PASS=$(echo "$SECRET_JSON" | jq -r '.password')

# Update .env (password only — host/port/db are static in .env)
sed -i "s/^POSTGRES_PASSWORD=.*/POSTGRES_PASSWORD=${DB_PASS}/" .env

# Sync Supabase role passwords after rotation (ignore errors if role missing)
export PGPASSWORD="$DB_PASS"
for ROLE in authenticator supabase_auth_admin supabase_storage_admin supabase_admin; do
  psql "host=${PROXY_HOST} port=${DB_PORT} dbname=${DB_NAME} user=${DB_USER} sslmode=require" \
    -c "ALTER USER ${ROLE} WITH PASSWORD '${DB_PASS}';" 2>/dev/null || true
done

docker compose pull
docker compose up -d
```

```bash
chmod +x /home/ubuntu/start-supabase.sh
```

Add a daily cron to catch rotation between restarts:

```bash
crontab -e
# Run at 08:00 UTC daily
0 8 * * * /home/ubuntu/start-supabase.sh >> /var/log/supabase-start.log 2>&1
```

### 6.7 Start Supabase

```bash
# Pull latest images (first time or after updates)
cd ~/supabase-project
docker compose pull

# Start via rotation-aware script (recommended)
/home/ubuntu/start-supabase.sh

# Or start manually from the project directory
cd ~/supabase-project
docker compose up -d

docker compose ps
docker compose logs -f   # Ctrl+C to exit
```

Studio (before Nginx): `http://<elastic-ip>:3000`

---

## Step 7 — Nginx + HTTPS

### DNS

Create A records pointing to the Elastic IP:

```
yourdomain.com      → <elastic-ip>
*.yourdomain.com    → <elastic-ip>   (optional)
```

### Nginx config

```bash
sudo nano /etc/nginx/sites-available/supabase
```

```nginx
server {
    listen 80;
    server_name yourdomain.com;
    location / { return 301 https://$host$request_uri; }
}

server {
    listen 443 ssl;
    server_name yourdomain.com;

    ssl_certificate /etc/letsencrypt/live/yourdomain.com/fullchain.pem;
    ssl_certificate_key /etc/letsencrypt/live/yourdomain.com/privkey.pem;
    ssl_protocols TLSv1.2 TLSv1.3;

    location /studio {
        proxy_pass http://localhost:3000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
    }

    location / {
        proxy_pass http://localhost:8000;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_http_version 1.1;
        proxy_set_header Upgrade $http_upgrade;
        proxy_set_header Connection "upgrade";
        proxy_read_timeout 3600;
    }
}
```

```bash
sudo ln -s /etc/nginx/sites-available/supabase /etc/nginx/sites-enabled/
sudo nginx -t
sudo certbot --nginx -d yourdomain.com
sudo systemctl reload nginx
```

---

## Step 8 — Secrets Manager for `.env`

Store the full `.env` in Secrets Manager instead of leaving secrets only on disk:

```bash
aws secretsmanager create-secret \
  --name supabase/production/env \
  --secret-string file:///home/ubuntu/supabase-project/.env \
  --region eu-west-1
```

Update `start-supabase.sh` to pull `.env` before applying the DB password:

```bash
aws secretsmanager get-secret-value \
  --secret-id supabase/production/env \
  --region eu-west-1 \
  --query SecretString --output text > /home/ubuntu/supabase-project/.env
```

---

## Step 9 — Backups and monitoring

### RDS

- Automated backups: 7-day retention minimum
- Point-in-time recovery enabled
- Manual snapshot before major migrations

### CloudWatch alarms

Monitor: `CPUUtilization`, `FreeStorageSpace`, `DatabaseConnections`, `FreeableMemory`

### Docker watchdog cron

```bash
*/5 * * * * cd /home/ubuntu/supabase-project && docker compose up -d >> /var/log/supabase-watchdog.log 2>&1
```

---

## Step 10 — Verify deployment

```bash
cd ~/supabase-project

# Containers running
docker compose ps

# REST API
curl https://yourdomain.com/rest/v1/ \
  -H "apikey: <anon-key>"

# Auth
curl https://yourdomain.com/auth/v1/settings \
  -H "apikey: <anon-key>"

# Storage
curl https://yourdomain.com/storage/v1/bucket \
  -H "Authorization: Bearer <service-role-key>"

# Studio
open https://yourdomain.com/studio
```

---

## Common issues


| Issue                                | Fix                                                                                |
| ------------------------------------ | ---------------------------------------------------------------------------------- |
| `docker compose` can't find config   | Run commands from `~/supabase-project`, not `~/supabase/docker`                    |
| Containers keep restarting           | `cd ~/supabase-project && docker compose logs <service>` — usually DB auth failure; auth specifically: `docs/supabase-auth-container-logs.md` |
| Cannot connect to database           | Check proxy SG allows EC2 SG on 5432; verify password from Secrets Manager         |
| Auth works then breaks after ~7 days | Password rotated; run `start-supabase.sh` or fix cron                              |
| Auth emails not sending              | Verify SES domain; confirm out of sandbox                                          |
| Storage upload fails                 | Check S3 IAM key and bucket policy                                                 |
| Send SMS / phone OTP                 | `docs/supabase-auth-hook-send-sms-lambda.md` — GoTrue hook → Lambda via API Gateway |
| Realtime not working                 | Confirm `wal_level=logical`; Nginx must allow WebSocket upgrade on 443             |
| Studio 403                           | Check `DASHBOARD_USERNAME` / `DASHBOARD_PASSWORD` in `.env`                        |


---

## Differences from original guide


| Original guide                          | This setup                                                       |
| --------------------------------------- | ---------------------------------------------------------------- |
| Work in `supabase/docker/` directly     | Copy to `supabase-project/` per official docs; run compose there |
| `POSTGRES_HOST=<rds>.rds.amazonaws.com` | `POSTGRES_HOST=<proxy>.proxy-....rds.amazonaws.com`              |
| Static `POSTGRES_PASSWORD` in `.env`    | Fetch `password` from Secrets Manager; host/port/db set manually |
| RDS SG only                             | **Proxy SG** must allow EC2 on 5432                              |
| `af-south-1` examples                   | `eu-west-1`                                                      |
| Single postgres user                    | Multiple Supabase DB roles share `POSTGRES_PASSWORD`             |


---

## Related

- Lambda functions in this repo read Supabase URL/keys from SSM (`/supabase/url`, `/supabase/anon`, `/supabase/service_role`) — update those after deployment.
- [agblox/supabase-postgres RDS README](https://github.com/agblox/supabase-postgres/blob/develop/rds/README.md)
- [Supabase self-hosting Docker docs](https://supabase.com/docs/guides/self-hosting/docker)