Unified PostgreSQL Configuration

[emmanuelmathot]

Problem Statement

The EOAPI chart currently has a messy approach to managing PostgreSQL database access and credentials. The issues include:

1. Fragmented Configuration: There are multiple ways to configure PostgreSQL access, but no unified approach.
2. Non-Standard Environment Variables: The chart uses custom environment variables like POSTGRES_USER instead of standard PostgreSQL variables like PGUSER.
3. Limited Flexibility: The current implementation doesn't support all required deployment scenarios.
4. Redundant Variables: Variables like POSTGRES_HOST_READER and POSTGRES_HOST_WRITER add unnecessary complexity.

Requirements

We need an unified way to manage all PostgreSQL access data and credentials that supports several options:

1. Default: PostgreSQL cluster is enabled using the CrunchyData PGO. This option creates a PostgreSQL cluster with options and users via a dependency chart. Access info and credentials are stored in a secret with the name of the configured user and passed to standard PostgreSQL environment variables.

2. User-managed with plain text credentials: PostgreSQL cluster is disabled and the user passes access info and credentials directly in the values. This is not recommended but available.

3. User-managed with secret credentials: PostgreSQL cluster is disabled and the user passes access info and credentials in a secret that they reference in the values.

Proposed Solution

1. Create a Unified PostgreSQL Configuration Section

Add a new postgresql section in values.yaml while maintaining the existing postgrescluster section for backward compatibility:

# New unified PostgreSQL configuration
postgresql:
  # Management type: "postgrescluster" (default), "external-plaintext", or "external-secret"
  type: "postgrescluster"
  
  # Configuration for external PostgreSQL (used when type is "external-plaintext")
  external:
    # Connection information
    host: ""
    port: "5432"
    database: "eoapi"
    
    # Credentials configuration (used when type is "external-plaintext")
    credentials:
      username: ""
      password: ""
    
    # Secret reference (used when type is "external-secret")
    existingSecret:
      name: ""
      # Key mapping for the secret
      keys:
        username: "username"
        password: "password"
        # Optional: if these are provided in the secret
        host: "host"
        port: "port"
        database: "database"

# Maintain existing postgrescluster section for backward compatibility
postgrescluster:
  enabled: true
  postgresVersion: 16
  postGISVersion: 3.4
  pgBouncerReplicas: 1
  monitoring: false
  instances:
  - name: eoapi
    replicas: 1
    dataVolumeClaimSpec:
      accessModes:
      - "ReadWriteOnce"
      resources:
        requests:
          storage: "10Gi"
          cpu: "1024m"
          memory: "3048Mi"
  users:
    - name: postgres
      databases:
        - eoapi
        - postgres
    - name: eoapi
      databases:
        - eoapi
        - postgres
      password:
        type: AlphaNumeric

2. Update Helper Templates

Create new helper templates that support all three scenarios and use standard PostgreSQL environment variables:

{{/*
PostgreSQL environment variables based on the configured type
*/}}
{{- define "eoapi.postgresqlEnv" -}}
{{- if eq .Values.postgresql.type "postgrescluster" }}
  {{- include "eoapi.postgresclusterSecrets" . }}
{{- else if eq .Values.postgresql.type "external-plaintext" }}
  {{- include "eoapi.externalPlaintextPgstacSecrets" . }}
{{- else if eq .Values.postgresql.type "external-secret" }}
  {{- include "eoapi.externalSecretPgstacSecrets" . }}
{{- end }}
{{- end }}

{{/*
PostgreSQL cluster secrets
*/}}
{{- define "eoapi.postgresclusterSecrets" -}}
{{- range $userName, $v := .Values.postgrescluster.users -}}
{{/* do not render anything for the "postgres" user */}}
{{- if not (eq (index $v "name") "postgres") }}
# Standard PostgreSQL environment variables
- name: PGUSER
  valueFrom:
    secretKeyRef:
      name: {{ $.Release.Name }}-pguser-{{ index $v "name" }}
      key: user
- name: PGPORT
  valueFrom:
    secretKeyRef:
      name: {{ $.Release.Name }}-pguser-{{ index $v "name" }}
      key: port
- name: PGHOST
  valueFrom:
    secretKeyRef:
      name: {{ $.Release.Name }}-pguser-{{ index $v "name" }}
      key: host
- name: PGPASSWORD
  valueFrom:
    secretKeyRef:
      name: {{ $.Release.Name }}-pguser-{{ index $v "name" }}
      key: password
- name: PGDATABASE
  valueFrom:
    secretKeyRef:
      name: {{ $.Release.Name }}-pguser-{{ index $v "name" }}
      key: dbname
- name: PGBOUNCER_URI
  valueFrom:
    secretKeyRef:
      name: {{ $.Release.Name }}-pguser-{{ index $v "name" }}
      key: pgbouncer-uri
{{- end }}
{{- end }}
- name: PGADMIN_URI
  valueFrom:
    secretKeyRef:
      name: {{ .Release.Name }}-pguser-postgres
      key: uri
{{- end }}

