Is it possible to disable pg_hba.conf local trust?

[apeschel]

Please, answer some short questions which should help us to understand your problem / question better?

• Which image of the operator are you using? registry.opensource.zalan.do/acid/postgres-operator:v1.6.3
• Type of issue? question

I've been digging through this repo and the patroni repo to find info on this, but I haven't had much luck.

The pg_hba.conf used by the postgres-operator PostgreSQL deployment has this line:

local   all             all                                   trust

This gives full password-less access to all Postgres users to any user running on the local command-line.

This in turn means that anyone with kubectl exec access can access any database on the cluster:

kubectl exec -it test-db -- psql -U postgres

psql (13.3 (Ubuntu 13.3-1.pgdg18.04+1))
Type "help" for help.

postgres=#

This behavior is less than ideal from a security standpoint.

Is this permissive trust required for some functionality in the postgres-operator? Is it possible to disable this behavior by modifying the pg_hba.conf ?

[FxKu]

You can overwrite pg_hba in the manifest under patroni section. Not sure, if this is only possible on cluster initialization and not after.

[apeschel]

@FxKu Would overwriting it break anything though? Does the postgres-operator somehow rely on this permissive access?

[hemakshis]

Hi @FxKu ,

I tried updating the pg_hba.conf file by adding the following in the postgres-operator manifest -

spec:
  dockerImage: ghcr.io/zalando/spilo-15:3.0-p1
  teamId: "acid"
  numberOfInstances: 1
    users:  # Application/Robot users
      test-user: []
      zalando:
        - superuser
        - createdb
  databases:
    test_db: test-user
  patroni:
    pg_hba:
    - local   all             all                                   md5
    - 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
    - local   replication     standby                    trust
    - hostssl replication     standby all                md5
    - hostnossl all           all                all                reject
    - hostssl all             +zalandos    all                pam
    - hostssl all             all                all                md5

I basically copied the default pg_hba.conf file and then changed trust -> md5 for local but getting following errors in the database pod -

2024-05-28 16:48:45,710 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
Password for user postgres: 
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
2024-05-28 16:48:55,627 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:49:19.180 UTC [24] LOG Starting pgqd 3.5
2024-05-28 16:49:19.180 UTC [24] LOG auto-detecting dbs ...
2024-05-28 16:49:19.183 UTC [24] ERROR connection error: PQconnectPoll
2024-05-28 16:49:19.183 UTC [24] ERROR libpq: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
2024-05-28 16:49:25,628 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:49:49.209 UTC [24] LOG {ticks: 0, maint: 0, retry: 0}
2024-05-28 16:49:55,625 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:50:19.209 UTC [24] LOG {ticks: 0, maint: 0, retry: 0}
2024-05-28 16:50:19.212 UTC [24] ERROR connection error: PQconnectPoll
2024-05-28 16:50:19.212 UTC [24] ERROR libpq: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
2024-05-28 16:50:25,635 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:50:49.240 UTC [24] LOG {ticks: 0, maint: 0, retry: 0}
2024-05-28 16:50:55,624 INFO: no action. I am (hsachdev-postgresql-db-new-0), the leader with the lock
2024-05-28 16:51:19.239 UTC [24] ERROR connection error: PQconnectPoll
2024-05-28 16:51:19.239 UTC [24] ERROR libpq: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: fe_sendauth: no password supplied
2024-05-28 16:51:19.243 UTC [24] LOG {ticks: 0, maint: 0, retry: 0}

How to fix this?

Also, if I switch back to trust then I don't see these errors.

[JuniorTrailblazer]

Hi @hemakshis,

are you still experiencing the errors? Because I´m trying out the operator and see the same errors as well. Changing it to trust might solve it but from the security pov it is not a good setup to leave that be.

Not sure what to do since I have tried changing the pg_hba entries a few times now.

[sda399]

hi, looking for same setup. Any configuration doc to tell pgqd to use a password?