Support SSL in MySQL backend?

[ZxMYS]

Hi! It seems like currently if I want to use MySQL as the backend there’s no way to enable SSL of db connections? (I just briefly skimmed the source code so I might be wrong).
Can we added optional MySQL SSL configs to connection config and support enabling SSL of MySQL in MLMD? Thanks!

[hughmiao]

hi @ZxMYS , mysql client ssl option is not supported yet. We will add the support.
In the current release, you can also setup a mlmd grpc server and use ssl connection from the grpc client, if it works for you.

[ZxMYS]

@hughmiao great! looking forward to it.
Thanks for the suggestion - our MySQL server requires SSL, so I guess even if I set grpc up, the grpc server would still need SSL options in its connection to MySQL.

[hughmiao]

True, the server does the same thing. Yes, we need to add the ssl options to mysql connection config.

[hughmiao]

this should be fixed on the head. it is also included in the 0.15.1 release. please feel free to reopen if there's other issues.

[ZxMYS]

@hughmiao thanks for the quick resolution! Appreciated.
One more request - can we also support configuring the MYSQL_OPT_SSL_MODE option (see https://dev.mysql.com/doc/refman/5.7/en/mysql-options.html)? We'd like to set it to SSL_MODE_VERIFY_IDENTITY.
A related example is that PyMySQL supports a check_hostname flag in its ssl config which does this. mysql-connector-python also has a ssl-mode option.

[hughmiao]

thanks @ZxMYS for the feedback. MYSQL_OPT_SSL_MODE looks to me is added in 5.7+.
Note we rely on libmysql client headers (mariadb-connector-c [1]) to build and test, which is a mariadb implementation on mysql 5.6, where that constant is not defined in the header [1]. On the other hand, MYSQL_OPT_SSL_VERIFY_SERVER_CERT may be possible to add, however it is deprecated in 5.7 and removed in 8. Not sure it works in your environment. Our test env is mariadb 10+ too. We may consider to support MYSQL_OPT_SSL_VERIFY_SERVER_CERT in the next release.

Also if this FR is a blocker for you and waiting for the next release is not ideal, consider to use mysql_options in a similar way as the commit does, and build the wheel from the modified source. If set MYSQL_OPT_SSL_VERIFY_SERVER_CERT works in your environment, please feel free to send a pull request too.

[1] https://github.com/MariaDB/mariadb-connector-c/blob/de04c2e01fef13cb4545b85f47fd90ec92f2c95e/include/mysql.h#L166
[2] https://dev.mysql.com/doc/refman/5.7/en/mysql-options.html

[andrijaperovic]

@ZxMYS @hughmiao can you give some suggestions around what a working client config for SSL might look like?
Have tried to modify the metadata-grpc service in kubeflow/pipelines to pass the following config in text protobuf format to /bin/metadata_store_server:

connection_config {
      mysql {
        host: "..."
        port: int
        database: "..."
        user: "..."
        password: "..."
        ssl_options {
          verify_server_cert: true
          capath: "/etc/ssl/certs/my-cert.pem"
        }
      }
    }

Observing the following exception without much details:
2021-10-11 18:04:01.536992: F ml_metadata/metadata_store/metadata_store_server_main.cc:226] Non-OK-status: status status: Internal: mysql_real_connect failed: errno: 0, error: MetadataStore cannot be created with the given connection config.
It is using gcr.io/tfx-oss-public/ml_metadata_store_server:1.0.0.