Auth error with JDBC source and postgresql

[Craig Hooper]

I've set up a Connect JDBC sink that writes to postgresql and it works fine. I'm trying to set up a source from the same database, different table, using exactly the same connection settings in the source properties file and I'm getting the error

connectorState: FAILED

trace: org.apache.kafka.connect.errors.ConnectException: org.postgresql.util.PSQLException: The server requested password-based authentication, but no password was provided.

at io.confluent.connect.jdbc.util.CachedConnectionProvider.getValidConnection(CachedConnectionProvider.java:59)

at io.confluent.connect.jdbc.JdbcSourceConnector.start(JdbcSourceConnector.java:77)

at org.apache.kafka.connect.runtime.WorkerConnector.doStart(WorkerConnector.java:100)

at org.apache.kafka.connect.runtime.WorkerConnector.start(WorkerConnector.java:125)

at org.apache.kafka.connect.runtime.WorkerConnector.transitionTo(WorkerConnector.java:182)

at org.apache.kafka.connect.runtime.Worker.startConnector(Worker.java:164)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.startConnector(DistributedHerder.java:773)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.startWork(DistributedHerder.java:747)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.handleRebalanceCompleted(DistributedHerder.java:708)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.tick(DistributedHerder.java:204)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.run(DistributedHerder.java:174)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.postgresql.util.PSQLException: The server requested password-based authentication, but no password was provided.

at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:473)

at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:203)

at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:65)

at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:149)

at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:35)

at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)

at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:47)

at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:30)

at org.postgresql.Driver.makeConnection(Driver.java:414)

at org.postgresql.Driver.connect(Driver.java:282)

at java.sql.DriverManager.getConnection(DriverManager.java:664)

at java.sql.DriverManager.getConnection(DriverManager.java:247)

at io.confluent.connect.jdbc.util.CachedConnectionProvider.newConnection(CachedConnectionProvider.java:66)

at io.confluent.connect.jdbc.util.CachedConnectionProvider.getValidConnection(CachedConnectionProvider.java:52)

... 11 more

The connection parts of my config look like 

connection.url=jdbc:postgresql://127.0.0.1/ebx

connection.user=ebx

connection.password=ebx

which were copied and pasted from the sink config, which again is working just fine.

I've looked at the source for JdbcSourceConnector and  I can't see any reason why it's not picking up the password.

I've also tried deleting the sink task and restarting Connect, in case there was some clash over the connection, but the results were exactly the same.

 

[Craig Hooper]

OK, so it looks like this commit isn't actually in CP 3.1.1 - https://github.com/confluentinc/kafka-connect-jdbc/commit/670135a665a90dbb4530818190b6884be50f737d, so source JDBC config is different to sink config, which takes user and password separately.

When I try putting the credentials in the URL I get an error saying that the connection.url wasn't supplied, even though I can see it returned when the task is started and via the config REST API. I even tried copying the connection.url line from the quickstart in case there was something wrong with my URL.

This is the error I get now:

connectorState: FAILED

trace: org.apache.kafka.connect.errors.ConnectException: Couldn't start JdbcSourceConnector due to configuration error

at io.confluent.connect.jdbc.JdbcSourceConnector.start(JdbcSourceConnector.java:70)

at org.apache.kafka.connect.runtime.WorkerConnector.doStart(WorkerConnector.java:100)

at org.apache.kafka.connect.runtime.WorkerConnector.start(WorkerConnector.java:125)

at org.apache.kafka.connect.runtime.WorkerConnector.transitionTo(WorkerConnector.java:182)

at org.apache.kafka.connect.runtime.Worker.startConnector(Worker.java:164)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.startConnector(DistributedHerder.java:773)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.startWork(DistributedHerder.java:747)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.handleRebalanceCompleted(DistributedHerder.java:708)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.tick(DistributedHerder.java:204)

at org.apache.kafka.connect.runtime.distributed.DistributedHerder.run(DistributedHerder.java:174)

at java.lang.Thread.run(Thread.java:745)

Caused by: org.apache.kafka.common.config.ConfigException: Missing required configuration "connection.url" which has no default value.

at org.apache.kafka.common.config.ConfigDef.parse(ConfigDef.java:421)

at org.apache.kafka.common.config.AbstractConfig.<init>(AbstractConfig.java:56)

