Unable to connect the postgres instance

[PuneetPunamiya]

I created a postgres operator by following the (docs)[] and was able to install the postgres operator. Later created an instance of it and it created a database called hippo

I had a migration job which crreates table in the database using the creds from the secret created hippo-pguser-hippo

apiVersion: batch/v1
kind: Job
metadata:
  name: tekton-hub-db-migration
  labels:
    app: tekton-hub-db
spec:
  template:
    spec:
      containers:
        - name: tekton-hub-db-migration
          image: quay.io/tekton-hub/db-migration:v1.8.0
          env:
            - name: POSTGRES_HOST
              valueFrom:
                secretKeyRef:
                  name: hippo-pguser-hippo
                  key: host
            - name: POSTGRES_PORT
              valueFrom:
                secretKeyRef:
                  name: hippo-pguser-hippo
                  key: port
            - name: POSTGRES_DB
              valueFrom:
                secretKeyRef:
                  name: hippo-pguser-hippo
                  key: dbname
            - name: POSTGRES_USER
              valueFrom:
                secretKeyRef:
                  name: hippo-pguser-hippo
                  key: user
            - name: POSTGRES_PASSWORD
              valueFrom:
                secretKeyRef:
                  name: hippo-pguser-hippo
                  key: password
      restartPolicy: Never
  backoffLimit: 3

But after applying the above job on the cluster I'm getting the following error and not able to connect to the database

╰─ kubectl logs tekton-hub-db-migration-znkz6                                                                             
SKIP: loading env file .env.dev failed: open .env.dev: no such file or directory
{"level":"info","ts":1658918343.015952,"caller":"app/app.go:281","msg":"in \"production\" mode ","app":"hub"}
{"level":"info","ts":1658918348.0288486,"caller":"app/gorm_logger.go:76","msg":"","file":"/go/src/github.com/tektoncd/hub/api/pkg/app/app.go:288","db-error":"failed to connect to `host=hippo-primary.openshift-pipelines.svc user=hippo database=hippo`: server error (FATAL: no pg_hba.conf entry for host \"10.128.2.28\", user \"hippo\", database \"hippo\", no encryption (SQLSTATE 28000))"}
{"level":"error","ts":1658918348.0288894,"caller":"app/app.go:292","msg":"failed to establish database connection: [database=hippo user=hippo host=hippo-primary.openshift-pipelines.svc:5432]: failed to connect to `host=hippo-primary.openshift-pipelines.svc user=hippo database=hippo`: server error (FATAL: no pg_hba.conf entry for host \"10.128.2.28\", user \"hippo\", database \"hippo\", no encryption (SQLSTATE 28000))","app":"hub","stacktrace":"github.com/tektoncd/hub/api/pkg/app.APIBaseFromEnvFile\n\t/go/src/github.com/tektoncd/hub/api/pkg/app/app.go:292\ngithub.com/tektoncd/hub/api/pkg/app.APIBaseFromEnv\n\t/go/src/github.com/tektoncd/hub/api/pkg/app/app.go:255\nmain.main\n\t/go/src/github.com/tektoncd/hub/api/cmd/db/main.go:26\nruntime.main\n\t/usr/local/go/src/runtime/proc.go:250"}
FATAL: failed to initialise: failed to connect to `host=hippo-primary.openshift-pipelines.svc user=hippo database=hippo`: server error (FATAL: no pg_hba.conf entry for host "10.128.2.28", user "hippo", database "hippo", no encryption (SQLSTATE 28000))%    

I'm not sure if this is because of wrong host value

[PuneetPunamiya]

Also I tried the keycloak example from the docs, and the pod goes in CrashLoopBackOff state, is this expected

╰─ k get pods  -w                              
NAME                                                READY   STATUS             RESTARTS     AGE
hippo-backup-77rb-6t878                             0/1     Completed          0            7m35s
hippo-instance1-9np4-0                              4/4     Running            0            8m
hippo-repo-host-0                                   2/2     Running            0            8m
keycloak-5d9c485bc4-fwwkb                           0/1     CrashLoopBackOff   5 (4s ago)   3m21s
keycloakdb-00-88q6-0                                4/4     Running            0            3m20s
keycloakdb-00-ld77-0                                4/4     Running            0            3m20s
keycloakdb-backup-jbjw-6vjlc                        0/1     Completed          0            2m50s
keycloakdb-repo-host-0                              2/2     Running            0            3m20s

╰─ k logs keycloak-5d9c485bc4-fwwkb                    
Keycloak - Open Source Identity and Access Management

Find more information at: https://www.keycloak.org/docs/latest

Usage:

kc.sh [OPTIONS] [COMMAND]

Use this command-line tool to manage your Keycloak cluster.
Make sure the command is available on your "PATH" or prefix it with "./" (e.g.:
"./kc.sh") to execute from the current folder.

Options:

-cf, --config-file <file>
                     Set the path to a configuration file. By default, configuration properties are
                       read from the "keycloak.conf" file in the "conf" directory.
-h, --help           This help message.
-v, --verbose        Print out error details when running this command.
-V, --version        Show version information

Commands:

  build                   Creates a new and optimized server image.
  start                   Start the server.
  start-dev               Start the server in development mode.
  export                  Export data from realms to a file or directory.
  import                  Import data from a directory or a file.
  show-config             Print out the current configuration.
  tools                   Utilities for use and interaction with the server.
    completion            Generate bash/zsh completion script for kc.sh.

Examples:

  Start the server in development mode for local development or testing:

      $ kc.sh start-dev

  Building an optimized server runtime:

      $ kc.sh build <OPTIONS>

  Start the server in production mode:

      $ kc.sh start <OPTIONS>

  Enable auto-completion to bash/zsh:

      $ source <(kc.sh tools completion)

  Please, take a look at the documentation for more details before deploying in
production.

Use "kc.sh start --help" for the available options when starting the server.
Use "kc.sh <command> --help" for more information about other commands.

I think regarding keycloak, we already have a pr for this https://github.com/CrunchyData/postgres-operator/pull/3307

[PuneetPunamiya]

@andrewlecuyer, Can you please have a look at this issue, thanks in advance 🙂

[PuneetPunamiya]

@cbandy @tomswartz07, Can you please take a look at this issue, thanks!

[philipsd6]

I am having the same issue, though I am using an additional database added by following the documentation to add this block to the CRD:

spec:
  users:
    - name: myapp
      databases:
        - myapp

It's been a year since the last update, is there any new info?

[bkowalik]

I was facing the same issue as you @philipsd6 @PuneetPunamiya.

What helped me is to put following lines into PostgresCluster definition

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PostgresCluster
metadata:
  name: "tes"
  namespace: "test"
spec:
  patroni:
    dynamicConfiguration:
      postgresql:
        pg_hba:
          - host all all 0.0.0.0/0 scram-sha-256
          - host all all ::1/128 scram-sha-256

ocf this is not a complete definition, but essentially patroni.dynamicConfiguration.postgresql.pg_hba is what solved it.

[PuneetPunamiya]

I was able to solve the above issue by going inside the pod and updating the pg_hba.conf file to give the access to connect to the database

[bkowalik]

So precisely the same solution. Just what I proposed can be stored as a part of GitOps flow.

[PuneetPunamiya]

Yes 🙂

[dsessler7]

Hello @PuneetPunamiya,

We recommend making changes to the pg_hba.conf file via the spec as @bkowalik exhibited rather than editing it manually since patroni can overwrite the file.

I'd also like to note that the Keycloak example has been fixed.