search

pgpool / pgpool2_on_k8s

57 stars 36 forks source link

ISsue in md5 authentication on backend DB #27

Closed harshchoudhary727 closed 4 months ago

harshchoudhary727 commented 1 year ago

root@rancher pgpool2_on_k8s]# cat pgpool-configmap.yaml apiVersion: v1 kind: ConfigMap metadata: name: pgpool-config labels: name: pgpool-config data: pgpool.conf: |- listen_addresses = '' port = 9999 socket_dir = '/var/run/pgpool' pcp_listen_addresses = '' pcp_port = 9898 pcp_socket_dir = '/var/run/pgpool' backend_hostname0 = '192.168.100.160' backend_port0 = 5432 backend_weight0 = 1 backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER' sr_check_period = 0 health_check_period = 0 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 enable_pool_hba = on pool_passwd = postgres failover_on_backend_error = off log_min_messages = warning

If pool_hba.conf isn't configured, Pgpool-II will automatically generate it.

Note that to use pool_hba.conf you must set enable_pool_hba = on.

pool_hba.conf: |- local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust host all all 0.0.0.0/0 md5 hostssl all all 0.0.0.0/0 md5 [root@rancher pgpool2_on_k8s]#

[root@rancher pgpool2_on_k8s]# cat pgpool-deploy-metrics.yaml apiVersion: apps/v1 kind: Deployment metadata: name: pgpool spec: replicas: 1 selector: matchLabels: app: pgpool template: metadata: labels: app: pgpool spec: containers:

[root@rancher pgpool2_on_k8s]# kubectl logs -f pgpool-89f975ff9-ffzrr Defaulted container "pgpool" out of: pgpool, pgpool-stats Configuring Pgpool-II... Custom pgpool.conf file detected. Use custom configuration files. Skip generating pool_passwd. Use password authentication between client and Pgpool-II and force ssl on all connections in pool_hba.conf. Generating pcp.conf... Custom pool_hba.conf file detected. Use custom pool_hba.conf. Generating self-signed certificate... Starting Pgpool-II... 2023-03-08 11:39:49.913: main pid 53: LOG: Backend status file /tmp/pgpool_status does not exist 2023-03-08 11:39:49.913: main pid 53: LOG: health_check_stats_shared_memory_size: requested size: 12288 2023-03-08 11:39:49.913: main pid 53: LOG: memory cache initialized 2023-03-08 11:39:49.913: main pid 53: DETAIL: memcache blocks :64 2023-03-08 11:39:49.913: main pid 53: LOG: allocating (136981824) bytes of shared memory segment 2023-03-08 11:39:49.913: main pid 53: LOG: allocating shared memory segment of size: 136981824 2023-03-08 11:39:49.957: main pid 53: LOG: health_check_stats_shared_memory_size: requested size: 12288 2023-03-08 11:39:49.957: main pid 53: LOG: health_check_stats_shared_memory_size: requested size: 12288 2023-03-08 11:39:49.957: main pid 53: LOG: memory cache initialized 2023-03-08 11:39:49.957: main pid 53: DETAIL: memcache blocks :64 2023-03-08 11:39:49.958: main pid 53: LOG: pool_discard_oid_maps: discarded memqcache oid maps 2023-03-08 11:39:49.962: main pid 53: LOG: unix_socket_directories[0]: /tmp/.s.PGSQL.9999 2023-03-08 11:39:49.962: main pid 53: LOG: listen address[0]: 2023-03-08 11:39:49.962: main pid 53: LOG: Setting up socket for 0.0.0.0:9999 2023-03-08 11:39:49.962: main pid 53: LOG: Setting up socket for :::9999 2023-03-08 11:39:49.965: main pid 53: LOG: find_primary_node_repeatedly: waiting for finding a primary node 2023-03-08 11:39:49.965: main pid 53: LOG: listen address[0]: 2023-03-08 11:39:49.965: main pid 53: LOG: Setting up socket for 0.0.0.0:9898 2023-03-08 11:39:49.965: main pid 53: LOG: Setting up socket for :::9898 2023-03-08 11:39:49.965: pcp_main pid 87: LOG: PCP process: 87 started 2023-03-08 11:39:49.965: sr_check_worker pid 88: LOG: process started 2023-03-08 11:39:49.965: health_check pid 89: LOG: process started 2023-03-08 11:39:49.966: main pid 53: LOG: pgpool-II successfully started. version 4.4.2 (nurikoboshi) 2023-03-08 11:39:49.966: main pid 53: LOG: node status[0]: 0 2023-03-08 11:39:50.647: child pid 86: ERROR: failed to authenticate with backend using SCRAM 2023-03-08 11:39:50.647: child pid 86: DETAIL: valid password not found 2023-03-08 11:39:52.038: child pid 85: ERROR: failed to authenticate with backend using SCRAM 2023-03-08 11:39:52.038: child pid 85: DETAIL: valid password not found 2023-03-08 11:40:10.216: child pid 86: ERROR: failed to authenticate with backend using SCRAM 2023-03-08 11:40:10.216: child pid 86: DETAIL: valid password not found 2023-03-08 11:40:34.198: child pid 59: ERROR: failed to authenticate with backend using SCRAM 2023-03-08 11:40:34.199: child pid 59: DETAIL: valid password not found 2023-03-08 11:41:17.207: child pid 62: ERROR: failed to authenticate with backend using SCRAM 2023-03-08 11:41:17.207: child pid 62: DETAIL: valid password not found

pengbo0328 commented 4 months ago

@harshchoudhary727 It seems PostgreSQL is using SCRAM authentication.

You need to specify scram-sha-256 to PGPOOL_PASSWORD_ENCRYPTION_METHOD:

- name: PGPOOL_PASSWORD_ENCRYPTION_METHOD
  value: "scram-sha-256"
harshchoudhary727 commented 4 months ago

Yes thanks @pengbo0328 I applied the same and it is working in PROD.

pengbo0328 commented 4 months ago

@harshchoudhary727 Sorry for the late response. I am going to close this issue.

harshchoudhary727 commented 4 months ago

No issue @pengbo0328 Thanks to you