CLOVER🍀

That was when it all began.

セキュアなクラスターとして構築したCockroachDBに、PostgreSQLのJDBCドライバーでアクセスする

これは、なにをしたくて書いたもの?

セキュアなクラスターとして構築したCockroachDBに、PostgreSQLのJDBCドライバーを使ってアクセスしてみたいと思いまして。

環境

今回の環境は、こちら。

$ cockroach --version
cockroach version details:
Build Tag:        v21.1.8
Build Time:       2021/08/30 13:41:17
Distribution:     CCL
Platform:         linux amd64 (x86_64-unknown-linux-gnu)
Go Version:       go1.15.14
C Compiler:       gcc 6.5.0
Build Commit ID:  57b7968be0e1c8e6cb1523ab01df886afef2a2ea
Build Type:       release
(use 'cockroach version --build-tag' to display only the build tag)

CockroachDBは5ノードでクラスターを作り、セキュアクラスターにしておきます。

ですが、ほとんど登場しませんけどね…。

Javaに関するものは、こちら。

$ java --version
openjdk 11.0.11 2021-04-20
OpenJDK Runtime Environment (build 11.0.11+9-Ubuntu-0ubuntu2.20.04)
OpenJDK 64-Bit Server VM (build 11.0.11+9-Ubuntu-0ubuntu2.20.04, mixed mode, sharing)


$ mvn --version
Apache Maven 3.8.2 (ea98e05a04480131370aa0c110b8c54cf726c06f)
Maven home: $HOME/.sdkman/candidates/maven/current
Java version: 11.0.11, vendor: Ubuntu, runtime: /usr/lib/jvm/java-11-openjdk-amd64
Default locale: ja_JP, platform encoding: UTF-8
OS name: "linux", version: "5.4.0-84-generic", arch: "amd64", family: "unix"

CockroachDBとJDBC

CockroachDBは、PostgreSQL互換のデータベースです。

どのバージョンと互換なのか?というと、現時点のCockroachDBはPostgreSQL 13と互換のようです(制限もあり)。

CockroachDB is wire-compatible with PostgreSQL 13 and works with majority of PostgreSQL database tools such as Dbeaver, Intellij, pgdump and so on.

PostgreSQL Compatibility | CockroachDB Docs

JDBCドライバーも、PostgreSQLのものを使います。

Install a Driver or ORM Framework / Java Drivers

Javaで接続する時のドキュメントは、こちらを参考に。

Build a Hello World App with CockroachDB and JDBC | CockroachDB Docs

Build a Simple CRUD Java App with CockroachDB and JDBC | CockroachDB Docs

なのですが、cloneを指示しているGitHubリポジトリにあるソースコードは、SSL/TLS前提にはなっていません。

https://github.com/cockroachlabs/hello-world-java-jdbc/blob/master/app/src/main/java/example/app/App.java

https://github.com/cockroachlabs/example-app-java-jdbc/blob/master/app/src/main/java/com/cockroachlabs/BasicExample.java

なんですが、ドキュメントの方をよく見るとSSL/TLSでの接続例になっています。

Build a Simple CRUD Java App with CockroachDB and JDBC / Step 2. Get the code

結論からいくと、セキュアなクラスターとして構築したCockroachDBクラスターにJDBC接続するには、以下の情報が
必要です。

  • cockroach cert create-nodeコマンドで作成したノード証明書
  • 接続するユーザー名とパスワード

PostgreSQLのJDBCドライバーの接続、SSL/TLS設定に関するドキュメントはこちら。

Connecting to the Database

Configuring the Client

では、このあたりの情報を元に、簡単なプログラムを作成して確認してみます。

準備

Maven依存関係など。

    <dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.23</version>
            <scope>runtime</scope>
        </dependency>

        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.8.0</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.assertj</groupId>
            <artifactId>assertj-core</artifactId>
            <version>3.20.2</version>
            <scope>test</scope>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <artifactId>maven-surefire-plugin</artifactId>
                <version>2.22.2</version>
            </plugin>
        </plugins>
    </build>

必要なのはPostgreSQLのJDBCドライバーで、その他はテストライブラリです。

CockroachDBには、以下の情報でアクセスします。

  • 接続先ホスト … localhost
  • ユーザー … myuser
  • パスワード … password
  • 接続先データベース … example

ノード証明書は、以下のパスに配置しているものとします。

