PGAdmin TLS Connections

[jsievenpiper]

Have an idea to improve PGO? We'd love to hear it! We're going to need some information from you to learn more about your feature requests.

Please be sure you've done the following:

• Provide a concise description of your feature request.
• Describe your use case. Detail the problem you are trying to solve.
• Describe how you envision that the feature would work.
• Provide general information about your current PGO environment.

Overview

I have a deployed PostgresCluster that I'm trying to administer with the new PGAdmin resource. I'd like to have a strict cert-based authentication pattern (or sslMode: verify-full with password, mostly irrelvant which for this discussion) for connections to the cluster.

Use Case

I'm using custom TLS everywhere, managed by cert-manager with frequently-rotating certs (24hr). Ideally, even tools like PGAdmin would have such a requirement (I have the issuer configured to only issue short-lived certs as well, so making a special exception for PGAdmin would involve deploying an entirely new intermediate issuer with longer lifespan capabilities...). I followed the docs for mounting a secret into the PGAdmin instance, but it seems like these are mounted purely into the configuration directory, and aren't actually available as a PGAdmin user?

Is there another way to acccomplish this?

Desired Behavior

I'd like to be able to mount a PV that maps to the user-storage PGAdmin seems to be using for cert lookups, or even a turn-key way to supply a cert reference for cluster connections in the same way this is magically handled for PGBouncer and PGBackrest.

Environment

Tell us about your environment:

Please provide the following details:

• Platform: Kubernetes
• Platform Version: 1.28.5
• PGO Image Tag: ubi8-5.6.0-0
• Postgres Version: 16.3
• Storage: zfs-ebs
• Number of Postgres clusters: just one, for now.

Additional Information

Please provide any additional information that may be helpful.

[andrewlecuyer]

Hi @jsievenpiper, thanks for reaching out!

While setting up TLS connections to Postgres via pgAdmin (e.g. in order to use verify-full) should be possible (and easy), pgAdmin v8.8 has a bug that is currently preventing this from working properly: pgadmin-org/pgadmin4#7306.

The good news, however, is that a fix is available, which we're hoping will be available in the next pgAdmin release (e.g. pgAdmin v8.9): pgadmin-org/pgadmin4@9933a9a.

And based on some early/pre-release testing we've done, this fix does indeed appear to properly address the issue that we're seeing. So we're hoping to pull in this fix as soon as possible.

In the meantime, however, to provide a bit more context around how this should/will work, you'll essentially want to update your pgAdmin spec similar to the following:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PGAdmin
metadata:
  name: rhino
  namespace: mutiny
spec:
  config:
    files:
    - secret:
        name: hippo-pgadmin-cluster-cert
    settings:
      SHARED_STORAGE: [{ 'name': 'config-files', 'path': '/etc/pgadmin/conf.d', 'restricted_access': False}]
...

For this example I also have an associated PostgresCluster named hippo-pgadmin, which means CPK has created the hippo-cluster-cert Secret for me. However, you can point to any Secret containing the proper certificate information for your Postgres connections (e.g. if using custom TLS certs).

And what this will do is effectively make the contents of that Secret available for use to users within pgAdmin. E.g. in the following screenshot you can see that I can select the config-files shared storage, after which I see the contents of my hippo-pgadmin-cluster-cert Secret (e.g. the certs needed to verify-full, etc.)

Unfortunately, at the moment you will get an error when attempting to connect when using/selecting cert files from shared storage. But once the next release of pgAdmin is available, this should hopefully all work properly.

And in the meantime, there does appear to be a workaround available. Users can leverage the pgAdmin Storage Manager to download the required cert file from the config-files shared storage, and then upload it back to the My Storage area. Then, when setting up the connection to Postgres (e.g. when selecting the root cert for verify-full), simply select the required cert file from the My Storage area (rather than the config-files shared storage).

Hope this helps!