When we running standby cluster we must allow connection from postgres user and know it's credentials?

dragoangel commented 3 months ago

Which image of the operator are you using? ghcr.io/zalando/postgres-operator:v1.12.2
Where do you run it - cloud or metal? Kubernetes or OpenShift? Bare Metal K8s
Are you running Postgres Operator in production? no
Type of issue? question

I can't provide postgres password and access to it from standby replica to main server, as this is different cluster that I not own, I only allowed to replicate it with user I was provided.

From what I see there is no way to setup rewind user, or just say to use same standby user for check_leader_is_not_in_recovery.

Based on: Patroni ENVIRONMENT:

PATRONI_REWIND_USERNAME: (optional) name for the user for pg_rewind; the user will be created during initialization of postgres 11+ and all necessary permissions will be granted.
PATRONI_REWIND_PASSWORD: (optional) password for the user for pg_rewind; the user will be created during initialization.
PATRONI_SUPERUSER_USERNAME: name for the superuser, set during initialization (initdb) and later used by Patroni to connect to the postgres. Also this user is used by pg_rewind.

I tried settings PATRONI_REWIND_USERNAME and PATRONI_REWIND_PASSWORD environment variables to the pods, but it doesn't change behavior of cluster (from what I read Spilo ignores PATRONI_* varuables).

I can't set rewind user settings in CRDs, and this is a problem.

Operator also not allows me to set same superuser login as standby login.

In result I facing next error:

2024-08-06 18:02:15,773 ERROR: Exception when working with leader
Traceback (most recent call last):
  File "/usr/local/lib/python3.10/dist-packages/patroni/postgresql/rewind.py", line 79, in check_leader_is_not_in_recovery
    with get_connection_cursor(connect_timeout=3, options='-c statement_timeout=2000', **conn_kwargs) as cur:
  File "/usr/lib/python3.10/contextlib.py", line 135, in __enter__
    return next(self.gen)
  File "/usr/local/lib/python3.10/dist-packages/patroni/postgresql/connection.py", line 157, in get_connection_cursor
    conn = psycopg.connect(**kwargs)
  File "/usr/local/lib/python3.10/dist-packages/patroni/psycopg.py", line 103, in connect
    ret = _connect(*args, **kwargs)
  File "/usr/lib/python3/dist-packages/psycopg2/__init__.py", line 122, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: connection to server at "main-server", port 5432 failed: FATAL:  password authentication failed for user "postgres"
connection to server at "main-server", port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "myip", user "postgres", database "postgres", no encryption

dragoangel commented 3 months ago

Also interesting that there is no attempts to use encryption, only "no encryption"

dragoangel commented 3 months ago

@FxKu sorry, for pinging you directly, but can you please advice on this case, if there any "workarounds" to set rewind user, or can postgres-operator add rewind user as another option that can be "optionally" set? And #2718 is also quite related, if you can look/help it would be very nice 🙏.

dragoangel commented 3 months ago

Also after "manual modification" of postgres.yaml I can set rewind user to be same as replication, but of course such settings will not persist over restarts.

There no option to turn off pg_rewind in CRD as well.

dragoangel commented 3 months ago

CREATE USER rewind_user LOGIN;
GRANT EXECUTE ON function pg_catalog.pg_ls_dir(text, boolean, boolean) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_stat_file(text, boolean) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text) TO rewind_user;
GRANT EXECUTE ON function pg_catalog.pg_read_binary_file(text, bigint, bigint, boolean) TO rewind_user;
GRANT SELECT ON TABLE pg_catalog.pg_authid TO rewind_user;
GRANT SELECT ON pg_catalog.pg_stat_get_wal_receiver TO rewind_user;
ALTER USER rewind_user REPLICATION;
--
GRANT USAGE ON SCHEMA pg_catalog TO rewind_user;

GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_current_wal_lsn() TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_walfile_name(pg_lsn) TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_current_wal_flush_lsn() TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_wal_lsn_diff(pg_lsn, pg_lsn) TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_receive_lsn() TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_wal_replay_paused() TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.pg_stat_get_wal_receiver() TO rewind_user;
GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO rewind_user;

GRANT SELECT ON TABLE pg_catalog.pg_settings TO rewind_user;
GRANT SELECT ON pg_catalog.pg_stat_get_wal_receiver TO rewind_user;

Will allow to set super_username as rewind_user. I do not understand why operator using pg_authid while it's enouth to use just pg_roles table? @FxKu

Since this catalog contains passwords, it must not be publicly readable. pg_roles is a publicly readable view on pg_authid that blanks out the password field.

https://www.postgresql.org/docs/current/catalog-pg-authid.html

zalando / postgres-operator

When we running standby cluster we must allow connection from postgres user and know it's credentials? #2720