{{/*
External PostgreSQL with plaintext credentials
*/}}
{{- define "eoapi.externalPlaintextPgstacSecrets" -}}
# Standard PostgreSQL environment variables
- name: PGUSER
  value: {{ .Values.postgresql.external.credentials.username | quote }}
- name: PGPORT
  value: {{ .Values.postgresql.external.port | quote }}
- name: PGHOST
  value: {{ .Values.postgresql.external.host | quote }}
- name: PGPASSWORD
  value: {{ .Values.postgresql.external.credentials.password | quote }}
- name: PGDATABASE
  value: {{ .Values.postgresql.external.database | quote }}
{{- end }}

{{/*
External PostgreSQL with secret credentials
*/}}
{{- define "eoapi.externalSecretPgstacSecrets" -}}
# Standard PostgreSQL environment variables
- name: PGUSER
  valueFrom:
    secretKeyRef:
      name: {{ .Values.postgresql.external.existingSecret.name }}
      key: {{ .Values.postgresql.external.existingSecret.keys.username }}
- name: PGPASSWORD
  valueFrom:
    secretKeyRef:
      name: {{ .Values.postgresql.external.existingSecret.name }}
      key: {{ .Values.postgresql.external.existingSecret.keys.password }}

# Host, port, and database can be from the secret or from values
{{- if .Values.postgresql.external.existingSecret.keys.host }}
- name: PGHOST
  valueFrom:
    secretKeyRef:
      name: {{ .Values.postgresql.external.existingSecret.name }}
      key: {{ .Values.postgresql.external.existingSecret.keys.host }}
{{- else }}
- name: PGHOST
  value: {{ .Values.postgresql.external.host | quote }}
{{- end }}

{{- if .Values.postgresql.external.existingSecret.keys.port }}
- name: PGPORT
  valueFrom:
    secretKeyRef:
      name: {{ .Values.postgresql.external.existingSecret.name }}
      key: {{ .Values.postgresql.external.existingSecret.keys.port }}
{{- else }}
- name: PGPORT
  value: {{ .Values.postgresql.external.port | quote }}
{{- end }}

{{- if .Values.postgresql.external.existingSecret.keys.database }}
- name: PGDATABASE
  valueFrom:
    secretKeyRef:
      name: {{ .Values.postgresql.external.existingSecret.name }}
      key: {{ .Values.postgresql.external.existingSecret.keys.database }}
{{- else }}
- name: PGDATABASE
  value: {{ .Values.postgresql.external.database | quote }}
{{- end }}
{{- end }}

3. Add Validation

Add validation to ensure proper configuration for each scenario:

{{/*
Validate PostgreSQL configuration
*/}}
{{- define "eoapi.validatePostgresql" -}}
{{- if eq .Values.postgresql.type "postgrescluster" }}
  {{- if not .Values.postgrescluster.enabled }}
    {{- fail "When postgresql.type is 'postgrescluster', postgrescluster.enabled must be true" }}
  {{- end }}
  {{- include "eoapi.validatePostgresCluster" . }}
{{- else if eq .Values.postgresql.type "external-plaintext" }}
  {{- if not .Values.postgresql.external.host }}
    {{- fail "When postgresql.type is 'external-plaintext', postgresql.external.host must be set" }}
  {{- end }}
  {{- if not .Values.postgresql.external.credentials.username }}
    {{- fail "When postgresql.type is 'external-plaintext', postgresql.external.credentials.username must be set" }}
  {{- end }}
  {{- if not .Values.postgresql.external.credentials.password }}
    {{- fail "When postgresql.type is 'external-plaintext', postgresql.external.credentials.password must be set" }}
  {{- end }}
{{- else if eq .Values.postgresql.type "external-secret" }}
  {{- if not .Values.postgresql.external.existingSecret.name }}
    {{- fail "When postgresql.type is 'external-secret', postgresql.external.existingSecret.name must be set" }}
  {{- end }}
  {{- if not .Values.postgresql.external.existingSecret.keys.username }}
    {{- fail "When postgresql.type is 'external-secret', postgresql.external.existingSecret.keys.username must be set" }}
  {{- end }}
  {{- if not .Values.postgresql.external.existingSecret.keys.password }}
    {{- fail "When postgresql.type is 'external-secret', postgresql.external.existingSecret.keys.password must be set" }}
  {{- end }}
  {{- if not .Values.postgresql.external.existingSecret.keys.host }}
    {{- if not .Values.postgresql.external.host }}
      {{- fail "When postgresql.type is 'external-secret' and existingSecret.keys.host is not set, postgresql.external.host must be set" }}
    {{- end }}
  {{- end }}
{{- else }}
  {{- fail "postgresql.type must be one of: 'postgrescluster', 'external-plaintext', 'external-secret'" }}
{{- end }}
{{- end }}

