Connect to PostgreSQL in Zerops

Default PostgreSQL database and user

Zerops creates a default database and a default user automatically when a new PostgreSQL service is created.

Database

The default database name is identical to the service hostname. The default encoding is set to utf8mb4.

DB user

Default user name is identical to the service hostname. Default user password is generated randomly. You will find the password in Zerops GUI or you can use the environment variable.

Info

Zerops creates a second DB user: zps for maintenance reasons with full privileges. Do not delete, change the password or remove privileges from this user, it will disrupt Zerops ability to maintain the database cluster.

Copy access details from Zerops GUI

You will find the PostgreSQL access details under the Access details button in the project dashboard page.

// TODO screenshot (Access detail popover)

The same information is available in the service detail page in the left menu under the Peek access details button.

PostgreSQL access parameters:

Parameter	Description
Hostname	The service hostname specified when the PostgreSQL service was created.
Port	5432 This port is fixed for all PostgreSQL services and cannot be customised.
User	Zerops creates a database user automatically when the service is created. The user name is always identical to the service hostname.
Password	Zerops sets a random password when the service is created.
Connection string	The connection string for PostgreSQL service is: postgresql://${user}:${password}@{hostname}:5432

Connect to PostgreSQL from runtime services of the same project

Projects in Zerops represent a group of one or more services. Services can be of different types (runtime services, databases, message brokers, object storage, etc.). All services of the same project share a dedicated private network. To connect to a service within the same project, just use the service hostname and its internal port.

// TODO image (project example diagram)

Example

To connect to PostgreSQL database1 service, set

host = database1
user = database1
password = **********

You will find the password under the Access details button in Zerops GUI.

Caution

Do not use SSL/TLS protocols when connecting to PostgreSQL from other runtime services in the same project. Zerops PostgreSQL is not configured to support these protocols. The security is assured by the project private network. Due to security reasons Zerops doesn't allow exposing PostgreSQL service to the internet.

Use PostgreSQL environment variables

Zerops creates default environment variables for each PostgreSQL service to help you with connection from runtime services in the same project. To avoid the need to copy database access parameters manually, use environment variables in your [runtime service].

Prefix the environment variable key

All services of the same project can reference environment variables from other services. To use an environment variable from one service in another service in the same project, you must prefix the environment variable key with the service hostname and underscore.

Example

To access the connectionString env variable of the postgresql1 service, use postgresql1_connectionString as the env variable key. To access the password env variable of the postgresql2 service, use postgresql2_password as the env variable key.

PostgreSQL environment variables

List of service environment variables is available in Zerops GUI. Go to a PostgreSQL service detail and choose Environment variables in the left menu.

// TODO screenshot (Service env variables table page in the PostgreSQL detail)

Zerops creates following environment variables when the PostgreSQL service is created:

Variable	Description
Hostname	The service hostname specified when the PostgreSQL service was created.
Port	5432 This port is fixed for all PostgreSQL services and cannot be customised.
projectId	ID of the project. Generated by Zerops.
serviceId	ID of the PostgreSQL service. Generated by Zerops.
Connection string	The connection string for PostgreSQL service is: postgresql://${user}:${password}@{hostname}:5432 Connection string contains references to user and password variables. Each time the user or password variable is updated, the connectionString variable is automatically updated as well.
User	Zerops creates a database user automatically when the service is created. The user name is always identical to the service hostname.
Password	Zerops sets a random password when the service is created.

Caution

When you change the value of the password env variable, only the env variable is updated, not the actual password of the PostgreSQL user. You have to update the password of the database user manually.

When you change the password of the default PostgreSQL user in the database, the new password is not synchronised to the password env variable. You have to update the password env variable manually.

Caution

The official PostgreSQL documentation states that both postgresql:// and postgres:// URIs are valid. In Zerops, we chose to generate the connection string with the widely used postgresql:// schema. Some softwares however require the connection string to start with the shorter postgres:// version only, which might cause errors. To fix that, create your own environment variable with the correct URI, e.g. when your PostgreSQL service is called db - postgres://${db_user}:${db_password}@${db_hostname}:${db_port}.

You can create own custom environment variables for the PostgreSQL service in Zerops GUI and use them in the same way as the default variables.

Connect to PostgreSQL in Zerops remotely

Caution

Due to security reasons Zerops doesn't allow exposing PostgreSQL service directly to the internet.

Start VPN connection

You can securely connect to PostgreSQL from your local workspace via Zerops VPN. Zerops VPN client is included into zCLI, the Zerops command-line tool. To start a VPN connection to the selected Zerops project, follow these steps:

1. Install & setup zCLI
2. Start the Zerops VPN

Access PostgreSQL through VPN

Once the VPN session is established, you have the secured connection to the project's private network in Zerops. You can access all project services locally by using their hostname. The only difference is that no environment variables are available when connected through VPN. To connect to PostgreSQL in Zerops you have to copy the access details manually from Zerops GUI.

Caution

Do not use SSL/TLS protocols when connecting to PostgreSQL over VPN. Zerops PostgreSQL is not configured to support these protocols. The security is assured by the VPN.

Stop VPN connection

Stop the Zerops VPN in zCLI.

Connect to PostgreSQL from another Zerops project

All services of the same project share a dedicated private network. You can use the service hostname to connect from one service to another within the same project.

Different Zerops projects have no special connection. They can communicate with each other only via the internet. If you need to connect to a PostgreSQL service in a Zerops project from a runtime service in another project, you need to use the Zerops VPN. Due to security reasons Zerops doesn't allow exposing PostgreSQL service directly to the internet.