postgres-operator
postgres-operator copied to clipboard
zalando
How to pass postgres credentials to DB pod after disabling local trust in pg_hba.conf
Please, answer some short questions which should help us to understand your problem / question better?
-
Which image of the operator are you using?
ghcr.io/zalando/postgres-operator:v1.11.0 - Where do you run it - cloud or metal? Kubernetes or OpenShift? AWS
- Are you running Postgres Operator in production? [yes | no] No, but soon going to GA
- Type of issue? [Bug report, question, feature request, etc.] Bug
In order to go GA, we need to enable some security hardening on our postgres pods so I tried updating the pg_hba.conf file by adding the following in the postgres-operator manifest (basically don't allow anyone to enter postgres by either kubectl exec or port-forwarding the DB pod, at least ask for a password) -
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}
If I switch back to trust then I don't see these errors. Please suggest how to fix this? Another thing, please add me to you slack community as we use the operator a lot and would like to keep contributing as that will help us too.
When you exec into the pod there's a postgres.yml file. Can you check if credentials are set under postgresql.authentication?
Hi @FxKu, this is how the file looks like -
root@control-plane-database-0:/home/postgres# cat postgres.yml
...
...
...
postgresql:
authentication:
replication:
password: jtSN0HGJTicu14fv0VKjUd0L5hxjvvthwJkCumpixoBNqX5xvQULNNvSkaksP76j
username: standby
superuser:
password: PPusPhzipDjidlNjY2wOcp6GlJ1opre6xtbnDujFWDXwfhGauINz0wRYxnzkQAji
username: postgres
basebackup_fast_xlog:
command: /scripts/basebackup.sh
retries: 2
bin_dir: /usr/lib/postgresql/15/bin
callbacks:
on_role_change: /scripts/on_role_change.sh zalandos true
connect_address: <som_IP_addr>:5432
create_replica_method:
- basebackup_fast_xlog
data_dir: /home/postgres/pgdata/pgroot/data
listen: '*:5432'
name: control-plane-database-0
parameters:
archive_command: /bin/true
bg_mon.history_buckets: 120
bg_mon.listen_address: '::'
extwlist.custom_path: /scripts
extwlist.extensions: btree_gin,btree_gist,citext,extra_window_functions,first_last_agg,hll,hstore,hypopg,intarray,ltree,pgcrypto,pgq,pgq_node,pg_trgm,postgres_fdw,tablefunc,uuid-ossp,pg_partman
log_destination: csvlog
log_directory: ../pg_log
log_file_mode: '0644'
log_filename: postgresql-%u.log
log_rotation_age: 1d
log_truncate_on_rotation: 'on'
logging_collector: 'on'
pg_stat_statements.track_utility: 'off'
shared_buffers: 32MB
shared_preload_libraries: bg_mon,pg_stat_statements,pgextwlist,pg_auth_mon,set_user,pg_cron,pg_stat_kcache
ssl: 'on'
ssl_cert_file: /run/certs/server.crt
ssl_key_file: /run/certs/server.key
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
pgpass: /run/postgresql/pgpass
use_unix_socket: true
use_unix_socket_repl: true
restapi:
connect_address: <some_IP_addr>:8008
listen: :8008
scope: control-plane-database
Any updates? @FxKu @hemakshis I´m currently experiencing the same issue and I´m not sure how to handle it the right way
