JDBCPool (RETURNING won't work) v/s PgPool (ssl error) question

[Vaibhav Desai]

Hello, I am using Vert.x 4 with the new sql client to connect to RDS postgres. We have the rds.force_ssl enabled on AWS. 

With JDBCPool: Can connect to DB but "RETURNING" clause won't work
With PgPool: Cannot get DB connection due to SSL auth error

PoolOptions poolOptions = new PoolOptions().setMaxSize(maxPoolSize);

// with JDBCPool

// url format: jdbc:postgresql://host:port/database

JDBCConnectOptions connectOptions = new JDBCConnectOptions()

    .setJdbcUrl(url)
    .setUser(username)
    .setPassword(password)
    .setAutoGeneratedKeys(true);
Pool jdbcPool = JDBCPool.pool(vertx, connectOptions, poolOptions);

// with PgPool
final PgConnectOptions sqlConnectOptions = new PgConnectOptions()
    .setHost(host)
    .setPort(port)
    .setUser(username)
    .setPassword(password)
    .setDatabase(database)
    .setSslMode(SslMode.PREFER);
Pool pgPool = PgPool.pool(vertx, sqlConnectOptions, poolOptions);

How can I get the DB connection and RETURNING to work? Please help.

Thanks,

Vaibhav

[Vaibhav Desai]

When using JDCPool and RETURNING, this is what debugger shows:

[Vaibhav Desai]

I was able to get RETURNING to work with JDBCPool  as follows:

jdbcPool
    .preparedQuery("INSERT INTO test VALUES (?) RETURNING *")
    .execute(Tuple.wrap(Arrays.asList('John Doe')))
    .onSuccess(rows -> {
              if (rows.property(JDBCPool.GENERATED_KEYS) != null) {
                    final Row insertedRow = rowSet.property(JDBCPool.GENERATED_KEYS);
                    final JsonObject insertedRowJson = new JsonObject();
                    for (int i = 0; i < insertedRow.size(); i++) {
                            final String colName = insertedRow.getColumnName(i);
                            final Object colValue = insertedRow.getValue(colName);
                            insertedRowJson.put(colName, colValue);
                    }
                    System.out.println(insertedRowJson);
              }
    }

The open question still remains: Why can't I get a connection using PgPool without specifying ssl params when the same with JDBCPool works with: "jdbc:postgresql://host:port/database".

Can someone please help? Thanks.

[Thomas SEGISMONT]

Hi,

Can you share details about the error with PgClient?

Have you tried with sslmode set to allow ?

--
You received this message because you are subscribed to the Google Groups "vert.x" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vertx+un...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/1cc2ceeb-41e3-45b3-85a5-5e9ef1492c1bn%40googlegroups.com.

[Vaibhav Desai]

Hello,

This is the error:

{
    "message": "no pg_hba.conf entry for host "x.x.x.x", user "<username>", database "<dbname>", SSL off", 

    "severity": "FATAL", 

    "code": "28000",

    "file": "auth.c", 

    "line": "490",

    "routine": "ClientAuthentication" 

}

Yes, I tried with all values for sslmode:
for JDBCPool: it works for all values except when sslmode=disable (as expected)
for PgPool: it does not work no matter what sslmode is specified

Thanks,

Vaibhav

[Thomas SEGISMONT]

And have you tried with setSsl(true) ?

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/07af212c-5945-4027-9272-b9415b5595c7n%40googlegroups.com.

[Vaibhav Desai]

Yes Thomas. I have. No luck. Setting setSsl(true) is setting the sslmode to be verify-ca internally. I have tried setting setSslMode(...) explicitly as well.

[Thomas SEGISMONT]

Ok. Would you mind putting together code snippets and filing an issue?

Please add this happens with RDS and if you have a pointer to the RDS doc which shows how to force SSL.

Thank you

To view this discussion on the web, visit https://groups.google.com/d/msgid/vertx/769b915f-aa6a-4762-af8d-094b902b93e2n%40googlegroups.com.

[Vaibhav Desai]

For sure. Just attach it here?

[Vaibhav Desai]

Oh nevermind. Just read the message again. Will submit an issue. Thanks.