Connect to a Postgres Cluster

It’s one thing to create a Postgres cluster; it’s another thing to connect to it. Let’s explore how PGO makes it possible to connect to a Postgres cluster!

Background: Services, Secrets, and TLS

PGO creates a series of Kubernetes Services to provide stable endpoints for connecting to your Postgres databases. These endpoints make it easy to provide a consistent way for your application to maintain connectivity to your data. To inspect what services are available, you can run the following command:

kubectl -n postgres-operator get svc --selector=postgres-operator.crunchydata.com/cluster=hippo

will yield something similar to:

NAME              TYPE        CLUSTER-IP     EXTERNAL-IP   PORT(S)    AGE
hippo-ha          ClusterIP   10.103.73.92   <none>        5432/TCP   3h14m
hippo-ha-config   ClusterIP   None           <none>        <none>     3h14m
hippo-pods        ClusterIP   None           <none>        <none>     3h14m
hippo-primary     ClusterIP   None           <none>        5432/TCP   3h14m

You do not need to worry about most of these Services, as they are used to help manage the overall health of your Postgres cluster. For the purposes of connecting to your database, the Service of interest is called hippo-primary. Thanks to PGO, you do not need to even worry about that, as that information is captured within a Secret!

When your Postgres cluster is initialized, PGO will bootstrap a database and Postgres user that your application can access. This information is stored in a Secret suffixed with pguser, following the pattern <clusterName>-pguser. For our hippo cluster, this Secret is called hippo-pguser. This Secret contains the information you need to connect your application to your Postgres database:

• user: The name of the user account.
• password: The password for the user account.
• dbname: The name of the database that the user has access to by default.
• host: The name of the host of the database. This references the Service of the primary Postgres instance.
• port: The port that the database is listening on.
• uri: A PostgreSQL connection URI that provides all the information for logging into the Postgres database.

All connections are over TLS. PGO provides its own certificate authority (CA) to allow you to securely connect your applications to your Postgres clusters. This allows you to use the verify-full “SSL mode” of Postgres, which provides eavesdropping protection and prevents MITM attacks. You can also choose to bring your own CA, which is described later in this tutorial in the Customize Cluster section.

Connect an Application

For this tutorial, we are going to connect Keycloak, an open source identity management application. Keycloak can be deployed on Kubernetes and is backed by a Postgres database. While we provide an example of deploying Keycloak in the Postgres Operator examples repository, we will use the sample manifest below to deploy the application:

cat <<EOF >> keycloak.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: keycloak
  namespace: postgres-operator
  labels:
    app.kubernetes.io/name: keycloak
spec:
  selector:
    matchLabels:
      app: keycloak
  template:
    metadata:
      labels:
        app.kubernetes.io/name: keycloak
    spec:
      containers:
      - image: quay.io/keycloak/keycloak:latest
        name: keycloak
        env:
        - name: DB_ADDR
          valueFrom: { secretKeyRef: { name: hippo-pguser, key: host } }
        - name: DB_PORT
          valueFrom: { secretKeyRef: { name: hippo-pguser, key: port } }
        - name: DB_DATABASE
          valueFrom: { secretKeyRef: { name: hippo-pguser, key: dbname } }
        - name: DB_USER
          valueFrom: { secretKeyRef: { name: hippo-pguser, key: user } }
        - name: DB_PASSWORD
          valueFrom: { secretKeyRef: { name: hippo-pguser, key: password } }
        - name: KEYCLOAK_USER
          value: "admin"
        - name: KEYCLOAK_PASSWORD
          value: "admin"
        - name: PROXY_ADDRESS_FORWARDING
          value: "true"
        ports:
        - name: http
          containerPort: 8080
        - name: https
          containerPort: 8443
        readinessProbe:
          httpGet:
            path: /auth/realms/master
            port: 8080
      restartPolicy: Always

EOF

kubectl apply -f keycloak.yaml

Notice this part of the manifest:

- name: DB_ADDR
  valueFrom:
    secretKeyRef:
      name: hippo-pguser
      key: host
- name: DB_PORT
  valueFrom:
    secretKeyRef:
      name: hippo-pguser
      key: port
- name: DB_DATABASE
  valueFrom:
    secretKeyRef:
      name: hippo-pguser
      key: dbname
- name: DB_USER
  valueFrom:
    secretKeyRef:
      name: hippo-pguser
      key: user
- name: DB_PASSWORD
  valueFrom:
    secretKeyRef:
      name: hippo-pguser
      key: password

The above manfiest shows how all of these values are derived from the hippo-pguser Secret. This means that we do not need to know any of the connection credentials or have to insecurely pass them around – they are made directly available to the application!

Using this method, you can tie application directly into your GitOps pipeline that connect to Postgres without any prior knowledge of how PGO will deploy Postgres: all of the information your application needs is propagated into the Secret!

Next Steps

Now that we have seen how to connect an application to a cluster, let’s learn how to create a high availability Postgres cluster!