scaler[ postgresql ] cannot use scaledobject.spec.triggers.passowrd as connection password

[spwangxp]

Report

when i set scaledobject.spec.triggers.passowrd as connection password, it's not work ❌, the log shows "password" are not set;
when i set scaledobject.spec.triggers.passowrdFromEnv as connection password, it's worked fine ✅;

Expected Behavior

the scaledobject.spec.triggers.passowrd works as expect

Actual Behavior

see report

Steps to Reproduce the Problem

1. see report

Logs from KEDA operator

2022-08-26T02:33:41Z    ERROR   scalehandler    Error getting scalers   {"object": {"apiVersion": "keda.sh/v1alpha1", "kind": "ScaledObject", "namespace": "default", "name": "pg-scaler"}, "error": "error establishing postgreSQL connection: pq: password authentication failed for user \"admin\""}

KEDA Version

2.8.0

Kubernetes Version

1.23

Platform

Other

Scaler Details

postgresql

Anything else?

No response

[tomkerkhove]

I don't think we should do this as this would mean there are hardcoded passwords, we should use trigger authentication instead (which should already be supported I think)

[tomkerkhove]

this is also not really a bug as password is supported, but through trigger authentication:
https://keda.sh/docs/2.8/scalers/postgresql/#authentication-parameters

[spwangxp]

https://github.com/kedacore/keda-docs/blame/main/content/docs/2.9/scalers/postgresql.md#L81

#3601

[tomkerkhove]

The docs that you are referring to is purely for TriggerAuthentication and not for trigger metadata.

[Sathya-omnius]

HI Team , any solution for this issue I am facing the same issue when I provide postgres data base details

[JorTurFer]

Your trigger authentication isn't doing anything because the parameter that you are setting is wrong, passwordFromEnv it's a key for metadata section to read the environment variable from the pod with that name.
Has the pod an env named postgrespassword with the password? If yes, You can remove the TriggerAuthentication and the reference to it, keeping simpler the ScaledObject. If you prefer to use the TriggerAuthentication, the parameter has to be password and not passwordFromEnv and you have to remove passwordFromEnv from metadata section.

In any case, I don't think that this duplication is the root cause. Please, check the username and the password to ensure that are correct and also validate if the user has enough permission in the database:

• https://help.heroku.com/DR0TTWWD/seeing-fatal-no-pg_hba-conf-entry-errors-in-postgres

You should also check the connection parameters. The problem is in a misconfiguration at some place

[Sathya-omnius]

Hello,

thanks for the reply we have env variable passwordFromEnv: postgrespassword but still its not working,when i use TriggerAuthentication
i am referring to
authenticationRef:
name: postgresqlpassword
as per the Keda document, where should i use password if I use TriggerAuthentication

[Sathya-omnius]

when we enable (sslmode: require) I am getting following error
ERROR Reconciler error {"controller": "scaledobject", "controllerGroup": "keda.sh", "controllerKind": "ScaledObject", "ScaledObject": {"name":"abby-scaledobject5","namespace":"engine"}, "namespace": "engine", "name": "abby-scaledobject5", "reconcileID": "181c20b1-ede0-40ac-81c1-b1595109a858", "error": "error establishing postgreSQL connection: pq: password authentication failed for user "decicdqa""}

[JorTurFer]

apiVersion: keda.sh/v1alpha1
kind: TriggerAuthentication
metadata:
  name: postgresqlpassword
spec:   
  secretTargetRef:                                                       
  - parameter: password
    name: postgres-909                                           
    key: postgrespassword    

[Sathya-omnius]

thank you so much for your quick response ,every thing is working fine by doing some changes w.r.t sslmode

[thinkbigdatanalytics]

@Sathya-omnius @JorTurFer @tomkerkhove

I have the same problem "**server error (FATAL: no pg_hba.conf entry for host "10.75.9.197**"

I don't see anything wrong in this config, please let me know if there are any issues here. I tried both passwordFromEnv and TriggerAuthentication, and it does have the same error. Looks like the password is getting fetched.

apiVersion: v1
kind: Secret
metadata:
  name: test-db-secret
  namespace: dev
type: Opaque
data:
  PG_PASSWORD: "G3BuGGDdBdjZmcw=="
---
apiVersion: keda.sh/v1alpha1
kind: TriggerAuthentication
metadata:
  name: keda-trigger-auth-postgres-secret
  namespace: dev
spec:
  secretTargetRef:
  - parameter: password
    name: test-db-secret
    key: PG_PASSWORD
  env:
  - parameter: password
    name: PG_PASSWORD
    containerName: test-app
---
apiVersion: apps/v1
kind: Deployment
metadata:
  name: test-app
  namespace: dev
spec:
  replicas: 1
  selector:
    matchLabels:
        app: test-app
  template:
    metadata:
      labels:
        app: test-app
    spec:
      containers:
        - name: test-app
          image: linuxacademycontent/store-products:1.0.0
          ports:
            - containerPort: 80
          envFrom:
            - secretRef:
                name: test-db-secret
---
apiVersion: keda.sh/v1alpha1
kind: ScaledObject
metadata:
  name: test-app
spec:
  scaleTargetRef:
    name: test-app
  pollingInterval: 10
  cooldownPeriod: 30
  maxReplicaCount: 2  
  minReplicaCount: 0
  triggers:
    - type: postgresql
      metadata:
        userName: testuser
        host: test_host
        port: "5432"
        dbName: test_db
        sslmode: disable
        query: "SELECT ceil(message_count) as message_count FROM instance"
        targetQueryValue: "1"
      authenticationRef:
        name: keda-trigger-auth-postgres-secret

[JorTurFer]

Could you share the whole operator logs related with this?
Is the ScaledObject in the same namespace where the TriggerAuthentication is?

[Stephan-adj]

I was facing the same issue when trying to connect the keda operator to my postgresql BDD, since keda was deployed in an other namespace I needed to change the host so that the FQDN was right. The TriggerAuthentication method worked fine for me. I just didn't need the env: - parameter: password name: PG_PASSWORD containerName: test-app part.
Thank you very much.

keda_helm_version: 2.14
postgreSQL_version: 15-debian-11
kubernetes_server_version: 1.28.6