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

        1. 54704-mariadb-1 config.png
          426 kB
          Andrew Lyons
        2. 54704-mariadb-2 nossl - fails.png
          439 kB
          Andrew Lyons
        3. 54704-mariadb-3 require - works.png
          501 kB
          Andrew Lyons
        4. 54704-mariadb-4 verify-full custom CA with valid cert.png
          428 kB
          Andrew Lyons
        5. 54704-mariadb-5 verify-full custom CA with SAN.png
          424 kB
          Andrew Lyons
        6. 54704-mariadb-6 verify-full custom CA with name not on cert.png
          462 kB
          Andrew Lyons
        7. 54704-mariadb-7 verify-full custom CA using IP not in cert.png
          531 kB
          Andrew Lyons
        8. 54704-mariadb-8 verify-full adding IP to cert.png
          557 kB
          Andrew Lyons
        9. 54704-mariadb-9 verify-full custom CA using IP added to cert.png
          499 kB
          Andrew Lyons
        10. 54704-pg-10 verify-full updated cert with SAN moodle.png
          412 kB
          Andrew Lyons
        11. 54704-pg-1 postgresql.conf.png
          361 kB
          Andrew Lyons
        12. 54704-pg-2 secure default command.png
          443 kB
          Andrew Lyons
        13. 54704-pg-3 secure default log.png
          415 kB
          Andrew Lyons
        14. 54704-pg-4 verify-full mismatched hostname.png
          584 kB
          Andrew Lyons
        15. 54704-pg-5 verify-full missing root cert.png
          569 kB
          Andrew Lyons
        16. 54704-pg-6 verify-full custom CA root cert in place.png
          562 kB
          Andrew Lyons
        17. 54704-pg-7 verify-full hostname correct matches ca.png
          487 kB
          Andrew Lyons
        18. 54704-pg-8 verify-full mismatched hostname with ca.png
          571 kB
          Andrew Lyons
        19. 54704-pg-9 verify-full updated cert with SAN.png
          481 kB
          Andrew Lyons
        20. image-2023-06-27-10-02-52-866.png
          9 kB
          Melanie Treitinger

            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.