これは、なにをしたくて書いたもの?
セキュアなクラスターとして構築した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前提にはなっていません。
なんですが、ドキュメントの方をよく見ると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設定に関するドキュメントはこちら。
では、このあたりの情報を元に、簡単なプログラムを作成して確認してみます。
準備
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
にするのは同義なようですが…。
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"
これは、SSL/TLSによる暗号化は使用するものの、証明書やホスト名の検証は行わないことを意味します。
ただ、これだと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証明書とユーザー名、パスワードでセッショントークンを取得)
なので、こういう感じなのかもしれませんね。
これで、その他の言語から接続する際にもそう戸惑うことはなくなるかな、と思います。