Configure SSL

[thepostgresguy]

I mounted a TLS secret successfully. But, I can't set up permissions for postgres to access the file. Is there any way to set fsGroup? Also, is there any way to set init containers?

[alex-arica]

Thank you for your message.

Could you please share the contents of the Kubegres configurations resources that you have created (ConfigMap, YAML of kind Kubegres, ...) and also the error message that Postgres displays when trying to access to the file?

[thepostgresguy]

Here is the kubegres YAML `apiVersion: kubegres.reactive-tech.io/v1 kind: Kubegres metadata: name: wpg-kubegres namespace: watcher

spec: volume: volumes:

• name: certs secret: secretName: wpg-kubegress-secret volumeMounts:

• mountPath: /var/lib/postgresql/certs name: certs replicas: 3 image: postgres:14

  customConfig: wpg-conf database: size: 75Gi

  env:

  • name: POSTGRES_PASSWORD valueFrom: secretKeyRef: name: wpg-secret key: superUserPassword

  • name: POSTGRES_REPLICATION_PASSWORD valueFrom: secretKeyRef: name: wpg-secret key: replicationUserPassword`

[thepostgresguy]

Here is my conf postgresql-kubegres-config.zip

[alex-arica]

Thank you for those details.

And what is the error that you see in your Postgres Pod?

[thepostgresguy]

This is the error: 2021-10-19 12:57:12.921 GMT [1] FATAL: could not load server certificate file "/var/lib/postgresql/tls.crt": No such file or directory

[thepostgresguy]

The files are there, but belong to root.

[alex-arica]

Thank you for those details.

Have you tried using DefaultMode with the value 0664 ? That would give other users than root the permission to read.

volumes:
- name: certs
  secret:
    secretName: wpg-kubegress-secret
    defaultMode: 0664

[thepostgresguy]

I just did. Same error: `apiVersion: kubegres.reactive-tech.io/v1 kind: Kubegres metadata: name: wpg-kubegres namespace: watcher

spec: volume: volumes:

• name: certs secret: secretName: wpg-sdstage01-kcftech-com-tls defaultMode: 0644 volumeMounts:

• mountPath: /var/lib/postgresql/certs name: certs replicas: 3 image: postgres:14

  customConfig: wpg-conf database: size: 75Gi

  env:

  • name: POSTGRES_PASSWORD valueFrom: secretKeyRef: name: wpg-secret key: superUserPassword

  • name: POSTGRES_REPLICATION_PASSWORD valueFrom: secretKeyRef: name: wpg-secret key: replicationUserPassword`

[thepostgresguy]

postgresql-kubegres.zip Github is messing with my format; here is the file.

[alex-arica]

Thank you for testing it.

Could you please share the output of ls -al in the folder /var/lib/postgresql/certs ?

[thepostgresguy]

Here is hte output: root@wpg-kubegres-1-0:/# ls -al /var/lib/postgresql/certs/ total 0 drwxrwxrwt 3 root root 120 Oct 19 13:53 . drwxr-xr-x 1 postgres postgres 19 Oct 19 13:53 .. drwxr-xr-x 2 root root 80 Oct 19 13:53 ..2021_10_19_13_53_05.452504246 lrwxrwxrwx 1 root root 31 Oct 19 13:53 ..data -> ..2021_10_19_13_53_05.452504246 lrwxrwxrwx 1 root root 14 Oct 19 13:53 tls.crt -> ..data/tls.crt lrwxrwxrwx 1 root root 14 Oct 19 13:53 tls.key -> ..data/tls.key root@wpg-kubegres-1-0:/#

[thepostgresguy]

The image is more clear.

[alex-arica]

Thank you.

The file tls.crt is a symlink pointing to a file in the folder data.

Could you please do a ls -a on the file located in the data folder?

[thepostgresguy]

[thepostgresguy]

Complete snapshot:

[alex-arica]

This is interesting. The folder and file have the correct chmod for other users. Am I missing something?

Have you tried to run the same ls -a as a "postgres" user?

[thepostgresguy]

Very curious: btw, for me to have the pods running, I had to turn off SSL. Still, the certs are mounted.

[alex-arica]

Could you please try? echo $PGDATA

[thepostgresguy]

here is the output.

[alex-arica]

Could you please move the certificate file to that folder?

[thepostgresguy]

postgresql-kubegres.zip Here is the output after applying the YAML file.

[alex-arica]

Sorry I meant copying the certificate file in the root of $PGDATA folder.

[thepostgresguy]

postgresql-kubegres.zip Same error.

[alex-arica]

If we forget about Kubegres, have you tested your certificate with Postgres only and did it work?

[thepostgresguy]

PostgreSQL won't start; the pods get stuck in CrashLoopBackOff.

[alex-arica]

Looking to the files that you sent me and the screenshots showing the chmod of the files and folders, from my point of view PostgreSql should be able to read the files.

What I suggest is you make the certificate work within a PostgreSql Pod using your access using "-exec -it ... --- bash " command. From there you can make your changes and restart the server independently.

