search

embulk / embulk-input-jdbc

MySQL, PostgreSQL, Redshift and generic JDBC input plugins for Embulk
Other
102 stars 74 forks source link

Update postgresql JDBC driver version for supporting scram-sha-256 #237

Closed mnmandahalf closed 2 years ago

mnmandahalf commented 2 years ago

This PR supports scram-sha-256 password encryption in embulk-input-postgresql plugin by default.

When I get to setup embulk-input-postgresql plugin for PostgreSQL 14.4, I get an "authentication method 10 not supported" error.

It seems that scram-sha-256 password encryption needs JDBC >= 42.2.0. https://www.postgresql.org/about/news/jdbc-4220-released-1825/

Here is an example of a testing with PostgreSQL 14.4 by locally built gem. checker-qual-3.5.0.jar is a runtime dependency of the jdbc.

$ embulk preview test.liquid.yml
2022-07-31 23:44:48.872 +0900: Embulk v0.9.24
2022-07-31 23:44:49.754 +0900 [WARN] (main): DEPRECATION: JRuby org.jruby.embed.ScriptingContainer is directly injected.
2022-07-31 23:44:51.851 +0900 [INFO] (main): Gem's home and path are set by default: "/Users/manami/.embulk/lib/gems"
2022-07-31 23:44:52.388 +0900 [INFO] (main): Started Embulk v0.9.24
2022-07-31 23:44:52.499 +0900 [INFO] (0001:preview): Loaded plugin embulk-input-postgresql (0.13.0)
2022-07-31 23:44:52.545 +0900 [WARN] (0001:preview): "UTC" is recognized as "Z" to be compatible with the legacy style.
2022-07-31 23:44:52.553 +0900 [INFO] (0001:preview): The Pg JDBC driver for the class "org.postgresql.Driver" is expected to be found in "default_jdbc_driver" at /Users/manami/.embulk/lib/gems/gems/embulk-input-postgresql-0.13.0-java/default_jdbc_driver/checker-qual-3.5.0.jar.
2022-07-31 23:44:52.554 +0900 [INFO] (0001:preview): The Pg JDBC driver for the class "org.postgresql.Driver" is expected to be found in "default_jdbc_driver" at /Users/manami/.embulk/lib/gems/gems/embulk-input-postgresql-0.13.0-java/default_jdbc_driver/postgresql-42.4.0.jar.
2022-07-31 23:44:52.559 +0900 [INFO] (0001:preview): Connecting to jdbc:postgresql://127.0.0.1:5432/mydatabase options {ApplicationName=embulk-input-postgresql, user=admin, password=***, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2022-07-31 23:44:52.720 +0900 [INFO] (0001:preview): SQL: SET search_path TO "public"
2022-07-31 23:44:52.725 +0900 [INFO] (0001:preview): Using JDBC Driver 42.4.0
2022-07-31 23:44:52.786 +0900 [WARN] (0001:preview): Z is deprecated as a military time zone name. Use UTC instead.
2022-07-31 23:44:52.786 +0900 [WARN] (0001:preview): "Z" is recognized as "Z" to be compatible with the legacy style.
2022-07-31 23:44:52.819 +0900 [INFO] (0001:preview): Connecting to jdbc:postgresql://127.0.0.1:5432/mydatabase options {ApplicationName=embulk-input-postgresql, user=admin, password=***, tcpKeepAlive=true, loginTimeout=300, socketTimeout=1800}
2022-07-31 23:44:52.840 +0900 [INFO] (0001:preview): SQL: SET search_path TO "public"
2022-07-31 23:44:52.844 +0900 [INFO] (0001:preview): SQL: DECLARE cur NO SCROLL CURSOR FOR SELECT * FROM "mytable"
2022-07-31 23:44:52.847 +0900 [INFO] (0001:preview): SQL: FETCH FORWARD 10000 FROM cur
2022-07-31 23:44:52.851 +0900 [INFO] (0001:preview): > 0.00 seconds
2022-07-31 23:44:52.853 +0900 [INFO] (0001:preview): SQL: FETCH FORWARD 10000 FROM cur
2022-07-31 23:44:52.855 +0900 [INFO] (0001:preview): > 0.00 seconds
+---------+-------------+--------------------------------+
| id:long | name:string |           updated_at:timestamp |
+---------+-------------+--------------------------------+
|       1 |      myname | 2022-07-31 05:38:27.367185 UTC |
+---------+-------------+--------------------------------+
dmikurube commented 2 years ago

We'll eventually want to update JDBC drivers, but actually, JDBC drivers often have silent incompatibility between versions. We have hesitated to update the "default" JDBC driver there, then. (E.g. default options, ...)

Instead, you should be able to switch the JDBC driver version with the driver_path option by yourself, without rebuilding the plugin. Please try that for a while.

mnmandahalf commented 2 years ago

@dmikurube Thank you. I had to be more careful about updating default driver. I'll try driver_path option to solve the issue.

Thank you for helping me. I close this PR.

y-ken commented 1 year ago

By adding these options, it works.

  driver_path: /opt/embulk/postgresql-42.6.0.jar
  options:
    sslmode: require

ref https://qiita.com/y-ken/items/07bb129d75ae24d0031f