PostgreSQLUsers

Connect to a cluster

Get a cluster's connection string, choose the URI or the key=value form, and hand it to a workload through a vault — directly, through the pooler or to the replicas.

Requires: postgrescluster/readSecrets

A cluster's connection string carries its password, so reading it needs the postgrescluster/readSecrets permission; the Owner, Contributor and Databases Operator roles have it. Every time the string is read, the cluster's Activity records who read it.

Get the connection string

On the cluster's Overview, in Access Details, choose Show connection string next to Connection String, then copy it. Hide connection string hides it again.

When the cluster is exposed outside the Kubernetes cluster, External Connection String holds the same credentials with the load balancer's address. It reads External address is being provisioned… until the load balancer has one — see Network access.

With the CLI:

bash

stsh pg connstr orders-db                  # both forms
stsh pg connstr orders-db --raw            # the URI only, for piping
stsh pg connstr orders-db --external       # through the load balancer

Through the API, GET .../resources/postgresclusters/<name>/connectionstring and .../connectionstring/external.

The two forms

The platform gives the connection string in two forms. The portal shows the first; the API and the CLI return both.

Form	Field	Looks like	For
URI	`connectionString`	`postgresql://orders:<password>@orders-db-rw.<namespace>:5432/orders`	psql and other libpq-based clients, psycopg, pgx, node-postgres
key=value	`keyValueConnectionString`	`Host=orders-db-rw.<namespace>;Port=5432;Database=orders;Username=orders;Password=<password>`	.NET and Npgsql

Npgsql cannot read the URI: given one it fails with Format of the initialization string does not conform to specification. Give a .NET application the key=value form. In the URI the password is percent-encoded; in the key=value form it is not.

The external strings are externalConnectionString and externalKeyValueConnectionString; both are empty while the load balancer has no address.

What the string points at

User — the owner of the database, named like the database.
Host — <cluster>-rw.<namespace>, the read-write service in the cluster's resource group. It always leads to the current primary, also after a failover.
Port — 5432.
Database — the database the cluster was created with.

The host resolves for workloads on the same Kubernetes cluster. Whether a workload may actually connect is up to the network rules — see Network access.

TLS

The cluster refuses every connection that does not use TLS, and by default accepts TLS 1.3 only. Most drivers try TLS first; if yours does not, turn it on, for example with sslmode=require in a URI or SSL Mode=Require in a key=value string. The server's certificate is issued by the cluster's own certificate authority, which the platform does not hand out, so a client set to verify the certificate against a known authority cannot connect.

The requirement is PostgreSQL's own. The PgBouncer pooler is a separate server, and the platform does not set it to refuse clients without TLS — see Through the pooler.

Give a workload the connection string

Keep the string in a vault rather than in a workload's settings:

Add it as a secret to a vault — see Add a secret. Pick the form the workload's driver reads.
Reference the secret from the workload and give the workload's identity Secrets Reader on the vault:
- an app reads it from the file /secrets/env.json — see Secrets in apps and functions;
- a function reads it as the environment variable STSH_<NAME> — see Read the values in a function;
- a container instance gets it as an environment variable — see Secrets in container instances.

The string stays the same for the cluster's life, also across a restore from a snapshot. A blueprint that declares a database writes its connection details into the blueprint's own vault — see Managed resources.

Through the pooler

With PgBouncer on, the cluster has a pooler service, <cluster>-pooler-rw, in the same resource group. The connection string does not use it: to go through the pooler, replace the host <cluster>-rw with <cluster>-pooler-rw and keep the rest — the same user, password, port and database.

Important

The platform does not configure the pooler to require TLS from its clients. Turn TLS on in every client that goes through the pooler, for example with sslmode=require, so that the password does not cross the network in the clear.

In Transaction and Statement mode a server connection is shared between clients from one transaction or statement to the next, so session state — SET, prepared statements, advisory locks, temporary tables — does not carry over. Use Session mode for applications that rely on it.

Read from the replicas

Two more services lead to the cluster, with the same credentials:

<cluster>-r — any instance, the primary included. The Overview shows it as Read Service.
<cluster>-ro — the replicas only.

Replicas apply the primary's changes a moment after it makes them, so a read from a replica can miss a write made just before. See Instances and high availability.