postgresqlkubernetespgpool

Pgpool failed to authenticate with backend using md5, valid password not found in kubernetes

I am trying to use pgpool in kubernetes without specifying passwords in pool_passwd, but I am unable to use md5 as authentication method, I am using the Spilo image:

pgpool.conf:

    listen_addresses = '*'
    port = 5432
    socket_dir = '/var/run/pgpool'
    pcp_listen_addresses = '*'
    pcp_port = 9898
    pcp_socket_dir = '/var/run/pgpool'
    backend_hostname0 = '%v'
    backend_port0 = 5432
    backend_weight0 = 1
    backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
    backend_hostname1 = '%v'
    backend_port1 = 5432
    backend_weight1 = 1
    backend_flag1 = 'DISALLOW_TO_FAILOVER'
    sr_check_period = 0
    enable_pool_hba = off
    backend_clustering_mode = 'streaming_replication'
    num_init_children = 32
    max_pool = 4
    child_life_time = 300
    child_max_connections = 0
    connection_life_time = 0
    client_idle_limit = 0
    connection_cache = on
    load_balance_mode = on
    ssl = off
    failover_on_backend_error = off

pg_hba.conf:

      local     all             all                                  trust
      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
      host      all             all             0.0.0.0/0            md5 # added temporarily to allow access from pgpool
      hostnossl all             all                   all            reject
      hostssl   all             +zalandos             all            pam
      hostssl   all             all                   all            md5

With this configuration I got this error:

│ 2022-02-24 08:22:54: pid 39: ERROR:  failed to authenticate with backend using md5                                                                                                  │
│ 2022-02-24 08:22:54: pid 39: DETAIL:  valid password not found

However same configuration but with docker-compose it does work:

services:
  db_master:
    image: flant/spilo
    ports:
      - "5432:5432"

  pg_pool:
    build:
      dockerfile: pgpool.Dockerfile
      context: .
    depends_on:
      - db_master
    ports:
      - "9999:9999"

Or even using PAM as auth methods works

Solution

  • Ok, this is the reason why it was "working" with docker-compose (From the pgpool docs):

    Note: If Pgpool-II is operated in raw mode or there's only 1 backend configured, you don't need to setup pool_passwd.

    And also:

    This authentication method is the password-based authentication methods in which MD-5-hashed password is sent by client. Since Pgpool-II does not has the visibility of PostgreSQL's database user password and client application only sends the MD5-hash of the password, so md5 authentication in Pgpool-II is supported using the pool_passwd authentication file.

    Honestly it doesn't make much sense why pgpool has to compare the passwords with md5 and I don't understand why with only one backend is not required.

    Source: https://www.pgpool.net/docs/42/en/html/auth-methods.html