# Connect to PostgreSQL


This guide covers everything about reaching your PostgreSQL database in Zerops: connection details, ports and TLS, connection pooling, connecting from other services or from your workstation, and the database tools you can use.

## Connection options at a glance

- **Internal**: between services in the same project, over the private network.
- **Remote**: from outside Zerops, either through the [VPN](#connect-via-zerops-vpn) (your machine joins the private network) or through [public IP access](#connect-via-public-ip) (TLS through pgBouncer).

## Connection details

Find your connection details in the service detail page under **Peek access details** (hostname, port, user, password, connection string). The full list of connection-related environment variables lives under **Environment variables** in the same service detail.

### Connection parameters

| Parameter | Internal | Public IP (TLS) | Env Variable |
| --- | --- | --- | --- |
| Hostname | Service hostname | Public IP address | `hostname` |
| Port (primary) | 5432 | 6432 (via pgBouncer) | `port` / `portTls` |
| Port (replicas, HA only) | 5433 | N/A | `portReplicas` |
| User | `db` (default) | Same as internal | `user` |
| Password | Generated at creation | Same as internal | `password` |
| Connection string (primary) | `postgresql://:@:5432/` | Same format with TLS port | `connectionString` / `connectionTlsString` |
| Connection string (replicas, HA only) | `postgresql://:@:5433/` | N/A | `connectionStringReplicas` |
| Database name | `db` (default) | Same as internal | `dbName` |

:::warning
Zerops creates a system user named `zps` with full privileges for maintenance. Do not delete it, change its password, or remove its privileges, as doing so disrupts Zerops' ability to maintain the database cluster.
:::

## Connection ports and TLS

PostgreSQL on Zerops exposes three ports, each for a different purpose:

| Port | Purpose | TLS |
| --- | --- | --- |
| `5432` | Primary node (read and write) | Not supported (plaintext) |
| `5433` | Read-only replicas, distributing reads (HA mode only) | Not supported (plaintext) |
| `6432` | pgBouncer connection pooler | **Required** |

- **Ports `5432` and `5433` do not support TLS.** Connect with `sslmode=disable`. Inside a project these ports are reachable only on the private network, and over the [VPN](#connect-via-zerops-vpn) the tunnel already encrypts the traffic, so requesting TLS on them fails the handshake.
- **Port `6432` (pgBouncer) requires TLS.** Connect with at least `sslmode=require`; the connection is always encrypted. pgBouncer presents a certificate signed by the [Zerops CA](/references/networking/zerops-ca) but does not force the client to validate it, so `sslmode=require` is enough. If you want to verify the server's identity, use `sslmode=verify-full` together with the Zerops CA. This holds whether you reach `6432` from inside the project or over a public IP.

## Connection pooling with pgBouncer

Port `6432` puts [pgBouncer](https://www.pgbouncer.org/) in front of PostgreSQL and multiplexes many client connections onto a small pool of server connections. This is valuable for applications that open many short-lived connections (serverless functions, PHP-FPM, or anything with high connection churn), and **we recommend connecting through `6432` rather than `5432` for these workloads, including for internal service-to-service connections.**

A few things to know:

- **Transaction pooling.** A server connection is returned to the pool after each transaction, not when the client disconnects. Prepared statements are supported (cached per server connection), but session-scoped features that span transactions (session-level `SET`, advisory locks held across statements, `LISTEN`/`NOTIFY`) won't behave as expected. Use a direct `5432` connection for those.
- **TLS is required** on `6432` (see [above](#connection-ports-and-tls)), even for internal connections.
- **HA mode.** pgBouncer pools connections to the primary (writes). Read routing across replicas on port `5433` is separate and is not pooled.

## Connect from services in the same project

All services in a project share a private network, so other services reach PostgreSQL directly by its hostname. There are two ways to wire it up.

### Direct connection parameters

Use the parameters from **Peek access details**:

```ini
host = db          # your PostgreSQL service hostname
port = 5432        # 5433 for read-only replicas in HA mode
user = db          # default user
password = **********
```

### Environment variables (recommended)

Zerops generates connection environment variables for every PostgreSQL service. To use one service's variables from another, prefix the variable name with the service hostname and an underscore. For example, to read the `connectionString` of service `db`, reference `db_connectionString`.

For read-only connections (HA mode only), use `connectionStringReplicas` instead.

See the [Environment Variables](/features/env-variables) documentation for details and for adding your own variables.

:::caution Important notes
- When changing a password, update both the database user and the environment variable, since they don't synchronize automatically.
- Zerops uses the `postgresql://` URI scheme. If your software requires the shorter `postgres://`, create a custom environment variable with that format.
- Internal connections on `5432`/`5433` don't use SSL/TLS. Security comes from the private network. For TLS-encrypted [connection pooling](#connection-pooling-with-pgbouncer), connect to pgBouncer on `6432`.
:::

## Connect remotely

There are two ways to reach PostgreSQL from outside Zerops: the VPN (recommended for development) and public IP access (for external applications).

### Connect via Zerops VPN

The VPN puts your workstation on the project's private network, so you connect exactly as an internal service would.

1. [Install & set up zCLI](/references/cli)
2. [Start the Zerops VPN](/references/networking/vpn#start-vpn)
3. Use the connection details from **Peek access details** in the service detail
4. When finished, [stop the Zerops VPN](/references/networking/vpn#stop-vpn)

:::warning Important notes
* Connect to `5432`/`5433` without SSL/TLS over the VPN, since the tunnel already encrypts the traffic.
* Environment variables are **not** available over the VPN; copy the access details from the GUI.
* If a connection doesn't work, try the `.zerops` suffix on the hostname (e.g. `db.zerops`). See the [VPN troubleshooting page](/references/networking/vpn#troubleshooting).
:::

### Connect via public IP

Public access always goes through pgBouncer on port `6432` over TLS (see [Connection ports and TLS](#connection-ports-and-tls)), which also pools your connections. The read-only replica port (`5433`) is **not** exposed publicly. Route reads through your application logic instead.

:::tip Trusting the TLS certificate
The certificate on `6432` is signed by the Zerops Certificate Authority. To verify it from outside Zerops, download and trust the [Zerops CA](/references/networking/zerops-ca), for example `psql "... sslmode=verify-full sslrootcert=./zerops-ca.pem"`.
:::

To enable public access:

1. Open your PostgreSQL service in the GUI and go to **Public Access through IP Addresses**
2. Choose IPv6 (available by default) or IPv4 (requires the [unique IPv4](/references/networking/public-access#ipv4-configuration) add-on)
3. Open one or more ports pointing to your service (routed through pgBouncer):
   - Any port from 10–65435 (except 80 and 443)
   - Each public port can map to any internal port; multiple public ports can share one internal port
   - IPv4 and IPv6 can be configured independently
4. Optionally enable firewall protection
5. Click **Publish X IP access change(s)** to apply

## Database management tools

You can use any PostgreSQL-compatible tool to administer your database. For a zero-setup option, Zerops provides a ready-to-use recipe for [Adminer](https://www.adminer.org), a lightweight, full-featured web-based tool that supports PostgreSQL.

### Install Adminer

In the GUI, open your project, select **Import services**, and paste:

```yaml title="zerops-import.yaml"
services:
  - hostname: adminer
    type: php-apache@8.5
    maxContainers: 1
    enableSubdomainAccess: true
    buildFromGit: https://github.com/zeropsio/recipe-adminer
```

Then [start the VPN](/references/networking/vpn) and open `http://adminer` in your browser (try `http://adminer.zerops` if it doesn't resolve).

:::caution
Do not use `https` when reaching management tools over the VPN.
:::

### Desktop tools and psql

Popular desktop clients (pgAdmin, DBeaver, DataGrip, or any PostgreSQL-compatible tool) work with Zerops over the VPN:

1. [Start the Zerops VPN](/references/networking/vpn) to open an encrypted tunnel to your project
2. Copy the [connection details](#connection-details) from the GUI (environment variables aren't available over the VPN)
3. Connect without SSL/TLS on `5432`/`5433`, since the tunnel already encrypts the connection

For the `psql` command-line client:

```sh
psql -h [hostname] -U [user] -d [database_name]
```

`psql` has no password flag, so it prompts you. To pass the password non-interactively, use `PGPASSWORD`:

```sh
PGPASSWORD=[password] psql -h [hostname] -U [user] -d [database_name]
```

:::tip
Try `[hostname].zerops` instead of `[hostname]` if you hit connection issues over the VPN.
:::

To move data in and out with these tools, see [Export & import data](/postgresql/how-to/manage#export--import-data).