How to set password to connect Crunchy Postgresql Operator on K8S? #12
Comments
sertceps
changed the title
|
I would like to check the logs to figure out the cause. Could you show the result of the following command?
|
Hi. The full log is: The resutl of |
|
Thank you. |
|
Thank you. The log is: The pgpool-configmap.yaml is: apiVersion: apps/v1
kind: Deployment
metadata:
name: pgpool
spec:
replicas: 1
selector:
matchLabels:
app: pgpool
template:
metadata:
labels:
app: pgpool
spec:
containers:
- name: pgpool
image: pgpool/pgpool
env:
- name: POSTGRES_USERNAME
valueFrom:
secretKeyRef:
name: stars-pguser-stars
key: user
- name: POSTGRES_PASSWORD
valueFrom:
secretKeyRef:
name: stars-pguser-stars
key: password
volumeMounts:
- name: pgpool-config
mountPath: /config
volumes:
- name: pgpool-config
configMap:
name: pgpool-config
---
apiVersion: v1
kind: Service
metadata:
name: pgpool
spec:
selector:
app: pgpool
ports:
- name: pgpool-port
protocol: TCP
port: 9999
targetPort: 9999
|
0 is correct, because pgpool doesn't manage the status of PostgreSQL on k8s. If you can connect to pgpool and execute |
|
Thank you very much 😁 😁 😁. Is there other config should be set? |
Thank you very very much. I am sorry to trouble you, but I am stuck with the authentication error and can not connect to the database. When I run comman: kubectl port-forward svc/pgpool 9999:9999
psql -U stars -h 127.0.0.1 -p 9999the result is: And the log of my application deployed by kubectl is: The out put of command stars:md50d33dc185b8bb8f192a9a0bc4967a22cAfter I turned on the 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
The error log of my application is: I have tried use cleartext & md5 password & password in My 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'
---------------------------------Where I changed the default file.-------------
backend_hostname0 = 'stars-ha'
----------------------------------No--dash--in--real--config---file---------------
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
---------------------------------Where I changed the default file.-------------
backend_hostname1 = 'stars-replicas'
----------------------------------No--dash--in--real--config---file---------------
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'DISALLOW_TO_FAILOVER'
sr_check_period = 0
---------------------------------Where I changed the default file.-------------
enable_pool_hba = on
----------------------------------No--dash--in--real--config---file---------------
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
---------------------------------Where I changed the default file.-------------
ssl = on
----------------------------------No--dash--in--real--config---file---------------
failover_on_backend_error = off
---------------------------------Where I changed the default file.-------------
# allow_clear_text_frontend_auth = on
# I first turned on it, but document say `pool_hba.conf cannot be used with the parameter`, so I turned off it.
----------------------------------No--dash--in--real--config---file---------------
pool_hba.conf: |-
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
---------------------------------Where I changed the default file.-------------
host all all 0.0.0.0/0 md5
hostssl all all 0.0.0.0/0 md5
----------------------------------No--dash--in--real--config---file---------------My application config is: env:
- name: DATABASE_HOST
value: "pgpool"
- name: DATABASE_PORT
value: "9999"
- name: DATABASE_USERNAME
value: stars
- name: DATABASE_PASSWORD
valueFrom: { secretKeyRef: { name: stars-pguser-stars, key: password } }
# value: 463efb32846eb45bb948a8362148bee7
# value: 0d33dc185b8bb8f192a9a0bc4967a22c
# value: md50d33dc185b8bb8f192a9a0bc4967a22c
- name: DATABASE_DATABASE
valueFrom: { secretKeyRef: { name: stars-pguser-stars, key: dbname } }
|
|
Are you using PostgreSQL 14? |
|
Hi. |
|
Please ignore content above. I found that, when I using |
|
I faced the exact issue @sertceps what you need is to disable pgpool's md5 encryption of passwords, as Crunchy data secrets are already encrypted. In the container in In |
|
@johanjk Thank you very much. Now the 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 = 'stars-ha'
backend_port0 = 5432
backend_weight0 = 1
backend_flag0 = 'ALWAYS_PRIMARY|DISALLOW_TO_FAILOVER'
backend_hostname1 = 'stars-replicas'
backend_port1 = 5432
backend_weight1 = 1
backend_flag1 = 'DISALLOW_TO_FAILOVER'
sr_check_period = 0
enable_pool_hba = on
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 = on
failover_on_backend_error = off
# allow_clear_text_frontend_auth = 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 scram-sha-256
hostssl all all 0.0.0.0/0 scram-sha-256
apiVersion: apps/v1
kind: Deployment
metadata:
name: pgpool
spec:
replicas: 1
selector:
matchLabels:
app: pgpool
template:
metadata:
labels:
app: pgpool
spec:
containers:
- name: pgpool
image: pgpool/pgpool
env:
- name: POSTGRES_USERNAME
valueFrom:
secretKeyRef:
name: stars-pguser-stars
key: user
- name: POSTGRES_PASSWORD
valueFrom:
secretKeyRef:
name: stars-pguser-stars
key: password
volumeMounts:
- name: pgpool-config
mountPath: /config
volumes:
- name: pgpool-config
configMap:
name: pgpool-config
---
apiVersion: v1
kind: Service
metadata:
name: pgpool
spec:
selector:
app: pgpool
ports:
- name: pgpool-port
protocol: TCP
port: 9999
targetPort: 9999
Application env is: env:
- name: DATABASE_HOST
value: "pgpool"
- name: DATABASE_PORT
value: "9999"
- name: DATABASE_USERNAME
valueFrom: { secretKeyRef: { name: stars-pguser-stars, key: user } }
- name: DATABASE_PASSWORD
valueFrom: { secretKeyRef: { name: stars-pguser-stars, key: password } }
- name: DATABASE_DATABASE
valueFrom: { secretKeyRef: { name: stars-pguser-stars, key: dbname } } |
I will look into this issue. |
|
@sertceps see my pull request. it allows setting |
|
@pengbo0328 @johanjk I found a way to solve it. In the document of PGO, User Management, it describe a way to Use the offical script to generate md5 PASSWORD=example
VERIFIER=md595c1cdc86aed4cacfb226c3a048e1ed8
PASSWORD=$(echo -n $PASSWORD | base64 | tr -d '\n')
VERIFIER=$(echo -n $VERIFIER | base64 | tr -d '\n')Patch password to pgo cluster: kubectl patch secret -n postgres-operator hippo-pguser-hippo -p \
"{\"data\":{\"password\":\"${PASSWORD}\",\"verifier\":\"${VERIFIER}\"}}"And the configMap add: hostssl all all 0.0.0.0/0 md5It seems work fine on my host now. Emm, I don't know if it'll be a problem. |
|
hey Guys : |
|
it works using pgpool minimal deployement( using ENV variables instead of config map ) : https://github.com/pgpool/pgpool2_on_k8s/blob/master/pgpool-deploy-minimal.yaml |
Hi.
I am reading and experimenting on the document
8.5. Pgpool-II on Kubernetes.When I enter pod of pgpool,
pool_passwdis empty.And the logs of pgpool-pod is :
I want to know how to set password to connect postgresql cluster managered by Crunchy Operator?
pgpool-configmap.yaml is:
pgpool-deploy.yaml is:
stars-pguser-stars describe is:
$ kubectl describe secrets stars-pguser-stars Name: stars-pguser-stars Namespace: default Labels: postgres-operator.crunchydata.com/cluster=stars postgres-operator.crunchydata.com/pguser=stars postgres-operator.crunchydata.com/role=pguser Annotations: <none> Type: Opaque Data ==== host: 25 bytes password: 24 bytes port: 4 bytes uri: 92 bytes user: 5 bytes verifier: 133 bytes dbname: 7 bytesThe content of secret is :
kubectl get secrets stars-pguser-stars -o jsonpath={.data}{ "dbname": "dXBncmFkZQ==", "host": "c3RhcnMtcHJpbWFyeS5kZWZhdWx0LnN2Yw==", "password": "U0drOVcvZnNFPyl0Mlg2MmVQQV4rQ1R9", "port": "NTQzMg==", "uri": "cG9zdGdyZXNxbDovL3N0YXJzOlNHazlXJTJGZnNFJTNGJTI5dDJYNjJlUEElNUUrQ1QlN0RAc3RhcnMtcHJpbWFyeS5kZWZhdWx0LnN2Yzo1NDMyL3VwZ3JhZGU=", "user": "c3RhcnM=", "verifier": "U0NSQU0tU0hBLTI1NiQ0MDk2OllpKzZzb3NwMlFrdlNRUnl0a20wQmc9PSRoM0V6V2EydzNBaTJQV1dGaTFobTdaUWRDamVqVGhHbWM2SjJuNGh3U240PToxcGVLRll6cTJBOHp2NTBYeThFNHV1Q2p0UGJxSzJMQ1RjcGtYeW5hQWI0PQ==" }The text was updated successfully, but these errors were encountered: