No `no pg_hba.conf` entry is created when creating a `REPLICATION` user

[honne23]

Please ensure you do the following when reporting a bug:

Overview

When creating a user using the v5 chart with REPLICATION permissions, no pg_hba.conf entry is created for the user, thereby making it difficult to programmatically access a replication slot in the database by connecting over a postgres://... URI rather than using a cert.

Environment

Please provide the following details:

• Platform: GKE
• Platform Version: v1.22.2
• PGO Image Tag: centos8-2.33-2
• Postgres Version: 13
• Storage: Standard GKE storage class

Steps to Reproduce

Create a user using the v5 yaml with the REPLICATION option enabled.

EXPECTED

1. A pg_hba.conf entry is created / appened upon database startup to give a replicated user the ability to create a slot.

ACTUAL

1. A conf entry is not generated, instead the only way to access the replication slot is through pg_recvlogical rather than programmatically, such as using Golang.

Logs

time="2022-01-27T15:18:31Z" level=error msg="unable to init session" error="FATAL: no pg_hba.conf entry for replication connection from host \"10.20.3.198\", user \"replicator\", SSL off (SQLSTATE 28000)"

[honne23]

Note this was resolved with the following config, however I think that adding a replication user should edit pg_hba.conf as part of expected behaviour:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: my-company-db
spec:
  image: eu.gcr.io/my-company-xxxxxx/timescale-custom:v38
  postgresVersion: 13
  users:
    - name: postgres
      databases:
        - company
      options: "SUPERUSER REPLICATION"
    - name: replicator
      databases:
        - company
      options: "REPLICATION"
  instances:
    - name: instance1
      replicas: 3
      dataVolumeClaimSpec:
        accessModes:
          - "ReadWriteOnce"
        resources:
          requests:
            storage: 100Gi
  databaseInitSQL:
    key: init.sql
    name: my-company-init-sql
  patroni:
    dynamicConfiguration:
      postgresql:
        pg_hba:
          - "host all all 0.0.0.0/0 trust" # this line enabled logical replication with programmatic access
          - "host all postgres 127.0.0.1/32 md5"
  backups:
    pgbackrest:
      image: registry.developers.crunchydata.com/crunchydata/crunchy-pgbackrest:centos8-2.33-2
      repoHost:
        dedicated: {}
      repos:
        - name: repo1
          volume:
            volumeClaimSpec:
              accessModes:
                - "ReadWriteOnce"
              resources:
                requests:
                  storage: 300Gi

Additionally some guidance on making this more secure would be more than welcome / ideal.

[lneelu]

thank you very much, it solved my day long problem . I wanted to add host all all 0.0.0.0/0 md5 in pg_hba.conf file

[tjmoore4]

Hello @honne23,

Thank you for the information regarding your issue. While trust authentication solves the immediate problem, it is, as you noted, not recommended from a security standpoint. Per the documenation:

"When trust authentication is specified, PostgreSQL assumes that anyone who can connect to the server is authorized to access the database with whatever database user name they specify (even superuser names). Of course, restrictions made in the database and user columns still apply. This method should only be used when there is adequate operating-system-level protection on connections to the server."

The best option will depend on the specifics of your use case. For more detailed information on other methods for user authentication, please see https://access.crunchydata.com/documentation/postgresql13/latest/auth-methods.html

Additionally, I've created a story in our backlog to better address this issue in the future.