Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-54704

SSL-support for connection to Postgres and MySQL Database

    XMLWordPrintable

Details

    • Any
    • MOODLE_30_STABLE
    • MOODLE_403_STABLE
    • MDL-54704-db-ssl
    • Easy
    • Hide

      Postgres

      Config postgresql.conf:

      ssl = on
      		 
      log_connections=on

      Check default connection is SSL

      1. Ensure config.php $CFG->dboptions does not have 'ssl' entry
      2. Request any page from the site
      3. Confirm that database log (usually /var/log/postgresql/postgresql-nn-main.log has a line similar to:
        connection authorized: user=moodle database=moodle SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256)

      Check verify-full

      1. Ensure Postgres does not have CA signed certificate, eg self-signed certigicate in postgresql.conf:
        ssl_cert_file='/etc/ssl/certs/ssl-cert-snakeoil.pem'
      2. Moodle config.php $CFG->dboptions: Set 'ssl' => 'verify-full'
      3. Request any page from the site
      4. Confirm that Error: Database connection failed is displayed

      MySQL (MariaDB)

      Config any mysql *.cnf:

      Create a self-signed certificate (https://mariadb.com/kb/en/certificate-creation-with-openssl/) and add to the config:

      [mysqld]
      		 
      ssl-ca = /etc/mysql/ssl/ca.pem
      		 
      ssl-cert = /etc/mysql/ssl/server-cert.pem
      		 
      ssl-key = /etc/mysql/ssl/server-key.pem
      		 
      require_secure_transport = on

      Check no ssl config

      1. Ensure config.php $CFG->dboptions does not have 'ssl' entry
      2. Request any page from the site
      3. Confirm that Error: Database connection failed is displayed

      Check required

      1. Moodle config.php $CFG->dboptions: Set 'ssl' => 'require'
      2. Moodle config.php $CFG->dbhost: Set to either FQDN from the self-signed certificate, or database server IP address
      3. Request any page from the site
      4. Confirm that Error: Database connection failed is not displayed{}

      Check verify-full{}

      1. Moodle config.php $CFG->dboptions: Set 'ssl' => 'verify-full'
      2. Request any page from the site
      3. Confirm that Error: Database connection failed is displayed

      Note: mysqli will do the server certificate validation if the dbhost is a name, and not do it if it is an IP address. That cannot be changed with any option.

      CA signed certificate

      If you also have a CA signed certificate, you can (for both databases)

      1.  Change config options (see corresponding Config sections above) to point to that certificate
      2. Request any page from the site
      3. Confirm that Error: Database connection failed is not displayed
      Show
      Postgres Config postgresql.conf : ssl = on log_connections=on Check default connection is SSL Ensure config.php $CFG->dboptions does not have 'ssl' entry Request any page from the site Confirm that database log (usually /var/log/postgresql/postgresql-nn-main.log has a line similar to: connection authorized: user=moodle database=moodle SSL enabled (protocol=TLSv1.3, cipher=TLS_AES_256_GCM_SHA384, bits=256) Check verify-full Ensure Postgres does not have CA signed certificate, eg self-signed certigicate in postgresql.conf : ssl_cert_file='/etc/ssl/certs/ssl-cert-snakeoil.pem' Moodle config.php $CFG->dboptions : Set 'ssl' => 'verify-full' Request any page from the site Confirm that Error: Database connection failed is displayed MySQL (MariaDB) Config any mysql *.cnf : Create a self-signed certificate ( https://mariadb.com/kb/en/certificate-creation-with-openssl/ ) and add to the config: [mysqld] ssl-ca = /etc/mysql/ssl/ca.pem ssl-cert = /etc/mysql/ssl/server-cert.pem ssl-key = /etc/mysql/ssl/server-key.pem require_secure_transport = on Check no ssl config Ensure config.php $CFG->dboptions does not have 'ssl' entry Request any page from the site Confirm that Error: Database connection failed is displayed Check required Moodle config.php $CFG->dboptions : Set 'ssl' => 'require' Moodle config.php $CFG->dbhost : Set to either FQDN from the self-signed certificate, or database server IP address Request any page from the site Confirm that Error: Database connection failed is not displayed{ } Check verify-full { } Moodle config.php $CFG->dboptions : Set 'ssl' => 'verify-full' Request any page from the site Confirm that Error: Database connection failed is displayed Note : mysqli will do the server certificate validation if the dbhost is a name, and not do it if it is an IP address. That cannot be changed with any option. CA signed certificate If you also have a CA signed certificate, you can (for both databases)   Change config options (see corresponding Config sections above) to point to that certificate Request any page from the site Confirm that Error: Database connection failed is not displayed

    Description

      Currently, moodle does not have SSL-support for the connection to a database for any of the supported DBMS.
      This might be unproblematic for moodle and database running on the same server.
      However, using separate servers, which is necessary for bigger moodle instances, allows potential man in the middle attacks.

      The added patch allows a connection to a postgres database using ssl. To test this the dboptions in the config.php has to extended by the sslmodeoption. For example:
      $CFG->dboptions = array(
      'dbpersist' => false,
      'dbsocket' => false,
      'dbport' => 5432,
      'sslmode' => 'require'
      );
      In this way the postgres connection can support any sslmode (see http://www.postgresql.org/docs/15/static/libpq-ssl.html).
      Additonally, no existing moodle instance is forced to you ssl.
      The sslmode dboption could be reused for other DBMS. There is an old issue for the same problem for MySQL: MDL-50780

      Attachments

        Issue Links

          has a non-specific relationship to

          Improvement - An enhancement to an existing Moodle feature. MDL-50780 MySQL SSL-based database connections require require updating the Moodle's core code

          • Minor - Minor loss of function where workaround is possible
          • Closed
          has been marked as being related by

          Improvement - An enhancement to an existing Moodle feature. MDL-69581 Azure hosted MariaDB and MySQL instances report the wrong version number

          • Major - Major loss of function, incorrect output
          • Closed

          Activity

            People

              Votes:
              49 Vote for this issue
              Watchers:
              66 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                9/Oct/23

                Time Tracking

                  Estimated:
                  Original Estimate - 0 minutes
                  0m
                  Remaining:
                  Remaining Estimate - 0 minutes
                  0m
                  Logged:
                  Time Spent - 1 day, 3 hours
                  1d 3h