Can't connect to MariaDB via JBDC from Google App Script with SSL

Question

I'm trying to connect to MariaDB with Google App Script.

I've been following this post, and these instructions and used this advice to get the certs setup.

const server = 'x.x.x.x'; //not my actual ip
const port = 3306;
const dbName = 'myDbName';
const username = 'googleusername';
const password = 'hunter2';
const url = 'jdbc:mysql://'+server+':'+port+'/'+dbName+'?useSSL=true';

const serverSslCertificate = '-----BEGIN CERTIFICATE-----\n'+
'zxcv1231223123'+
.... etc ....
'112223334'+'\n'+
'-----END CERTIFICATE-----';
const clientSslCertificate = '-----BEGIN CERTIFICATE-----\n
'+    'zxcv1231223123'+
.... etc ....
'112223334'+'\n'+
'-----END CERTIFICATE-----'; 
const clientSslKey ='-----BEGIN RSA PRIVATE KEY-----\n
'+    'zxcv1231223123'+
.... etc ....
'112223334'+'\n'+
'-----END RSA PRIVATE KEY-----';

  var connParams = {
    user: username,
    password: password,
    _serverSslCertificate: serverSslCertificate,
    _clientSslCertificate: clientSslCertificate,
    _clientSslKey: clientSslKey,
  };

When I try to connect, it fails and in MariaDB I can see in the mysql error logs: 2022-10-18 23:01:50 18 [Warning] Access denied for user 'googleusername'@'y.y.y.y' (using password: YES)

And in Apps Script I see Exception: Failed to establish a database connection. Check connection string, username and password.

If I do:

MariaDB [bitnami_wordpress]> SHOW GLOBAL VARIABLES LIKE '%ssl%' \G
*************************** 1. row ***************************
Variable_name: have_openssl
        Value: YES
*************************** 2. row ***************************
Variable_name: have_ssl
        Value: DISABLED
*************************** 3. row ***************************
Variable_name: ssl_ca
        Value: /opt/bitnami/mariadb/certs/ca.pem
*************************** 4. row ***************************
Variable_name: ssl_capath
        Value: 
*************************** 5. row ***************************
Variable_name: ssl_cert
        Value: /opt/bitnami/mariadb/certs/server-cert.pem
*************************** 6. row ***************************
Variable_name: ssl_cipher
        Value: 
*************************** 7. row ***************************
Variable_name: ssl_crl
        Value: 
*************************** 8. row ***************************
Variable_name: ssl_crlpath
        Value: 
*************************** 9. row ***************************
Variable_name: ssl_key
Value: /opt/bitnami/mariadb/certs/server-key.pem

Found this in the MariaDB startup log:

2022-10-18 23:32:19 0 [Warning] Failed to setup SSL
2022-10-18 23:32:19 0 [Warning] SSL error: SSL_CTX_set_default_verify_paths failed
2022-10-18 23:32:19 0 [Warning] SSL error: error:02001002:system library:fopen:No such file or directory
2022-10-18 23:32:19 0 [Warning] SSL error: error:2006D080:BIO routines:BIO_new_file:no such file
2022-10-18 23:32:19 0 [Warning] SSL error: error:0B084002:x509 certificate routines:X509_load_cert_crl_file:system lib

I took a look at the have_ssl = disabled thing, and found this.

bitnami@ip-172-26-11-184:~/stack/mariadb/conf/certs$ ls -lah
total 40K
drwxr-xr-x 2 bitnami root 4.0K Oct  9 04:17 .
drwxrwxr-x 4 root    root 4.0K Oct 18 22:10 ..
-rw-r--r-- 1 bitnami root 1.7K Oct  9 04:16 ca-key.pem
-rw-r--r-- 1 bitnami root 1.4K Oct  9 04:17 ca.pem
-rw-r--r-- 1 bitnami root 1.3K Oct  9 04:17 client-cert.pem
-rw------- 1 bitnami root 1.7K Oct  9 04:17 client-key.pem
-rw-r--r-- 1 bitnami root 1.1K Oct  9 04:17 client-req.pem
-rw-r--r-- 1 bitnami root 1.3K Oct  9 04:17 server-cert.pem
-rw------- 1 bitnami root 1.7K Oct  9 04:17 server-key.pem
-rw-r--r-- 1 bitnami root 1.1K Oct  9 04:17 server-req.pem

bitnami@ip-172-26-11-184:~/stack/mariadb/conf$ ls -lah
total 24K
drwxrwxr-x  4 root    root 4.0K Oct 18 22:10 .
drwxr-xr-x 12 root    root 4.0K Apr 14  2022 ..
drwxrwxr-x  3 root    root 4.0K Sep  5 11:52 bitnami
drwxr-xr-x  2 bitnami root 4.0K Oct  9 04:17 certs
-rw-rw-r--  1 bitnami root 1.1K Oct 18 22:10 my.cnf
-rw-r--r--  1 root    root 1002 Oct  9 04:16 run.sh

Amongst other things in my my.cnf:

[mysqld]
skip_name_resolve
explicit_defaults_for_timestamp
basedir=/opt/bitnami/mariadb
port=3306
tmpdir=/opt/bitnami/mariadb/tmp
socket=/opt/bitnami/mariadb/tmp/mysql.sock
pid_file=/opt/bitnami/mariadb/tmp/mysqld.pid
max_allowed_packet=16M
bind_address=0.0.0.0
log_error=/opt/bitnami/mariadb/logs/mysqld.log
slow_query_log=0
slow_query_log_file=/opt/bitnami/mariadb/logs/mysqld.log
long_query_time=10.0
character_set_server=utf8
collation_server=utf8_general_ci
plugin_dir=/opt/bitnami/mariadb/lib/plugin
ssl_ca=/opt/bitnami/mariadb/certs/ca.pem
ssl_cert=/opt/bitnami/mariadb/certs/server-cert.pem
ssl_key=/opt/bitnami/mariadb/certs/server-key.pem
require_secure_transport=ON

Does anyone have any ideas of what to try next and why it's not working?

Answer 1

I got to the bottom of it.

Lessons learnt:

1. If

         MariaDB [bitnami_wordpress]> SHOW GLOBAL VARIABLES LIKE '%ssl%' \G

shows

        Variable_name: have_ssl
        Value: DISABLED

Then your server side SSL is not enabled.

2. Check the MySQL log on reload/restart

        [Warning] SSL error: error:02001002:system library:fopen:No such file or directory 2022-10-18 23:32:19 0

This kind of thing is a hint - in my case that I'd listed the paths wrong in the config file

3. The certificates that are needed on the client side are:

• The Certificate Authority (ca.pem)
• Client Cert
• Client Key

In my case, I had the server cert, rather than the CA certificate.

4. It may be worth trying to connect with the certificates from a commandline client, but whatever "--ssl-verify-server-cert " does, it isn't what App Script does when it tries to connect.

Thanks @danblack for the pointers and encouragement.