Open apeschel opened 3 years ago
You can overwrite pg_hba in the manifest under patroni
section. Not sure, if this is only possible on cluster initialization and not after.
@FxKu Would overwriting it break anything though? Does the postgres-operator somehow rely on this permissive access?
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.
Please, answer some short questions which should help us to understand your problem / question better?
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: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: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
?