【trino】trino自签名生成证书

参考文章：
自建CA并生成自签名SSL证书
自签名证书
Keytool 工具的介绍与使用
openssl 命令介绍和使用案例
Java证书存储在哪里查看？

注意：subjectAltName=DNS:it.xiaomi.com, IP:192.168.100.100，必须包含it.xiaomi.com
否则报错：SSLPeerUnverifiedException: *** not verified的错误。

# 1. CA key
openssl genrsa -des3 -out selfca.key 2048
# Enter pass phrase for selfca.key:trino-ts

# Create ca root certificate...
openssl req -new -x509 -days 3650 -key selfca.key -subj "/C=CN/ST=Beijing/L=ChaoYang/O=xiaomi/OU=IT/CN=CA" -out selfca.crt
# Enter pass phrase(password) for selfca.key:trino-ts

# Create server key and certificate signing request..."
openssl genrsa -des3 -out selfca.key 2048
openssl req -newkey rsa:2048 -nodes -keyout server.key -subj "/C=CN/ST=Beijing/L=ChaoYang/O=xiaomi/OU=IT/CN=it.xiaomi.com" -out server.csr
openssl x509 -req -extfile <(printf "subjectAltName=DNS:it.xiaomi.com, IP:192.168.100.100") -days 3650 -in server.csr -CA selfca.crt -CAkey selfca.key -CAcreateserial -out server.crt

# generate pem file
touch server.pem
cat server.key >> server.pem && cat server.crt >> server.pem

# 验证证书
openssl x509 -in selfca.crt -subject -issuer -noout
# subject=C = CN, ST = Beijing, L = ChaoYang, O = xiaomi, OU = IT, CN = CA
# issuer=C = CN, ST = Beijing, L = ChaoYang, O = xiaomi, OU = IT, CN = CA

openssl x509 -in server.pem -subject -issuer -noout 
# subject=C = CN, ST = Beijing, L = ChaoYang, O = xiaomi, OU = IT, CN = it.xiaomi.com
# issuer=C = CN, ST = Beijing, L = ChaoYang, O = xiaomi, OU = IT, CN = CA


openssl verify -CAfile selfca.crt server.crt
# server.crt: OK

# show the subjectAltName that store in crt file
openssl x509 -in server.crt -ext subjectAltName -noout
# DNS:it.xiaomi.com, IP:192.168.100.100

将server.pem复制至trino/etc目录下。

测试jdbc连接：
使用kotlin语言。

import org.junit.jupiter.api.Test
import java.sql.DriverManager
import java.util.Properties

class TrinoJdbcTest {

    @Test
    fun fistHead() {
        println("hello")
    }

    @Test
    fun trinoJDBC() {
        val url = "jdbc:trino://it.xiaomi.com:443/hive"
        val props = Properties()
        props.put("user", "myuser")
        props.put("password", "123456")
        props.put("SSL", "true")
        // (1) no Verification
        // props.put("SSLVerification","NONE") // must set SSL as true!

        // (2) from Self Verificated CA
        // props.put("SSLTrustStorePath","D:\\projects\\trino-jdbc-test\\src\\test\\resources\\selfca.crt")

        // (3) from jvm CA,which is injected with Self Verificated CA
        // keytool -import -v -trustcacerts -alias trino-ts -file D:\projects\trino-jdbc-test\src\test\resources\selfca.crt -storepass changeit -keystore D:\xxx\jdk22.0.2\lib\security\cacerts
        // must provide the password for jvm and it defualt is "changeit"
        // props.put("SSLTrustStorePath","D:\\xxx\\jdk22.0.2\\lib\\security\\cacerts")
        // props.put("SSLTrustStorePassword","changeit")


        // optional client key store
        // openssl pkcs12 -export -out workspace-client.p12 -inkey workspace-client.key -in workspace-client.crt -passout pass:trino-client
        // props.put("SSLKeyStorePath","D:\\projects\\trino-jdbc-test\\src\\test\\resources\\workspace-client.pem") // failed
        // props.put("SSLKeyStorePath","D:\\projects\\trino-jdbc-test\\src\\test\\resources\\workspace-client.p12")
        // props.put("SSLKeyStorePassword","trino-client")
        // maybe use SSLUseSystemKeyStore
        // props.put("SSLUseSystemTrustStore","true")


        Class.forName("io.trino.jdbc.TrinoDriver")
        val conn = DriverManager.getConnection(url, props)
        val stmt = conn.createStatement()
        val query = stmt.executeQuery("show catalogs")
        while (query.next()) {
            val db = query.getString(1)
            println(db)
        }
    }
}