4. Update Deployment Templates

Update the service deployment template to use the new helper:

env:
  {{- include "eoapi.postgresqlEnv" $ | nindent 12 }}

5. Backward Compatibility

Add logic to map legacy configuration to the new structure:

{{/*
Map legacy configuration to new postgresql configuration
*/}}
{{- define "eoapi.mapLegacyPostgresql" -}}
{{- $postgresql := dict }}
{{- if .Values.postgrescluster.enabled }}
  {{- $_ := set $postgresql "type" "postgrescluster" }}
{{- else if .Values.db.enabled }}
  {{- $_ := set $postgresql "type" "external-plaintext" }}
  {{- $external := dict }}
  {{- $_ := set $external "host" .Values.db.settings.secrets.POSTGRES_HOST }}
  {{- $_ := set $external "port" .Values.db.settings.secrets.POSTGRES_PORT }}
  {{- $_ := set $external "database" .Values.db.settings.secrets.POSTGRES_DB }}
  {{- $credentials := dict }}
  {{- $_ := set $credentials "username" .Values.db.settings.secrets.POSTGRES_USER }}
  {{- $_ := set $credentials "password" .Values.db.settings.secrets.POSTGRES_PASSWORD }}
  {{- $_ := set $external "credentials" $credentials }}
  {{- $_ := set $postgresql "external" $external }}
{{- else }}
  {{- $_ := set $postgresql "type" "postgrescluster" }}
{{- end }}
{{- $postgresql | toYaml }}
{{- end }}

Breaking Changes

This implementation introduces breaking changes:

1. Environment Variable Standardization: Switching from custom environment variables (POSTGRES_) to standard PostgreSQL variables (PG).

2. Removal of Redundant Variables: Removing POSTGRES_HOST_READER, POSTGRES_HOST_WRITER, and DATABASE_URL.

These changes require a major version bump and clear documentation for migration.

Benefits

1. Unified Configuration: A single, consistent way to configure PostgreSQL access.

2. Standard Environment Variables: Using standard PostgreSQL environment variables improves compatibility with PostgreSQL tools and libraries.

3. Flexible Deployment Options: Support for all three required deployment scenarios.

4. Improved Maintainability: Cleaner, more organized code that follows Helm best practices.

5. Better Validation: Comprehensive validation to prevent misconfiguration.

6. Backward Compatibility: Maintaining the existing postgrescluster section for backward compatibility.

Implementation Plan

1. Create a new branch for these changes.
2. Implement the new configuration structure in values.yaml.
3. Update the helper templates in _helpers.tpl.
4. Update the deployment templates to use the new helpers.
5. Add validation for the new configuration.
6. Update documentation to explain the new configuration options.
7. Test all three deployment scenarios.
8. Create a pull request with a major version bump.

Example Configurations

1. Default: Using PostgreSQL Cluster

postgresql:
  type: "postgrescluster"

postgrescluster:
  enabled: true
  postgresVersion: 16
  postGISVersion: 3.4
  # ... other postgrescluster settings

2. User-managed with Plain Text Credentials

postgresql:
  type: "external-plaintext"
  external:
    host: "my-postgres-server.example.com"
    port: "5432"
    database: "eoapi"
    credentials:
      username: "eoapi_user"
      password: "my-secure-password"

postgrescluster:
  enabled: false

3. User-managed with Secret Credentials

postgresql:
  type: "external-secret"
  external:
    # These are optional if provided in the secret
    host: "my-postgres-server.example.com"
    port: "5432"
    database: "eoapi"
    existingSecret:
      name: "my-postgres-credentials"
      keys:
        username: "username"
        password: "password"
        # Optional: if these are provided in the secret
        host: "host"
        port: "port"
        database: "database"

postgrescluster:
  enabled: false

Conclusion

This approach provides a unified way to manage PostgreSQL access data and credentials in the EOAPI chart, supporting all three required scenarios while maintaining backward compatibility with the existing postgrescluster configuration. The standardization to official PostgreSQL environment variables improves compatibility and maintainability, and the removal of redundant variables simplifies the configuration.

[emmanuelmathot]

Cc @batpad @sunu @j08lue @pantierra

[pantierra]

Love this!

I did find the two potential places for host, port, and database a bit confusing. That said, I don’t have a more elegant structure in mind just yet. Probably with the validation it is just fine.

[sunu]

Love the idea of standardizing Postgres configs!

I haven’t dug into this too much, so correct me if I’m off -- but I think some services like stac-fastapi-pgstac expect non-standard env vars (e.g., POSTGRES_USER instead of PGUSER link).

Feels like the chart should expose the standard vars like PGUSER, and we can use the helper to map those to whatever each eoAPI service needs.

[knordstrom-muon]

This looks like a very reasonable solution to me!