Add Flight JDBC Connection String example

[edmondop]

I have tried to connect using the JDBC driver added to my Intellij IDEA, with a different combinations of parameters. The driver failed with:

org.apache.arrow.driver.jdbc.shaded.org.flight.FlightRuntimeException: UNAVAILABLE io Exception

Any help would be highly appreciated

[kou]

Could you try jdbc:arrow-flight-sql://localhost:15432/?username=${USER_NAME}&password=${PASSWORD}&useEncryption=1?

@lidavidm Can we specify x-flight-sql-database header with the JDBC driver?

[lidavidm]

https://arrow.apache.org/docs/java/flight_sql_jdbc_driver.html#connecting-to-a-database

Any URI parameters that are not handled by the driver are passed to the Flight SQL service as gRPC headers. For example, the following URI

jdbc:arrow-flight-sql://localhost:12345/?useEncryption=0&database=mydb

This will connect without authentication or encryption, to a Flight SQL service running on localhost on port 12345. Each request will also include a database=mydb gRPC header.

So just appending &x-flight-sql-database=... should work

[edmondop]

I tried with the followng

jdbc:arrow-flight-sql://localhost:15432/?username=postgres&password=mypassword&useEncryption=0&database=mydb

It doesn't work, I am also connected with Postgres JDBC provider on port 5432 and that works ccrrectly. I tried also useEncryption=1. The following jdbc connection string works

jdbc:postgresql://localhost:5432/mydb?username=postgres&password=mypassword

[kou]

Could you try jdbc:arrow-flight-sql://localhost:15432/?username=postgres&password=mypassword&useEncryption=0&x-flight-sql-database=mydb?

[edmondop]

I figured it out reading carefully the logs that there was a previous problem that was hiding the real problem.

postgres-1  |
postgres-1  | 2024-09-01 21:32:09.143 UTC [1] LOG:  starting PostgreSQL 15.7 (Debian 15.7-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres-1  | 2024-09-01 21:32:09.145 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres-1  | 2024-09-01 21:32:09.145 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres-1  | 2024-09-01 21:32:09.146 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres-1  | 2024-09-01 21:32:09.153 UTC [76] LOG:  database system was shut down at 2024-09-01 21:32:08 UTC
postgres-1  | 2024-09-01 21:32:09.170 UTC [1] LOG:  database system is ready to accept connections
postgres-1  | 2024-09-01 21:32:09.210 UTC [81] LOG:  listening on grpc://127.0.0.1:15432 for Apache Arrow Flight SQL
postgres-1  | 2024-09-01 21:37:09.256 UTC [74] LOG:  checkpoint starting: time

I realized that the arrow-flight was only binding on 127.0.0.1 and my docker-compose was not mapping that, I discovered that via grpccurl. It might be a good idea to align the arrow-flight-sql binding behavior to the one of the "traditional" connector.

However, this didn't solve the problem, I created a small snippet

public final class ArrowExample {
    public static void main(String []args) throws Exception{
        DriverManager.registerDriver(new ArrowFlightJdbcDriver());
        Driver driver = DriverManager.getDriver("jdbc:arrow-flight://localhost:15432");
        Connection connection = driver.connect("jdbc:arrow-flight-sql://localhost:15432/?username=postgres&password=mypassword&useEncryption=0&database=ah_arrow", new Properties());
        Statement statement = connection.createStatement();
        ResultSet rs = statement.executeQuery("select * from public.people");
        System.out.println("Hello world");
    }
}

this fails like so:

java.sql.SQLException: Error while executing SQL "select * from public.people": Network closed for unknown reason

If I enable useEncryption=1, it fails during TSL negotiation.

[kou]

Could you share PostgreSQL log on the error?

[edmondop]

Is there a different log for arrow ? The console doesn't show anything

[kou]

arrow-flight-sql-postgresql uses the same log as PostgreSQL's one. Hmm. Could you share your public.people definition?

[lidavidm]

Isn't the URI in the code snippet still using &database= and not &x-flight-sql-database=?

[edmondop]

I have some additional interesting infos.

Connecting from within the container works:

telnet localhost 15432
Trying ::1...
Connection failed: Connection refused
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
???@Did not receive HTTP/2 settings before handshake timeoutConnection closed by foreign host.

Container from the outside container

 telnet 127.0.0.1 15432
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

[edmondop]

-flight-sql-database=?

I tried that, doesn't make a difference. It looks like a problem with networking. I need to figure out why

[kou]

Could you provide your docker-compose.yml?

[edmondop]

services:
  postgres:
    image: "ghcr.io/apache/arrow-flight-sql-postgresql:main-bookworm-15"
    platform: "linux/amd64"

    environment:
      POSTGRES_DB: ah_arrow
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: mypassword
    volumes:
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    ports:
      - "5432:5432"
      - "127.0.0.1:15432:15432"

[kou]

Thanks. Could you also provide init.sql?

[edmondop]

Sure, the table is minimal I was really only trying to get the connector to work

CREATE TABLE people (
    phone_number VARCHAR(255)
);

INSERT INTO people (phone_number) VALUES ('123-45-6789');
INSERT INTO people (phone_number) VALUES ('987-65-4321');
INSERT INTO people (phone_number) VALUES ('555-55-5555');

[kou]

Thanks. I found a problem in "ghcr.io/apache/arrow-flight-sql-postgresql:main-bookworm-15": https://github.com/apache/arrow-flight-sql-postgresql/issues/191

Could you try again after docker compose pull to use the latest image?

[edmondop]

Thank you, now I get a connection error

UNAUTHENTICATED: Invalid: SCRAM auth method isn't supported yet. Detail: Unauthenticated

if i use enableEncryption=0, if I use enableEncryption=1 instead I get an SSL Exception

[kou]

OK. Could you change the last host all all all scram-sha-256 line in /var/lib/postgresql/data/pg_hba.conf to host all all all plain? Apache Arrow Flight SQL adapter for PostgreSQL doesn't support scran-sha-256 yet because it's a challenge-response type authentication.

[edmondop]

Thanks, this is not something I can disable at client level right?

[kou]

Right. It's a server side configuration.

[edmondop]

Bumping this @kou , do you think we should change the docker image to automatically disable SCRAM auth?

[kou]

No. It seems that we can change it by the POSTGRES_HOST_AUTH_METHOD environment variable: https://hub.docker.com/_/postgres#postgres_host_auth_method

Could you try it?

[CincyBC]

Is there any intention to add md5 and scram-sha-256 compatibility for this extension in the near future? I'm getting a sense that FlightSQL hasn't really taken off yet (pun intended).

[kou]

It's difficult to support md5 and scram-sha-256 because they are challenge-response based authentications. There is no standard way to implement challenge-response based authentications in Flight SQL.

Could you use TLS + password for secure authentication?

[CincyBC]

Thank you. I thought that's how you'd respond based on your above responses, but I saw the // TODO comments in your code so I thought I'd ask. Generally speaking, over the Postgres wire is perfectly performant for my use cases, but I'm exploring FlightSQL for a future project and using it with Postgres is a good start. Thank you for creating this.

[kou]

Thanks.

I should release a new version that supports the latest Flight SQL specification and the latest PostgreSQL...