Open edmondop opened 1 week ago
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?
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
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
Could you try jdbc:arrow-flight-sql://localhost:15432/?username=postgres&password=mypassword&useEncryption=0&x-flight-sql-database=mydb
?
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.
Could you share PostgreSQL log on the error?
Is there a different log for arrow ? The console doesn't show anything
arrow-flight-sql-postgresql uses the same log as PostgreSQL's one.
Hmm. Could you share your public.people
definition?
Isn't the URI in the code snippet still using &database=
and not &x-flight-sql-database=
?
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.
-flight-sql-database=
?
I tried that, doesn't make a difference. It looks like a problem with networking. I need to figure out why
Could you provide your docker-compose.yml
?
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"
Thanks.
Could you also provide init.sql
?
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');
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?
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
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.
Thanks, this is not something I can disable at client level right?
Right. It's a server side configuration.
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