Support Secrets for PGAdmin CRD

[dave-tucker]

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:

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

Overview

I would like to use Kubernetes Secrets when configuring the PGAdmin CRD. For example the OAUTH2_CLIENT_SECRET in this example should be a Kubernetes secret.

Use Case

Describe your use case. Why do you want this feature? What problem will it solve? Why will it help you? Why will it make it easier to use PGO?

This will solve an issue whereby secrets are being checked in as plain text in Git repositories. A practice that is frowned upon (with good reason) by most security teams.

Desired Behavior

Describe how the feature would work. How do you envision interfacing with it?

Allow me to provide secrets as environment variables to the PGAdmin CR. This could look something like this:

apiVersion: postgres-operator.crunchydata.com/v1beta1
kind: PGAdmin
metadata:
  name: rhino
  namespace: postgres-operator
spec:
  dataVolumeClaimSpec:
    accessModes:
    - "ReadWriteOnce"
    resources:
      requests:
        storage: 1Gi
  serverGroups:
    - name: supply
      # An empty selector selects all postgresclusters in the Namespace
      postgresClusterSelector: {}
  config:
    settings:
      AUTHENTICATION_SOURCES: ['oauth2', 'internal']
      OAUTH2_CONFIG:
        - OAUTH2_NAME: "google"
          OAUTH2_DISPLAY_NAME: "Google"
          OAUTH2_CLIENT_ID: $GOOGLE_CLIENT_ID
          OAUTH2_CLIENT_SECRET: $GOOGLE_CLIENT_SECRET
          OAUTH2_TOKEN_URL: "https://oauth2.googleapis.com/token"
          OAUTH2_AUTHORIZATION_URL: "https://accounts.google.com/o/oauth2/auth"
          OAUTH2_API_BASE_URL: "https://openidconnect.googleapis.com/v1/"
          OAUTH2_SERVER_METADATA_URL: "https://accounts.google.com/.well-known/openid-configuration"
          OAUTH2_SCOPE: "openid email profile"
          OAUTH2_USERINFO_ENDPOINT: "userinfo"
          OAUTH2_SSL_CERT_VERIFICATION: "False" # for testing purposes
          OAUTH2_BUTTON_COLOR: "red"   
      OAUTH2_AUTO_CREATE_USER : "True"
      DEBUG: "True" # for testing purposes
      SERVER_MODE: "True"
  env:
    - name: GOOGLE_CLIENT_ID
       valueFrom:
          secretKeyRef:
             name: my-secret
             key: google_client_id
    - name: GOOGLE_CLIENT_SECRET
       valueFrom:
          secretKeyRef:
             name: my-secret
             key: google_client_secret

My understanding is that you're converting those settings directly into config.py. This feature would require the translation of:

OAUTH2_CLIENT_ID: $GOOGLE_CLIENT_ID

into:

OAUTH2_CLIENT_ID = os.environ["GOOGLE_CLIENT_ID"}

Environment

Tell us about your environment:

Please provide the following details:

• Platform: Kubernetes
• Platform Version: 1.30.4
• PGO Image Tag: ubi8-16.3-1
• Postgres Version: 16
• Storage: PersistentVolume
• Number of Postgres clusters: 1

Additional Information

Please provide any additional information that may be helpful.

[fabio-stix-cancom]

Hello,

I would like to express my interest in this topic as well. Working in a GitOps setting, where all secrets are sealed away, conflicts with having sensitive information like the client secret in plain text somewhere.

[brunnels]

I think the easiest way to resolve this is to add oath2ClientId and oath2ClientSecret to the crd same as configDatabaseURI instead of trying to add env interpolation.

spec:
  config:
    oath2ClientId:
      name: oauth2-creds-secret
      key: OAUTH_CLIENT_ID 
    oath2ClientSecret:
      name: oauth2-creds-secret
      key: OAUTH_CLIENT_SECRET

Then something pretty similar to this added to the internal/controller/standalone_pgadmin/pod.go at the appropriate places.

        configOath2ClientIdPath= "~postgres-operator/config-oauth2-clientid"
        configOath2ClientSecretPath  = "~postgres-operator/config-oauth2-clientsecret"

	if pgadmin.Spec.Config.Oath2ClientId != nil {
		config = append(config, corev1.VolumeProjection{
			Secret: initialize.Pointer(
				pgadmin.Spec.Config.Oath2ClientId .AsProjection(configOath2ClientIdPath),
			),
		})
	}

	if pgadmin.Spec.Config.Oath2ClientSecret != nil {
		config = append(config, corev1.VolumeProjection{
			Secret: initialize.Pointer(
				pgadmin.Spec.Config.Oath2ClientSecret.AsProjection(configOath2ClientSecretPath),
			),
		})
	}

	// configOath2ClientIdPath is the path for mounting the oauth2 client id
	configOath2ClientIdAbsolutePath = configMountPath + "/" + configOath2ClientIdPath

	// configOath2ClientSecretPath is the path for mounting the oauth2 client secret
	configOath2ClientSecretAbsolutePath = configMountPath + "/" + configOath2ClientSecretPath

	configSystem = `
if os.path.isfile('` + configOath2ClientIdAbsolutePath + `'):
    with open('` + configOath2ClientIdAbsolutePath + `') as _f:
        OAUTH2_CLIENT_ID = _f.read()

if os.path.isfile('` + configOath2ClientSecretAbsolutePath  + `'):
    with open('` + configOath2ClientSecretAbsolutePath + `') as _f:
        OAUTH2_CLIENT_SECRET = _f.read()
`

[brunnels]

Actually, I just realized that doesn't quite work because you can have multiple OAUTH2 configs in pgadmin. So will probably need to add individual OAUTH2_CONFIG objects to be loaded via a list of secrets. Each secret would have all it's key/value pairs added as a new OAUTH2_CONFIG object.

[brunnels]

This was resolved when oauthConfigurations was added so now we can add a json object with OAUTH2_CLIENT_ID and OAUTH2_CLIENT_SECRET to a secret and then define it like this:

    oauthConfigurations:
      - name: authelia
        secret:
          name: pgadmin-oauth2
          key: pgadmin-oauth2.json