user.md

User Guide

Learn how to work with the Postgres Operator in a Kubernetes (K8s) environment.

Create a manifest for a new PostgreSQL cluster

Make sure you have set up the operator. Then you can create a new Postgres cluster by applying manifest like this minimal example:

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: acid-minimal-cluster
spec:
  teamId: "acid"
  volume:
    size: 1Gi
  numberOfInstances: 2
  users:
    # database owner
    zalando:
    - superuser
    - createdb

    # role for application foo
    foo_user: # or 'foo_user: []'

  #databases: name->owner
  databases:
    foo: zalando
  postgresql:
    version: "17"

Once you cloned the Postgres Operator repository you can find this example also in the manifests folder:

kubectl create -f manifests/minimal-postgres-manifest.yaml

Make sure, the spec section of the manifest contains at least a teamId, the numberOfInstances and the postgresql object with the version specified. The minimum volume size to run the postgresql resource on Elastic Block Storage (EBS) is 1Gi.

Note, that when enable_team_id_clustername_prefix is set to true the name of the cluster must start with the teamId and -. At Zalando we use team IDs (nicknames) to lower chances of duplicate cluster names and colliding entities. The team ID would also be used to query an API to get all members of a team and create database roles for them. Besides, the maximum cluster name length is 53 characters.

Watch pods being created

Check if the database pods are coming up. Use the label application=spilo to filter and list the label spilo-role to see when the master is promoted and replicas get their labels.

kubectl get pods -l application=spilo -L spilo-role -w

The operator also emits K8s events to the Postgresql CRD which can be inspected in the operator logs or with:

kubectl describe postgresql acid-minimal-cluster

Connect to PostgreSQL

With a port-forward on one of the database pods (e.g. the master) you can connect to the PostgreSQL database from your machine. Use labels to filter for the master pod of our test cluster.

# get name of master pod of acid-minimal-cluster
export PGMASTER=$(kubectl get pods -o jsonpath={.items..metadata.name} -l application=spilo,cluster-name=acid-minimal-cluster,spilo-role=master -n default)

# set up port forward
kubectl port-forward $PGMASTER 6432:5432 -n default

Open another CLI and connect to the database using e.g. the psql client. When connecting with a manifest role like foo_user user, read its password from the K8s secret which was generated when creating acid-minimal-cluster. As non-encrypted connections are rejected by default set SSL mode to require:

export PGPASSWORD=$(kubectl get secret postgres.acid-minimal-cluster.credentials.postgresql.acid.zalan.do -o 'jsonpath={.data.password}' | base64 -d)
export PGSSLMODE=require
psql -U postgres -h localhost -p 6432

Password encryption

Passwords are encrypted with md5 hash generation by default. However, it is possible to use the more recent scram-sha-256 method by changing the password_encryption parameter in the Postgres config. You can define it directly from the cluster manifest:

apiVersion: "acid.zalan.do/v1"
kind: postgresql
metadata:
  name: acid-minimal-cluster
spec:
  [...]
  postgresql:
    version: "17"
    parameters:
      password_encryption: scram-sha-256

Defining database roles in the operator

Postgres Operator allows defining roles to be created in the resulting database cluster. It covers three use-cases:

• manifest roles: create application roles specific to the cluster described in the manifest.
• infrastructure roles: create application roles that should be automatically created on every cluster managed by the operator.
• teams API roles: automatically create users for every member of the team owning the database cluster.

In the next sections, we will cover those use cases in more details. Note, that the Postgres Operator can also create databases with pre-defined owner, reader and writer roles which saves you the manual setup. Read more in the next chapter.

Manifest roles

Manifest roles are defined directly in the cluster manifest. See minimal postgres manifest for an example of zalando role, defined with superuser and createdb flags.

Manifest roles are defined as a dictionary, with a role name as a key and a list of role options as a value. For a role without any options it is best to supply the empty list []. It is also possible to leave this field empty as in our example manifests. In certain cases such empty field may be missing later removed by K8s due to the null value it gets (foobar_user: is equivalent to foobar_user: null).

The operator accepts the following options: superuser, inherit, login, nologin, createrole, createdb, replication, bypassrls.

By default, manifest roles are login roles (aka users), unless nologin is specified explicitly.

The operator automatically generates a password for each manifest role and places it in the secret named {username}.{clustername}.credentials.postgresql.acid.zalan.do in the same namespace as the cluster. This way, the application running in the K8s cluster and connecting to Postgres can obtain the password right from the secret, without ever sharing it outside of the cluster.

At the moment it is not possible to define membership of the manifest role in other roles.

To define the secrets for the users in a different namespace than that of the cluster, one can set enable_cross_namespace_secret and declare the namespace for the secrets in the manifest in the following manner (note, that it has to be reflected in the database section, too),

spec:
  users:
    # users with secret in different namespace
    appspace.db_user:
    - createdb
  databases:
    # namespace notation is part of user name
    app_db: appspace.db_user

Here, anything before the first dot is considered the namespace and the text after the first dot is the username. Also, the postgres roles of these usernames would be in the form of namespace.username.

For such usernames, the secret is created in the given namespace and its name is of the following form, {namespace}.{username}.{clustername}.credentials.postgresql.acid.zalan.do

Infrastructure roles

An infrastructure role is a role that should be present on every PostgreSQL cluster managed by the operator. An example of such a role is a monitoring user. There are two ways to define them:

• With the infrastructure roles secret only
• With both the the secret and the infrastructure role ConfigMap.