If there is no evidence that the certificate works within a PostgreSql instance (let's forget about Kubegres) then I suggest going to PostgreSql documentation about how to make a certificate work.

There is no evidence that this issue is related to a lack of feature from Kubegres.

[thepostgresguy]

Ok, understood. Thank you!

[alex-arica]

Np.

[alex-arica]

In your initial message you suggested adding fsGroup option in the YAML. However, considering that the read/write chmod allow other users to read, then the Postgres user should be able to read the certificate.

I don't think we should close this ticket. What I said is, you need to know exactly what is required to make a certificate work in a Pod with the Docker official image of Postgres. Once your certificate works in that context, then we will know what is needed in Kubegres. Perhaps it would be a new feature. I do not know at this stage.

[thepostgresguy]

Thank you @alex-arica, I think it would be beneficial to allow the operator to set custom fsGroup. I am busy with other issues at work, but by Friday I will try to do more on the PostgreSQL pods and test certs.

[alex-arica]

Thank you for your help. That would be great. This current issue could be useful if you identify missing features in Kubegres which are required to add a certificate.

And fsGroup will be added as part of the issue #52 where it will be possible to add the field "securityContext" in the YAML, as follows:

apiVersion: kubegres.reactive-tech.io/v1
kind: Kubegres
metadata:
  name: mypostgres
  namespace: default

spec:
...
securityContext:
    runAsUser: 1000
    runAsGroup: 3000
    fsGroup: 2000
...

[thepostgresguy]

That would be awesome! ty!

[thepostgresguy]

Sorry for the delay. I tested defaultMode 644 and got the following error. `ubuntu@ip-10-0-1-219:~$ kubectl -n pgvirtuoso logs -f -l app=ivdb

PostgreSQL Database directory appears to contain a database; Skipping initialization

2021-10-31 00:02:25.250 GMT [1] FATAL: private key file "/var/lib/postgresql/data/certs/tls.key" has group or world access 2021-10-31 00:02:25.250 GMT [1] DETAIL: File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root. 2021-10-31 00:02:25.250 GMT [1] LOG: database system is shut down I changed to 640 and got this error: ubuntu@ip-10-0-1-219:~$ kubectl -n pgvirtuoso logs -f -l app=ivdb

PostgreSQL Database directory appears to contain a database; Skipping initialization

2021-10-31 00:06:24.820 GMT [1] FATAL: could not load server certificate file "/var/lib/postgresql/data/certs/tls.crt": Permission denied 2021-10-31 00:06:24.820 GMT [1] LOG: database system is shut down ` Is there an option to deploy init container?

[alex-arica]

There is no option to deploy init container. I am happy to review a PR if a developer from the community would like to add this feature.

I released a "beta" version in the main branch which allows to specify the field "securityContext" . To install it and test it please run:

kubectl apply -f  https://raw.githubusercontent.com/reactive-tech/kubegres/main/kubegres.yaml

I tested it locally with the following config in the YAML:

apiVersion: kubegres.reactive-tech.io/v1
kind: Kubegres
metadata:
  name: mypostgres
  namespace: default
spec:
...
 securityContext:
   runAsNonRoot: true
   runAsUser: 999
...

Under securityContext, you can specify fsGroup as well.

[alex-arica]

What would you use as a code/script if it was possible to have a deploy init container?

[thepostgresguy]

I was thinking of a bash script to change file ownership and properties. AKA, chmod and chown.

[thepostgresguy]

BTW, it worked!!!! Thank you! SSL is on. (I used cert-manager with let's encrypt certificate and connected with full verification)

[alex-arica]

Nice one!

Thank you for sharing all the details which allowed you to get there.

I am closing this issue.

[alex-arica]

Note that Kubegres version 1.13 is available with the support for the new field securityContext.

Please see the release page: https://github.com/reactive-tech/kubegres/releases/tag/v1.13

I updated the documentation by adding details about the new field 'securityContext': https://www.kubegres.io/doc/properties-explained.html

To install Kubegres 1.13, please run:

kubectl apply -f https://raw.githubusercontent.com/reactive-tech/kubegres/v1.13/kubegres.yaml

[thepostgresguy]

Thank you!!!!

[movitto]

To get this working locally we had to specify the following in our kubegres config;

apiVersion: kubegres.reactive-tech.io/v1
kind: Kubegres
metadata:
  name: mypostgres
  namespace: default
spec:
   replicas: 3
   image: postgres:14.1
   database:
      size: 10Gi
   customConfig: mypostgres-conf
   securityContext:
     fsGroup: 999
   volume:
     volumeMounts:
       - mountPath: "/mypostgres/.postgresql"
         name: mypostgres-server-tls
         readOnly: true
     volumes:
       - name: mypostgres-server-tls
         secret:
           secretName: mypostgres-server-tls
           defaultMode: 0400
   env:
     - name: POSTGRES_PASSWORD
       valueFrom:
         secretKeyRef:
           name: mypostgres-secret
           key: superUserPassword
     - name: POSTGRES_REPLICATION_PASSWORD
       valueFrom:
         secretKeyRef:
           name: mypostgres-secret
           key: replicationUserPassword

Of particular importance is that only fsGroup was specified to the securityContext as specifying runAsNonRoot / runAsUser resulted in the following error:

mkdir: cannot create directory ‘/var/lib/postgresql/data/pgdata’: Permission denied

Also note defaultMode is specified as volume parameter, omitting this results in:

2022-01-06 21:15:59.609 GMT [1] FATAL:  private key file "/mypostgres/.postgresql/tls.key" has group or world access
2022-01-06 21:15:59.609 GMT [1] DETAIL:  File must have permissions u=rw (0600) or less if owned by the database user, or permissions u=rw,g=r (0640) or less if owned by root.

Finally if defaultMode is specified but fsGroup is not specified, you will get the following error:

2022-01-06 21:16:47.837 GMT [1] FATAL:  could not load server certificate file "/mypostgres/.postgresql/tls.crt": Permission denied

Only through the combination specified above were we able to get it working. We will elaborate more momentarily in another issue (feature request to add ssl option)

[alex-arica]

@movitto thank you for sharing this! It will be helpful for the community.