PostgreSQL with TLS Certificate generate by certmanager - SSL error code 214748366

[lenglet-k]

Hi,

I use certmanager for create my postgresql cluster. And on a from sractch install, i have this error:

FATAL: could not load server certificate file "/tls/tls.crt": SSL error code 2147483661. I don't understand the problem.

This is my config:

....
spec:
  databases:
    database_a: abc
  numberOfInstances: 1
  postgresql:
    parameters:
      password_encryption: scram-sha-256
    version: "16"
  resources:
    limits:
      memory: 256Mi
    requests:
      cpu: 50m
      memory: 128Mi
  teamId: userA
  tls:
    caSecretName: abc-pgsql-tls
    secretName: abc-pgsql-tls
  users:
    userA: []
    zalando:
    - superuser
    - createdb
  volume:
    size: 1Gi

My certificate:

spec:
  commonName: abc-postgresql
  duration: 2160h0m0s
  issuerRef:
    kind: ClusterIssuer
    name: abc-root-ca-issuer
  privateKey:
    algorithm: ECDSA
    size: 256
  renewBefore: 360h0m0s
  secretName: abc-pgsql-tls
  subject:
    organizations:
    - pgsql
  usages:
  - digital signature
  - key encipherment

[sgielen]

I believe this is a permission denied error when the Postgres user tries to load the certs. They are normally only readable by root but postgres runs as postgres.

root@database-0:/home/postgres# ls -la /tls
[...]
lrwxrwxrwx 1 root root   14 Feb 11 13:43 tls.key -> ..data/tls.key

To fix this, change your Postgres cluster manifest to add a spec.spiloFSGroup set to the ID of the group of the postgres user, which by default, is 103:

root@database-0:/home/postgres# id postgres
uid=101(postgres) gid=103(postgres) groups=103(postgres),0(root),102(ssl-cert)

This way, as configmaps/secrets are mounted, they are readable by postgres instead of only by root:

root@database-0:/home/postgres# ls -la /tls
[...]
lrwxrwxrwx 1 root postgres   14 Feb 11 13:56 tls.key -> ..data/tls.key

For me, postgresql now starts and properly encrypts communication with the key & cert provided by cert-manager.