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

SSL-support for connection to Postgres and MySQL Database

XMLWordPrintable

    • 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

      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

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

              Created:
              Updated:
              Resolved:

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

                  Error rendering 'clockify-timesheets-time-tracking-reports:timer-sidebar'. Please contact your Jira administrators.