pg_hba is not working

[angapov]

Whatever I put in cluster spec:

  patroni:
    initdb:
      encoding: "UTF8"
      locale: "en_US.UTF-8"
      data-checksums: "true"
    pg_hba:
    - host    all all 0.0.0.0/0 md5

Inside container pg_hba.conf always looks the same rejecting non-local connections:

# cat /home/postgres/pgdata/pgroot/data/pg_hba.conf
# Do not edit this file manually!
# It will be overwritten by Patroni!
local   all             all                                   trust
hostssl all             +zalandos    127.0.0.1/32       pam
host    all             all                127.0.0.1/32       md5
hostssl all             +zalandos    ::1/128            pam
host    all             all                ::1/128            md5
hostssl replication     standby all                md5
hostnossl all           all                all                reject
hostssl all             +zalandos    all                pam
hostssl all             all                all                md5

Am I missing something?

[alexeyklyukin]

@angapov can you also post the YAML statefulset generated by the operator (kubectl get statefulset $cluster_name -o yaml)?

[angapov]

Here it is.

apiVersion: apps/v1
kind: StatefulSet
metadata:
  annotations:
    zalando-postgres-operator-rolling-update-required: "false"
  creationTimestamp: 2018-06-26T11:37:51Z
  generation: 1
  labels:
    application: spilo
    team: bell
    version: bell-postgres
  name: bell-postgres
  namespace: bell
  resourceVersion: "7279439"
  selfLink: /apis/apps/v1/namespaces/bell/statefulsets/bell-postgres
  uid: 5c0b782f-7935-11e8-a982-12cc68e32afc
spec:
  podManagementPolicy: OrderedReady
  replicas: 3
  revisionHistoryLimit: 10
  selector:
    matchLabels:
      application: spilo
      version: bell-postgres
  serviceName: bell-postgres
  template:
    metadata:
      creationTimestamp: null
      labels:
        application: spilo
        team: bell
        version: bell-postgres
      namespace: bell
    spec:
      containers:
      - env:
        - name: SCOPE
          value: bell-postgres
        - name: PGROOT
          value: /home/postgres/pgdata/pgroot
        - name: POD_IP
          valueFrom:
            fieldRef:
              apiVersion: v1
              fieldPath: status.podIP
        - name: POD_NAMESPACE
          valueFrom:
            fieldRef:
              apiVersion: v1
              fieldPath: metadata.namespace
        - name: PGUSER_SUPERUSER
          value: postgres
        - name: PGPASSWORD_SUPERUSER
          valueFrom:
            secretKeyRef:
              key: password
              name: postgres.bell-postgres.credentials
        - name: PGUSER_STANDBY
          value: standby
        - name: PGPASSWORD_STANDBY
          valueFrom:
            secretKeyRef:
              key: password
              name: standby.bell-postgres.credentials
        - name: PAM_OAUTH2
          value: https://info.example.com/oauth2/tokeninfo?access_token= uid realm=/employees
        - name: SPILO_CONFIGURATION
          value: '{"postgresql":{"bin_dir":"/usr/lib/postgresql/10/bin","parameters":{"log_statement":"all","shared_buffers":"1024MB"}},"bootstrap":{"initdb":[{"auth-host":"md5"},{"auth-local":"trust"},"data-checksums",{"encoding":"UTF8"},{"locale":"en_US.UTF-8"}],"users":{"zalandos":{"password":"","options":["CREATEDB","NOLOGIN"]}},"pg_hba":["host    all
            all 0.0.0.0/0 md5"],"dcs":{"postgresql":{"parameters":{"max_connections":"300"}}}}}'
        - name: DCS_ENABLE_KUBERNETES_API
          value: "true"
        image: registry.opensource.zalan.do/acid/spilo-cdp-10:1.4-p8
        imagePullPolicy: IfNotPresent
        name: postgres
        ports:
        - containerPort: 8008
          protocol: TCP
        - containerPort: 5432
          protocol: TCP
        - containerPort: 8080
          protocol: TCP
        resources:
          limits:
            cpu: "1"
            memory: 4Gi
          requests:
            cpu: 500m
            memory: 2Gi
        securityContext:
          privileged: true
        terminationMessagePath: /dev/termination-log
        terminationMessagePolicy: File
        volumeMounts:
        - mountPath: /home/postgres/pgdata
          name: pgdata
      dnsPolicy: ClusterFirst
      restartPolicy: Always
      schedulerName: default-scheduler
      securityContext: {}
      serviceAccount: zalando-postgres-operator
      serviceAccountName: zalando-postgres-operator
      terminationGracePeriodSeconds: 300
  updateStrategy:
    type: OnDelete
  volumeClaimTemplates:
  - metadata:
      annotations:
        volume.alpha.kubernetes.io/storage-class: default
      creationTimestamp: null
      name: pgdata
    spec:
      accessModes:
      - ReadWriteOnce
      resources:
        requests:
          storage: 20Gi
    status:
      phase: Pending
status:
  collisionCount: 0
  currentReplicas: 3
  currentRevision: bell-postgres-6569665dbc
  observedGeneration: 1
  readyReplicas: 3
  replicas: 3
  updateRevision: bell-postgres-6569665dbc

[alexeyklyukin]

Ok, so it passes those lines correctly; however , Spilo (the docker image) apparently ignores those parameters in favor of hard-coded lines: https://github.com/zalando/spilo/blob/3cf05f0c099e225d4d8cff580a51cb3eaab2e556/postgres-appliance/scripts/configure_spilo.py#L211

So this is a bug report to Spilo.

[angapov]

@alexeyklyukin this makes postgres-operator effectively useless as it always rejects non-local connection. Do you know what docker image version was working before?

[Jan-M]

If I am not mistaken we use SSL in all environments, which is allowed in

https://github.com/zalando/spilo/blob/3cf05f0c099e225d4d8cff580a51cb3eaab2e556/postgres-appliance/scripts/configure_spilo.py#L226

[alexeyklyukin]

@angapov as @Jan-M says, it doesn't reject all connections originating from outside of the localhost, but only non-SSL ones. Postgres operator has nothing to do with this, as it sets the environment variable for the Spilo image correctly, however, Spilo subsequently ignores those.

Do you know what docker image version was working before?

If you imply that we used to honor the custom pg_hba before, but not now, this is not the case, apparently pg_hba has never been taken in account in Spilo :-(

[angapov]

As a dirty workaround I modified file /configure_spilo.py to my needs and rebuilt custom image.

[fiftoine]

Without rebuilding the spilo image, how would you use SSL to connect? Is there a way to extract root.crt to generate client certificates?

[Jan-M]

We do not use client certs to auth connections to PostgreSQL in our setup.

[alexeyklyukin]

The fix for that merged at #361. The operator will populate the pg_hba.conf parameters in the Patroni local section instead of the bootstrap one, overriding Spilo defaults and allowing to change those parameters subsequently by altering the manifest.