[bitnami/moodle] Cannot connect to Postgresql with SSL/TLS connection when moodle database on RDS is setup with SSL/TLS.

[sanlinnaing]

Name and Version

bitnami/moodle:4.4

What architecture are you using?

amd64

What steps will reproduce the bug?

1. Environment

• docker hosted on Ubuntu 22.04
• docker compose
• Database : Postgresql hosted on AWS RDS with SSL/TLS=on
• REF setup : https://github.com/bitnami/containers/blob/main/bitnami/moodle/README.md

When setting up with docker compose by using docker.io/bitnami/moodle:4.4 images, I have used Postgresql database hosted on AWS RDS.
AWS RDS config on for SSL/TLS.
I can connect postgresql databse from docker host using psql . There is no problem.
But when I run the docker compose and check logs of moodle and a request to exposed port, I found following error.
pg_connect(): Unable to connect to PostgreSQL server: connection to server at ______failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied

It seem that apache user (daemon or www-data) have not right permission to /root/.postgresql/postgresql.crt.

What is the expected behavior?

It should be able to connect to Postgresql just setting correct database endpoint on MOODLE_DATABASE_HOST.

What do you see instead?

I found following error logs of moodle container when moodle initial setup from web.

Additional information

Workaround
I found a way to overcome the /root/.postgresql/postgresql.crt access permission problem with following way.
Set environment variable PGSSLCERT: /tmp/postgresql.crt to overwrite the default postgresql.crt location.
reference : postgresql docs
With this envrionment variable, there is no problem and I can successfully setup the moodle.

[gongomgra]

Hi @sanlinnaing,

Thanks for using Bitnami and sorry for the delay. I'm glad you found a solution for your issue by setting the PGSSLCERT variable. Can you give us more information about it? I see in the docs that the default place where PostgreSQL looks for the certificate is at ~/.postgresql/postgresql.crt (i.e. the HOME folder) Were you mounting the SSL certificate to connect with PostgreSQL at /root/.postgresql/postgresql.crt at the beginning? If so, can you tell us why did you choose that directory under the /root folder? Did you set any other parameter in Moodle configuration in order to use an SSL certificate for database connections?

I think we can improve our docs on how to define the SSL certificate in this case by documenting the PGSSLCERT environment variable you mentioned, but I'd like to better understand the issue and the solution first.

[sanlinnaing]

Hi @gongomgra

Sorry for delay of response and thanks for your contact.
I would like to explain my understanding on the issue.
It seem that Moodle used PHP lib pg_connect and want to access the certificate location defined on PGSSLCERT or sslcert param (that path may be used to create or read certificate). If PGSSLCERT or sslcert param is not provided, pg_connect used ~/.postgresql/postgresql.crt (i.e. the home folder).
Actually we do not need a certificate to communicate a database with SSL/TLS connection as a client for my usecase and I do not have a client certificate.
So, I did not need to mount /root/.postgresql/postgresql.crt at the beginning.

I did not set any parameter in the Moodle configuration.

I just added PGSSLCERT env to tell pg_connect the certificate location as /tmp/postgresql.crt that will not have any access restriction.

I will share yml that I used with docker compose.
docker-compose.yml (I used nginx-proxy with letsencrypt and it can be skip if it is not need in your env)

version: '3'

services:  
  nginx-proxy:
    image: jwilder/nginx-proxy:alpine
    container_name: nginx-proxy
    restart: always
    labels:
      com.github.jrcs.letsencrypt_nginx_proxy_companion.nginx_proxy: 'true'
    ports:
      - 80:80
      - 443:443
    volumes:
      - ./nginx/data/certs:/etc/nginx/certs:ro
      - ./nginx/data/conf.d:/etc/nginx/conf.d
      - ./nginx/data/vhost.d:/etc/nginx/vhost.d
      - ./nginx/data/html:/usr/share/nginx/html
      - /var/run/docker.sock:/tmp/docker.sock:ro
    networks:
      - webproxy

  letsencrypt:
    image: jrcs/letsencrypt-nginx-proxy-companion
    container_name: letsencrypt
    restart: always
    volumes:
      - ./nginx/data/vhost.d:/etc/nginx/vhost.d
      - ./nginx/data/certs:/etc/nginx/certs:rw
      - ./nginx/data/html:/usr/share/nginx/html
      - /var/run/docker.sock:/var/run/docker.sock:ro
    depends_on:
      - nginx-proxy
    networks:
      - webproxy

  moodle:
    image: docker.io/bitnami/moodle:4.4
    restart: always
    ports:
      - '8080:8080'
    environment:
      #BITNAMI_DEBUG: true
      MOODLE_DATABASE_TYPE: ${MOODLE_DATABASE_TYPE}
      MOODLE_DATABASE_HOST: ${MOODLE_DATABASE_HOST}
      MOODLE_DATABASE_PORT_NUMBER: ${MOODLE_DATABASE_PORT_NUMBER}
      MOODLE_DATABASE_NAME: ${MOODLE_DATABASE_NAME}
      MOODLE_DATABASE_USER: ${MOODLE_DATABASE_USER}
      MOODLE_DATABASE_PASSWORD: ${MOODLE_DATABASE_PASSWORD}
      MOODLE_USERNAME: ${MOODLE_USERNAME}
      MOODLE_PASSWORD: ${MOODLE_PASSWORD}
      MOODLE_SSLPROXY: yes
      VIRTUAL_HOST: ${VIRTUAL_HOST}
      VIRTUAL_PORT: ${VIRTUAL_PORT}
      LETSENCRYPT_HOST: ${LETSENCRYPT_HOST}
      LETSENCRYPT_EMAIL: ${LETSENCRYPT_EMAIL}
      # required PGSSLCERT to solve /root/.postgresql/postgresql.crt permission deny issue.
      PGSSLCERT: /tmp/postgresql.crt
    volumes:
      - moodle_data:/bitnami/moodle
      - moodledata_data:/bitnami/moodledata
    networks:
      - webproxy

volumes:
  moodle_data:
    driver: local
  moodledata_data:
    driver: local

networks:
  webproxy:

.env file to add moodle properties.

MOODLE_DATABASE_TYPE=pgsql
MOODLE_DATABASE_HOST=xxxx-stag-db.xxxxxxxx.us-east-2.rds.amazonaws.com
MOODLE_DATABASE_PORT_NUMBER=5432
MOODLE_DATABASE_NAME=moodle
MOODLE_DATABASE_USER=moodleuser
MOODLE_DATABASE_PASSWORD=xxxxxxxx
MOODLE_USERNAME=admin@example.com
MOODLE_PASSWORD=p@$$wd

VIRTUAL_HOST=md.example.com
VIRTUAL_PORT=8080
LETSENCRYPT_HOST=md.example.com
LETSENCRYPT_EMAIL=admin@example.com

AWS RDS PostgresSQL is used with SSL/TLS preferred setting.