GoogleAppScriptからMySQL接続(クライアント証明書認証)

GoogleAppScriptのAPIリファレンスをよく見ると、クライアント証明書認証につかうパラメータがある。
https://developers.google.com/apps-script/reference/jdbc/jdbc
せっかくMySQLサーバへのクライアント認証設定を入れたので、GoogleAppScriptから接続してみる。

GoogleAppScriptコード

// 各証明書・秘密鍵
// pemファイルの中身をほぼそのまま貼るが
// "----- BEGIN ... -----"と
// Base64エンコードされたデータと
// "---- END ... -----" の間だけ
// 改行文字"\n"を入れ、それ以外は改行を取り除いた文字列を準備する
//
var USER = "serverless_user";
var PASSWORD = "serverless_user_password";
var DATABASE_NAME = "serverless_db";
var SERVER_CERT = "-----BEGIN CERTIFICATE-----\nMII ... EI=\n-----END CERTIFICATE-----";
var CLIENT_CERT = "-----BEGIN CERTIFICATE-----\nMIIE .. c=\n-----END CERTIFICATE-----";
var CLIENT_KEY = "-----BEGIN RSA PRIVATE KEY-----\nMIIJ  ... 15\n-----END RSA PRIVATE KEY-----";

var HOST = "mysql.mydomain";
var PORT = 3306;


function myFunction() {
  var opts = {
    user: USER,
    password: PASSWORD,
    databaseName: DATABASE_NAME,
    _serverSslCertificate: SERVER_CERT,
    _clientSslCertificate: CLIENT_CERT,
    _clientSslKey: CLIENT_KEY
  };

  var conn = Jdbc.getConnection('jdbc:mysql://" + HOST + ":" + PORT + "/?useSSL=true', opts);

  if(!(conn)) {
    Logger.log("Failed");
    return;
  }
  
  var statement = conn.createStatement(Jdbc.ResultSet.TYPE_FORWARD_ONLY, Jdbc.ResultSet.CONCUR_READ_ONLY, Jdbc.ResultSet.HOLD_CURSORS_OVER_COMMIT);
  statement.setMaxRow(100);
  statement.setQueryTimeout(5);

  // Do Something like https://officeforest.org/wp/2019/01/20/google-apps-script%E3%81%A6%E3%82%99%E5%A4%96%E9%83%A8%E3%81%AEmysql%E3%83%86%E3%82%99%E3%83%BC%E3%82%BF%E3%83%98%E3%82%99%E3%83%BC%E3%82%B9%E3%81%AB%E6%8E%A5%E7%B6%9A%E3%81%99%E3%82%8B/

  var result  = statement.executeQuery("select * from user");
  var nColumns = result.getMetaData().getColumnCount();

  // Each Column name
  var columns = [];
  for(var i = 0; i < nColumns; ++i) {
    columns.push(result.getMetaData().getColumnName(i + 1));
  }
  Logger.log(JSON.stringify(columns));

  // Each Column type
  var types = [];
  for(var i = 0; i < nColumns; ++i) {
    types.push(result.getMetaData().getColumnClassName(i + 1));
  }
  Logger.log(JSON.stringify(types));

  var nRows = 0;
  var rows = [];
  while(result.next()) {
    var row = [];
    for(var i = 0; i < nColumns; ++i) {
      row.push(result.getString(i + 1));
    }
    rows.push(row);
  }
  Logger.log(JSON.stringify(rows));

  statement.close();
  conn.close();
}

最初、JDBC接続のURLに”?useSSL=true”を入れなかったのでサーバエラーとなってしまい失敗した。
stackoverflowに同じ問題について書かれているのをみつけ参考にした。

MySQLサーバ側Firewall設定

GoogleAppScriptは実行元IPが/32で固定できないものの、でもGoogleサービスからの通信以外はネットワーク層でブロックするよう、MySQLの稼働しているサーバのFirewallにルールを追加する。

GoogleAppScriptで外部通信を仕掛けた時の接続元IPのレンジは、こちらによると以下のアドレスのようだ。

  • 64.18.0.0/20
  • 64.233.160.0/19
  • 66.102.0.0/20
  • 66.249.80.0/20
  • 72.14.192.0/18
  • 74.125.0.0/16
  • 173.194.0.0/16
  • 207.126.144.0/20
  • 209.85.128.0/17
  • 216.239.32.0/19

なので、firewalldを使っている場合次のコマンドをやればいい。

firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="64.18.0.0/20" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="64.233.160.0/19" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="66.102.0.0/20" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="66.249.80.0/20" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="72.14.192.0/18" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="74.125.0.0/16" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="173.194.0.0/16" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="207.126.144.0/20" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="209.85.128.0/17" port port=3306 protocol="tcp" accept
firewall-cmd --zone=public --add-rich-rule=rule family="ipv4" source address="216.239.32.0/19" port port=3306 protocol="tcp" accept

firewall-cmd --runtime-to-permanent

RDBMSが使えれば、簡単なWebアプリケーションやAPIサーバを作ったり、DBをキューやジョブの状況保存に利用してGoogleAppScriptを本格的にワーカとして使ったりとGAS利用場面が広がる。
業務利用としては、Redmine等、アプリケーションのDBに直接アクセスして独自の集計ロジックで帳票出すとか。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です