zopu-code/.agents/skills/per-tenant-database/reference/self-hosting/postgres.md

# PostgreSQL

> Source: `src/content/docs/self-hosting/postgres.mdx`
> Canonical URL: https://rivet.dev/docs/self-hosting/postgres
> Description: Configure PostgreSQL for self-hosted Rivet deployments.

---
PostgreSQL is the recommended backend for multi-node self-hosted deployments today, but it remains experimental. For a production-ready single-node Rivet deployment, use the file system backend (RocksDB-based). Enterprise teams can contact [enterprise support](https://rivet.dev/sales) about FoundationDB for the most scalable production-ready deployment.

## Basic Configuration

```json Configuration-file
{
  "database": {
    "postgres": {
      "url": "postgresql://user:password@host:5432/database"
    }
  }
}
```

```bash Environment-variables
RIVET__database__postgres__url="postgresql://user:password@host:5432/database"
```

## Managed Postgres Compatibility

Some hosted PostgreSQL platforms require additional configuration due to platform-specific restrictions.

### PlanetScale

Use direct connection (not connection pooler).

```json Configuration-file
{
  "database": {
    "postgres": {
      "url": "postgresql://pscale_api_<username>.<unique-id>:<password>@<region>.pg.psdb.cloud:5432/postgres?sslmode=require",
      "unstable_disable_lock_customization": true
    }
  }
}
```

```bash Environment-variables
RIVET__database__postgres__url="postgresql://pscale_api_<username>.<unique-id>:<password>@<region>.pg.psdb.cloud:5432/postgres?sslmode=require"
RIVET__database__postgres__unstable_disable_lock_customization=true
```

### Supabase

Use direct connection on port `5432` (not connection pooler).

#### Without SSL

```json Configuration-file
{
  "database": {
    "postgres": {
      "url": "postgresql://postgres:<password>@db.<project-ref>.supabase.co:5432/postgres?sslmode=disable",
      "unstable_disable_lock_customization": true
    }
  }
}
```

```bash Environment-variables
RIVET__database__postgres__url="postgresql://postgres:<password>@db.<project-ref>.supabase.co:5432/postgres?sslmode=disable"
RIVET__database__postgres__unstable_disable_lock_customization=true
```

#### With SSL

Download the root certificate from your Supabase dashboard and specify its path. See [Supabase SSL Enforcement](https://supabase.com/docs/guides/platform/ssl-enforcement) for details.

```json Configuration-file
{
  "database": {
    "postgres": {
      "url": "postgresql://postgres:<password>@db.<project-ref>.supabase.co:5432/postgres?sslmode=require",
      "unstable_disable_lock_customization": true,
      "ssl": {
        "root_cert_path": "/path/to/supabase-ca.crt"
      }
    }
  }
}
```

```bash Environment-variables
RIVET__database__postgres__url="postgresql://postgres:<password>@db.<project-ref>.supabase.co:5432/postgres?sslmode=require"
RIVET__database__postgres__unstable_disable_lock_customization=true
RIVET__database__postgres__ssl__root_cert_path="/path/to/supabase-ca.crt"
```

## SSL/TLS Support

To enable SSL for Postgres, add `sslmode=require` to your PostgreSQL connection URL:

```json Configuration-file
{
  "database": {
    "postgres": {
      "url": "postgresql://user:password@host.example.com:5432/database?sslmode=require"
    }
  }
}
```

```bash Environment-variables
RIVET__database__postgres__url="postgresql://user:password@host.example.com:5432/database?sslmode=require"
```

The `sslmode` parameter controls TLS usage:

- `disable`: Do not use TLS
- `prefer`: Use TLS if available, otherwise connect without TLS (default)
- `require`: Require TLS connection (fails if TLS is not available)

To verify the server certificate against a CA or verify the hostname, use custom SSL certificates (see below).

### Custom SSL Certificates

For databases using custom certificate authorities (e.g., Supabase) or requiring client certificate authentication, you can specify certificate paths in the configuration:

```json Configuration-file
{
  "database": {
    "postgres": {
      "url": "postgresql://user:password@host:5432/database?sslmode=require",
      "ssl": {
        "root_cert_path": "/path/to/root-ca.crt",
        "client_cert_path": "/path/to/client.crt",
        "client_key_path": "/path/to/client.key"
      }
    }
  }
}
```

```bash Environment-variables
RIVET__database__postgres__url="postgresql://user:password@host:5432/database?sslmode=require"
RIVET__database__postgres__ssl__root_cert_path="/path/to/root-ca.crt"
RIVET__database__postgres__ssl__client_cert_path="/path/to/client.crt"
RIVET__database__postgres__ssl__client_key_path="/path/to/client.key"
```

| Parameter | Description | PostgreSQL Equivalent |
|-----------|-------------|----------------------|
| `root_cert_path` | Path to the root certificate file for verifying the server's certificate | `sslrootcert` |
| `client_cert_path` | Path to the client certificate file for client certificate authentication | `sslcert` |
| `client_key_path` | Path to the client private key file for client certificate authentication | `sslkey` |

All SSL paths are optional. If not specified, Rivet uses the default system root certificates from Mozilla's root certificate store.

## Do Not Use Connection Poolers

Rivet requires direct PostgreSQL connections for session-level features and does not support connection poolers.

Do not use:

- PgBouncer
- Supavisor
- AWS RDS Proxy

## Troubleshooting

### Permission Denied Errors

If you see errors like:

```
ERROR: permission denied to set parameter "deadlock_timeout"
ERROR: current transaction is aborted, commands ignored until end of transaction block
```

Add `unstable_disable_lock_customization: true` to your configuration:

```json
{
  "database": {
    "postgres": {
      "url": "postgresql://...",
      "unstable_disable_lock_customization": true
    }
  }
}
```

This disables Rivet's attempt to set `lock_timeout = 0` and `deadlock_timeout = 10ms`. Since `lock_timeout` defaults to `0` in PostgreSQL, skipping these settings is safe. Deadlock detection will use the default `1s` timeout instead of `10ms`.

_Source doc path: /docs/self-hosting/postgres_