at org.apache.kafka.common.config.AbstractConfig.<init>(AbstractConfig.java:63)

at io.confluent.connect.jdbc.source.JdbcSourceConnectorConfig.<init>(JdbcSourceConnectorConfig.java:207)

at io.confluent.connect.jdbc.JdbcSourceConnector.start(JdbcSourceConnector.java:68)

... 10 more

And here is my config

connection.url=jdbc:postgresql://localhost.com/ebx?user=ebx&password=ebx

name=postgresql-source-ebx

connector.class=io.confluent.connect.jdbc.JdbcSourceConnector

tasks.max=1

mode=timestamp+incrementing

incrementing.column.name=id

timestamp.column.name=dateModified

timestamp.delay.interval.ms=1000

query=SELECT * FROM application

topic.prefix=product-application

I must be blind, i can't see anything wrong.

[Craig Hooper]

So I can only assume that no-one else is using the JDBC source connector in CP 3.1.1.

Further testing has revealed that putting the credentials into properties instead of the URL, like how the sink properties work, gets all the way to connecting but then fails because there aren't any credentials (the CachedConnectionProvider doesn't pass them). The error is 

Caused by: org.postgresql.util.PSQLException: The server requested password-based authentication, but no password was provided.

But if you try to add them to the URL it looks like the config logic (in AbstractConfig maybe?) doesn't like the '?' or the '&'. So with a URL like 

connection.url=jdbc:postgresql://localhost.com/ebx?user=ebx&password=ebx

the error is 

Caused by: org.apache.kafka.common.config.ConfigException: Missing required configuration "connection.url" which has no default value.

at org.apache.kafka.common.config.ConfigDef.parse(ConfigDef.java:421)

at org.apache.kafka.common.config.AbstractConfig.<init>(AbstractConfig.java:56)

I'm pretty sure this is a bug, it's very simple and completely reproducible, should I raise an issue? If so, where?

On Wednesday, 21 December 2016 08:30:11 UTC+11, Craig Hooper wrote:

[Shikhar Bhushan]

Hi Craig,

Sorry you've had a rocky start. For the JDBC source connector, the `connection.user` and `connection.password` configs are only on the master branch and not in a 3.1.x release. That said, there was a missing spot where these configs needed to be used and weren't (fixed here).

Including the username and password as `connection.url` parameters should work though. I wonder if the problem you are running into is that you need to backslash escape the '=' characters. See http://docs.oracle.com/javase/7/docs/api/java/util/Properties.html#load(java.io.Reader)

You can report issues at https://github.com/confluentinc/kafka-connect-jdbc/issues.

Best,

Shikhar

--
You received this message because you are subscribed to the Google Groups "Confluent Platform" group.
To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platf...@googlegroups.com.
To post to this group, send email to confluent...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/confluent-platform/c1fd6514-43c9-4b37-b75c-87cc4cbbb074%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Craig Hooper]

Thanks Shikhar. I've gone about as far as I can without actually setting up a dev build to trace through it. The most helpful hint regarding escaping config I found said to do something like this:

        java.util.Properties propsTest = new java.util.Properties();

        propsTest.setProperty("connection.url", "jdbc:postgresql://localhost.com/ebx?user=ebx&password=ebx");

        try {

            propsTest.store(System.out, null);

        } catch (IOException e) {

            e.printStackTrace();

        }

which should print out a correctly escaped config.

If anyone is interested, what that gives is 

connection.url=jdbc\:postgresql\://localhost.com/ebx?user\=ebx&password\=ebx

which still gives the "required config missing" error. It also highlights that I should have been escaping the colons as well, but it seemed to work without that.

I've forked the code for kafka and the JDBC connector, hopefully I can find some time in the next week to work out how to run Connect in debug mode to see what's actually happening. I'm using IntelliJ, I'd appreciate any tips for setting it up to be able to debug the Connect CLI with a debug version of the Confluent JDBC source connector.

 

To unsubscribe from this group and stop receiving emails from it, send an email to confluent-platform+unsub...@googlegroups.com.

[rj.bh...@gmail.com]

Please provide connection url with schemaName first. Like : 

"jdbc:postgresql://localhost:5432/postgres?currentSchema=public&user=postgres&password=postgres"