src/test/resources/node.crt

CockroachDBのexampleデータベースには、以下の定義でテーブルを作成しているものとします。

create table book(
  isbn varchar(14) primary key,
  title text,
  price integer
);

テストコードの雛形

テストコードの雛形を作成しておきます。

src/test/java/org/littlewings/cockroachdb/jdbc/CockroachJdbcTest.java

package org.littlewings.cockroachdb.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.jupiter.api.Test;

import static org.assertj.core.api.Assertions.assertThat;
import static org.assertj.core.api.Assertions.assertThatThrownBy;

public class CockroachJdbcTest {

    // ここにテストコードを書く!
}

この中に、テストコードを埋めていきましょう。

とりあえず、接続してみる

まずは、CockroachDBに接続してみましょう。

    @Test
    public void connect() throws SQLException {
        String url = "jdbc:postgresql://localhost:26257/example?" +
                "sslrootcert=./src/test/resources/node.crt&" +
                "ssl=true&" +
                "sslmode=verify-full";
        String user = "myuser";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement ps = conn.prepareStatement("select 1");
             ResultSet rs = ps.executeQuery()) {
            rs.next();

            assertThat(rs.getInt(1)).isEqualTo(1);
        }
    }

SSL/TLSを有効にしたセキュアなCockroachDBクラスターに接続するためには、この設定が基本になるでしょうか?

sslをtrueにするのと、sslmodeをverify-fullにするのは同義なようですが…。

Configuring the Client

sslrootcertでは、ノード証明書のパスを指定します。

ユーザーの認証にはクライアント証明書を使うのかなとも思ったのですが、どうやらパスワードを使うようです。

Authentication / Client authentication

クライアント証明書は、CockroachDBのCLIが主要な使いどころなのかもしれません。

ここで、URLに指定するプロパティを変更してみます。

    @Test
    public void connectSimply() throws SQLException {
        String url = "jdbc:postgresql://localhost:26257/example";
        String user = "myuser";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement ps = conn.prepareStatement("select 1");
             ResultSet rs = ps.executeQuery()) {
            rs.next();

            assertThat(rs.getInt(1)).isEqualTo(1);
        }
    }

実は、ここまでシンプルな内容でも接続できたりします。

sslにはデフォルト値はないようです。sslmodeのデフォルト値はrequireです。

Parameter governing the use of SSL. The allowed values are disable, allow, prefer, require, verify-ca, verify-full. If ssl property is empty or set to true it implies verify-full. Default mode is "require"

Enum PGProperty / SSL_MODE

これは、SSL/TLSによる暗号化は使用するものの、証明書やホスト名の検証は行わないことを意味します。

Configuring the Client

ただ、これだとSSL/TLS通信が行えているのかちょっと不安になるので、明示的にSSL/TLSを無効に指定して
CockroachDBに接続してみます。

    @Test
    public void plainConnectFail() throws SQLException {
        String url = "jdbc:postgresql://localhost:26257/example?" +
                "ssl=false&" +
                "sslmode=disable";
        String user = "myuser";
        String password = "password";

        assertThatThrownBy(() -> DriverManager.getConnection(url, user, password))
                .isInstanceOf(SQLException.class)
                .hasMessage("ERROR: node is running secure mode, SSL connection required");
    }

こちらは失敗します。

これで、少なくともSSL/TLSが有効な状態でCockroachDBに接続していることは確認できたかな、と。

証明書やホスト名の検証を行うかは、設定次第ですね。

トランザクションを使ってみる

せっかくなので、トランザクションも使ってみましょう。

