Can't connect to a postgres DB using encryption

[OGKevin]

Support guidelines

• [X] I've read the support guidelines

I've found an issue and checked that ...

• [X] ... the documentation does not mention anything about my problem
• [X] ... there are no open or closed issues that are related to my problem

Description

When you use a postgres database with SSL encryption, the container is not appropriately configured to be able to connect to such database.

The library that is used in the application expects /root/.postgres to be accessible for it to read the certs.

psycopg2.OperationalError: connection to server at "main.psql-operator.svc.cluster.local" (10.107.254.50), port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied
connection to server at "main.psql-operator.svc.cluster.local" (10.107.254.50), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "10.244.1.193", user "dsmr-reader.dsmr-reader", database "dsmr_reader", no encryption

However, client certs is not mandatory to be available unless you configure the database to validate client certificates. Due to the permission denied error, the application than initializes a connection without encryption which than gets rejected by the database server.

This is due to the application running as user 803 in the container, but I'm not sure why the application is looking in root's home dir.

Setting DIUD and DGID to 0, solves the issue, but this makes the container run as root, which is not what you want.

Expected behaviour

Connecting to a SSL enabled database, works as user 803.

Actual behaviour

Application is not able to connect to database, due to configuration issue.

User 803 does not have access to /root/.postgres.

I'm not sure however, why user 503 is not using $HOME/.postgres.

Steps to reproduce

1. postgres database with SSL enabled
2. Connect to this database with DJANGO_DATABASE_OPTIONS: "{sslmode: 'required'}"
3. Application fails to stratup due to file system permission issue.

Docker info

-

not using docker

Version

5.10.3-2023.03.02

Docker compose

-
Not using docker compose

Container logs

psycopg2.OperationalError: connection to server at "main.psql-operator.svc.cluster.local" (10.107.254.50), port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied
connection to server at "main.psql-operator.svc.cluster.local" (10.107.254.50), port 5432 failed: FATAL:  pg_hba.conf rejects connection for host "10.244.1.193", user "dsmr-reader.dsmr-reader", database "dsmr_reader", no encryption

Additional info

No response

[github-actions[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.