[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
[email protected]
MOODLE_PASSWORD=p@$$wd

VIRTUAL_HOST=md.example.com
VIRTUAL_PORT=8080
LETSENCRYPT_HOST=md.example.com
[email protected]

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

[gongomgra]

Hi @sanlinnaing,

Thanks for your message. Unfortunately, I don't fully understand if you need the SSL certificate to connect with the RDS database or not. Does the /tmp/postgresql.crt file exist on your container? According to the first screenshot, the message you received is just a warning. Did the Moodle app start correctly or did you need to configure the PGSSLCERT variable for it to start?

[raghumuppa]

@gongomgra

No, we don't need an SSL certificate to connect to RDS and /tmp/postgresql.crt doesn't exist in the container. To ignore the error, we just need to configure the PGSSLCERT to point to any dummy location.

[sanlinnaing]

@raghumuppa Thanks for your comments. Your mention is correct. @gongomgra We do not need a certificate to connect to RDS. In case RDS config perfer SSL/TLS connection, in client side even though a certificate is not required to connect to RDS, it is required to configure PGSSLCERT with /tmp/postgresql.crt or any other accessible location.

[gongomgra]

HI @sanlinnaing, @raghumuppa,

Thanks for your messages. We are working on a new release including PGSSLCERT definition into /tmp folder. We will keep you posted.

[gongomgra]

Hi @sanlinnaing, @raghumuppa,

We have just released a new revision of our Moodle 4.4 image (tag 4.4.0-debian-12-r5) including the changes mentioned in my previous message (see pr/67709). Can you confirm the Moodle app starts properly using that image and not defining the PGSSLCERT variable in your docker-compose?

[sanlinnaing]

@gongomgra

Thanks for your information. I have tested with 4.4.0-debian-12-r5 tag. It seem that it does not solve. Here is the part of the log:

moo-moodle-1  | [07-Jun-2024 14:02:14 UTC] PHP Warning:  pg_connect(): Unable to connect to PostgreSQL server: connection to server at "xxxx-xxxx-db.xxxxxxwspv.us-east-2.rds.amazonaws.com" (10.2.169.196), port 5432 failed: could not open certificate file "/root/.postgresql/postgresql.crt": Permission denied
moo-moodle-1  | connection to server at "xxxx-xxxx-db.xxxxxxwspv.us-east-2.rds.amazonaws.com" (10.2.169.196), port 5432 failed: FATAL:  no pg_hba.conf entry for host "10.1.38.40", user "moodleuser", database "moodle", no encryption in /bitnami/moodle/lib/dml/pgsql_native_moodle_database.php on line 214

If I check the image version with ps, it is docker.io/bitnami/moodle:4.4.0-debian-12-r5.

ubuntu@ip-10-1-38-40:~/moo$ docker compose ps
NAME           IMAGE                                         COMMAND                                                                          SERVICE       CREATED         STATUS         PORTS
moo-moodle-1   docker.io/bitnami/moodle:4.4.0-debian-12-r5   "/opt/bitnami/scripts/moodle/entrypoint.sh /opt/bitnami/scripts/moodle/run.sh"   moodle        2 minutes ago   Up 2 minutes   0.0.0.0:8080->8080/tcp, :::8080->8080/tcp, 8443/tcp
letsencrypt    jrcs/letsencrypt-nginx-proxy-companion        "/bin/bash /app/entrypoint.sh /bin/bash /app/start.sh"                           letsencrypt   2 minutes ago   Up 2 minutes   
nginx-proxy    jwilder/nginx-proxy:alpine                    "/app/docker-entrypoint.sh forego start -r"                                      nginx-proxy   2 minutes ago   Up 2 minutes   0.0.0.0:80->80/tcp, :::80->80/tcp, 0.0.0.0:443->443/tcp, :::443->443/tcp

Still same with /root/.postgresql/postgresql.crt instead of /tmp/postgresql.crt.

When I check the environment variables inside the container with printenv command, there is no PGSSLCERT variable found.

[gongomgra]

Hi @sanlinnaing,

Thanks for your message. We will check the new issue. It is expected that you don't see the PGSSLCERT variable from the docker exec prompt because the variables are internally defined for Moodle execution only.

[gongomgra]

Hi @sanlinnaing,

We have been checking this issue, but I have not being able to reproduce it. I rolled back to previous bitnami/moodle:4.4.0-debian-12-r4 image, and also configured the PostgreSQL server with SSL connections by default (but not mandatory to use a client certificate) by using the pg_hba.conf custom file below

hostssl     all             all             0.0.0.0/0               md5
hostssl     all             all             ::/0                    md5
host     all              all       0.0.0.0/0    md5
host     all              all       ::/0         md5
local    all             all                                     trust

The Moodle app started without issues, the database got populated, and the connections to the database use SSL by default:

$ docker-compose exec -it moodle bash
root@9935711ee347:/# psql -U postgres -h postgresql
Password for user postgres:
psql (13.15)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

postgres=# SELECT pid, usename, ssl FROM pg_stat_activity JOIN pg_stat_ssl USING(pid);
 pid | usename  | ssl
-----+----------+-----
 157 | postgres | t
 160 | postgres | t
 162 | postgres | t
 154 | postgres | t
(4 rows)

I can also force non-SSL connections without issues.

root@9935711ee347:/# psql -U postgres -h postgresql "sslmode=disable"
Password for user postgres:
psql (13.15)
Type "help" for help.

postgres=# SELECT pid, usename, ssl FROM pg_stat_activity JOIN pg_stat_ssl USING(pid);
 pid | usename  | ssl
-----+----------+-----
 157 | postgres | t
 160 | postgres | t
 168 | postgres | f
 165 | postgres | t
(4 rows)

Can you check your RDS configuration? According to our tests it looks like the RDS is forcing the SSL certificate to be provided or checked locally somehow, because we didn't get any of the PHP warnings nor errors you are getting on your side. Here is the docker-compose file I used for further reference

# Copyright Broadcom, Inc. All Rights Reserved.
# SPDX-License-Identifier: APACHE-2.0

version: '2'
services:
  postgresql:
    image: docker.io/bitnami/postgresql:13
    volumes:
      - 'postgresql_data:/bitnami/postgresql'
      - ./certs:/opt/certs
      - ./config:/bitnami/postgresql/conf
    environment:
      - POSTGRESQL_PASSWORD=bitnami123
      - POSTGRESQL_DATABASE=bitnami_moodle
      - POSTGRESQL_ENABLE_TLS=yes
      - POSTGRESQL_TLS_CERT_FILE=/opt/certs/server.crt
      - POSTGRESQL_TLS_KEY_FILE=/opt/certs/server.key
      - POSTGRESQL_TLS_CA_FILE=/opt/certs/root.crt
      - POSTGRESQL_PGHBA_FILE=pg_hba.conf
  moodle:
    image: bitnami/moodle:4.4.0-debian-12-r4
    ports:
      - '80:8080'
      - '443:8443'
    environment:
      - BITNAMI_DEBUG=true
      - MOODLE_DATABASE_TYPE=pgsql
      - MOODLE_DATABASE_HOST=postgresql
      - MOODLE_DATABASE_PORT_NUMBER=5432
      - MOODLE_DATABASE_USER=postgres
      - MOODLE_DATABASE_NAME=bitnami_moodle
      - MOODLE_DATABASE_PASSWORD=bitnami123
    volumes:
      - 'moodle_data:/bitnami/moodle'
      - 'moodledata_data:/bitnami/moodledata'
    depends_on:
      - postgresql
volumes:
  postgresql_data:
    driver: local
  moodle_data:
    driver: local
  moodledata_data:
    driver: local

[gongomgra]

@sanlinnaing we have reverted the environment variables in PostgreSQL client until we have more information on this and we can think on a proper solution.

https://github.com/bitnami/containers/pull/68042

[github-actions[bot]]

This Issue has been automatically marked as "stale" because it has not had recent activity (for 15 days). It will be closed if no further activity occurs. Thanks for the feedback.

[github-actions[bot]]

Due to the lack of activity in the last 5 days since it was marked as "stale", we proceed to close this Issue. Do not hesitate to reopen it later if necessary.