Django + psycopg2 OperationalError could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied

Question

I have a server with ubuntu 22 and a postgres database on digitalocean. I'm deploying a django application there using docker swarm and when I try to access the /admin page I get this error:

connection to server at "xxxx.db.ondigitalocean.com" (xx.xxx.xxx.xx), port 25060 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied

I will add that when I created a test script to connect to the database for tests on the server, everything works fine, and I can connect to the database from the local computer. The same project when I run locally connects to the database without any problems

DB settings

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "NAME": "dbname",
        "USER": "user",
        "PASSWORD": "pass",
        "HOST": "xxxx.db.ondigitalocean.com",
        "PORT": "25060",
    }
}

DOCKERFILE

FROM python:3.11

ARG GIT_ACCESS_TOKEN

RUN git config --global url."https://${GIT_ACCESS_TOKEN}@github.com".insteadOf "ssh://[email protected]"

WORKDIR /backend

ENV PYTHONDONTWRITEBYTECODE 1
ENV PYTHONUNBUFFERED 1

COPY requirements.txt .
COPY entrypoint.sh .
COPY entrypoint-celery.sh .

# ADD /backend /backend
COPY /backend /backend

RUN python -m pip install -r requirements.txt

RUN mkdir -p /backend/tmp
RUN chmod -R 777 /backend/tmp

RUN mkdir -p /orders/xmls
RUN chmod -R 777 /orders/xmls

Answer 1

Had the same error, found this thread https://github.com/psycopg/psycopg2/issues/1535

I ended up applying this workaround suggested here: https://github.com/nginx/unit/issues/834#issuecomment-1410297997

For your dockerfile just add following

ENV PGSSLCERT /tmp/postgresql.crt