Connect to PostgreSQL in Zerops

This guide covers how to connect to your PostgreSQL database in Zerops, both from services within the same project and from outside the Zerops environment.

Connection Options Overview

Zerops provides several ways to connect to PostgreSQL:

1. Internal connections - Between services in the same Zerops project (via private network)
2. Remote connections:
   • VPN access - From your local machine via Zerops VPN
   • Direct IP access - Enables external applications to connect using TLS encryption by opening public ports on IPv6 (available by default) or IPv4 (requires add-on activation if not already enabled)

Connection Details

You'll find internal PostgreSQL connection details in two places in the Zerops GUI:

1. Under the Access details button in the project dashboard
2. In the service detail page under the Peek access details button

Connection Parameters

Parameter	Internal Connection	Direct IP Access (TLS)
Hostname/IP	Service hostname	Public IP address
Port	5432	6432
User	Identical to the service hostname	Same as internal
Password	Randomly generated during service creation	Same as internal
Port env variable	port	portTls
Connection string env variable	connectionString	connectionTlsString

Warning

Zerops creates a system user named zps with full privileges for maintenance purposes. Do not delete, change the password, or remove privileges from this user, as it will disrupt Zerops' ability to maintain the database cluster.

Info

For more information about default PostgreSQL setup, users, and databases, see Manage PostgreSQL Users and Databases.

Connect from Services in the Same Project

All services within a Zerops project share a dedicated private network. There are two ways to implement connections between services in the same project:

Method 1: Direct Connection Parameters

You can directly use the connection parameters from Access Details:

host = database1
port = 5432
user = database1
password = ********** (find under Access Details)

Method 2: Environment Variables (Recommended)

For better maintainability, Zerops creates environment variables for each PostgreSQL service that you can use in your application configuration. List of service environment variables is available in Zerops GUI. Go to a PostgreSQL service detail and choose Environment variables.

To use variables from one service in another, prefix the variable name with the service hostname and underscore - to access the connectionString variable of postgresql1, use postgresql1_connectionString.

For more details on how to use environment variables, and instructions for adding your own custom variables, see the Environment Variables documentation.

Important notes

• When changing passwords, update both the database user password and the environment variable separately - they don't automatically synchronize.
• While both postgresql:// and postgres:// URI formats are valid, Zerops uses the postgresql:// format. If your software requires postgres://, create a custom environment variable with this format.
• Do not use SSL/TLS protocols for internal connections. Security is assured by the project's private network.

Connect Remotely

Zerops offers two methods for connecting to your PostgreSQL database from outside the Zerops environment:

Method 1: Connect via Zerops VPN

You can securely connect to PostgreSQL from your local workstation via Zerops VPN:

1. Install & set up zCLI
2. Start the Zerops VPN
3. Use the connection details from Access Details in the PostgreSQL service detail in Zerops GUI
4. When finished, stop the Zerops VPN

Important notes

• Do not use SSL/TLS protocols when connecting over VPN. Security is provided by the VPN tunnel.
• If your connection over VPN doesn't work, try adding .zerops suffix to the service hostname (e.g., database1.zerops). For additional help, check the VPN troubleshooting page.

Method 2: Connect via Direct IP Access

Direct IP Access uses pgBouncer for connection pooling and TLS termination.

Internally, port 5432 is available without SSL. Externally, connections are secured with TLS through pgBouncer (port 6432) before being routed to your PostgreSQL service.

Enable external access

1. Navigate to your PostgreSQL service in the Zerops GUI and choose the Public Access through IP Addresses section
2. Choose either IPv6 (available by default) or IPv4 (requires the unique IPv4 add-on)
3. Open one or more ports and point them to your PostgreSQL service (the system will direct them through pgBouncer)
   • Choose any port from 10-65435 (except 80 and 443)
   • Select destination service and internal port
   • Each public port can be mapped to any internal service port
   • Multiple public ports can point to the same internal port if needed
   • Port configurations can be set independently for IPv4 and IPv6
4. Optionally enable firewall protection for additional security
5. Click the Publish X IP access change(s) button to apply your settings

For database management tools and how to manage users and databases, see Manage PostgreSQL Users and Databases.