コミットするパターン。

    @Test
    public void transaction() throws SQLException {
        String url = "jdbc:postgresql://localhost:26257/example?" +
                "sslrootcert=./src/test/resources/node.crt&" +
                "ssl=true&" +
                "sslmode=verify-full";
        String user = "myuser";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            conn.setAutoCommit(false);

            PreparedStatement insertPs = conn.prepareStatement("insert into book(isbn, title, price) values(?, ?, ?)");

            insertPs.setString(1, "978-4798160436");
            insertPs.setString(2, "PostgreSQL徹底入門 第4版");
            insertPs.setInt(3, 3608);
            assertThat(insertPs.executeUpdate()).isEqualTo(1);
            insertPs.clearParameters();

            insertPs.setString(1, "978-4297100896");
            insertPs.setString(2, "[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則");
            insertPs.setInt(3, 3608);
            assertThat(insertPs.executeUpdate()).isEqualTo(1);
            insertPs.clearParameters();

            insertPs.setString(1, "978-1800567498");
            insertPs.setString(2, "Mastering PostgreSQL 13: Build, administer, and maintain database applications efficiently with PostgreSQL 13, 4th Edition");
            insertPs.setInt(3, 4840);
            assertThat(insertPs.executeUpdate()).isEqualTo(1);
            insertPs.clearParameters();

            insertPs.setString(1, "978-1838985288");
            insertPs.setString(2, "Learn PostgreSQL: Build and manage high-performance database solutions using PostgreSQL 12 and 13");
            insertPs.setInt(3, 5435);
            assertThat(insertPs.executeUpdate()).isEqualTo(1);
            insertPs.clearParameters();

            insertPs.close();

            conn.commit();
        }

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            conn.setAutoCommit(false);

            PreparedStatement countPs = conn.prepareStatement("select count(*) from book");
            ResultSet countRs = countPs.executeQuery();

            countRs.next();

            assertThat(countRs.getInt(1)).isEqualTo(4);

            countRs.close();
            countPs.close();

            PreparedStatement selectPs = conn.prepareStatement("select isbn, title, price from book where isbn = ?");
            selectPs.setString(1, "978-4798160436");
            ResultSet selectRs = selectPs.executeQuery();

            selectRs.next();

            assertThat(selectRs.getString("title")).isEqualTo("PostgreSQL徹底入門 第4版");
            assertThat(selectRs.getInt("price")).isEqualTo(3608);

            selectRs.close();
            selectPs.close();
        }

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement ps = conn.prepareStatement("truncate table book")) {
            ps.executeUpdate();
        }
    }

最後にtruncateしています。

ロールバックするパターン。

    @Test
    public void rollback() throws SQLException {
        String url = "jdbc:postgresql://localhost:26257/example?" +
                "sslrootcert=./src/test/resources/node.crt&" +
                "ssl=true&" +
                "sslmode=verify-full";
        String user = "myuser";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            conn.setAutoCommit(false);

            PreparedStatement insertPs = conn.prepareStatement("insert into book(isbn, title, price) values(?, ?, ?)");

            insertPs.setString(1, "978-4798160436");
            insertPs.setString(2, "PostgreSQL徹底入門 第4版");
            insertPs.setInt(3, 3608);
            assertThat(insertPs.executeUpdate()).isEqualTo(1);
            insertPs.clearParameters();

            insertPs.setString(1, "978-4297100896");
            insertPs.setString(2, "[改訂新版]内部構造から学ぶPostgreSQL 設計・運用計画の鉄則");
            insertPs.setInt(3, 3608);
            assertThat(insertPs.executeUpdate()).isEqualTo(1);
            insertPs.clearParameters();

            insertPs.close();

            conn.rollback();
        }

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            conn.setAutoCommit(false);

            PreparedStatement countPs = conn.prepareStatement("select count(*) from book");
            ResultSet countRs = countPs.executeQuery();

            countRs.next();

            assertThat(countRs.getInt(1)).isEqualTo(0);

            countRs.close();
            countPs.close();

            PreparedStatement selectPs = conn.prepareStatement("select isbn, title, price from book where isbn = ?");
            selectPs.setString(1, "978-4798160436");
            ResultSet selectRs = selectPs.executeQuery();

            assertThat(selectRs.next()).isFalse();

            selectRs.close();
            selectPs.close();
        }

        try (Connection conn = DriverManager.getConnection(url, user, password);
             PreparedStatement ps = conn.prepareStatement("truncate table book")) {
            ps.executeUpdate();
        }
    }

このあたりも、期待通りに動きます、と。

まとめ

CockroachDBに対して、PostgreSQLのJDBCドライバーを使ってSSL/TLS接続してみました。

実際に使うまでは、クライアント証明書が必要だったりいろいろ大変な感じなのかな?と思っていたのですが、意外と
そうでもありませんでした。

Cluster APIでCockroachDBにアクセスする場合も似た感じ(CA証明書とユーザー名、パスワードでセッショントークンを取得)
なので、こういう感じなのかもしれませんね。

これで、その他の言語から接続する際にもそう戸惑うことはなくなるかな、と思います。