Обсуждение: Make java client lib accept same connection strings as psql
Hi PostgreSQL Hackers, I've run into something confusing. The psql command accepts connection strings of the form: postgresql://user1:pass1@localhost:5432/db1?sslmode=require But passing this string to the java client library (with a "jdbc:" prefix) fails. See the exception and stack trace below. According to the docs https://jdbc.postgresql.org/documentation/80/connect.html , the java client library accepts connection strings with this form: postgresql://localhost:5432/db1?user=user1&password=pass1&ssl=true How about making the Java client library accept the same connection strings as psql and other command-line tools? That would make PostgreSQL easier to use and increase its popularity. Sincerely, Michael Exception in thread "main" org.postgresql.util.PSQLException: The connection attempt failed. at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:292) at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:49) at org.postgresql.jdbc.PgConnection.<init>(PgConnection.java:211) at org.postgresql.Driver.makeConnection(Driver.java:458) at org.postgresql.Driver.connect(Driver.java:260) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677) at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228) at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:98) at org.postgresql.ds.common.BaseDataSource.getConnection(BaseDataSource.java:83) at com.leonhardllc.x.db.temp.TemporaryDatabase.createTempDatabase(TemporaryDatabase.java:39) at com.leonhardllc.x.db.generated.JOOQSourceGenerator.main(JOOQSourceGenerator.java:35) Caused by: java.net.UnknownHostException: user1:pass1@localhost at java.base/java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:220) at java.base/java.net.SocksSocketImpl.connect(SocksSocketImpl.java:403) at java.base/java.net.Socket.connect(Socket.java:591) at org.postgresql.core.PGStream.<init>(PGStream.java:75) at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:91) at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192) ... 10 more
On Fri, Feb 21, 2020 at 6:21 PM Michael Leonhard <michael@leonhardllc.com> wrote:
How about making the Java client library accept the same connection
strings as psql and other command-line tools? That would make
PostgreSQL easier to use and increase its popularity.
That falls outside the scope of this list/project. The separate pgJDBC project team would need to decide to take that up.
I also doubt both unsubstantiated claims you make - that it would make developers' lives materially easier and influence popularity measurably.
David J.
On Sat, Feb 22, 2020 at 4:05 AM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Fri, Feb 21, 2020 at 6:21 PM Michael Leonhard <michael@leonhardllc.com> wrote:How about making the Java client library accept the same connection
strings as psql and other command-line tools? [...]That falls outside the scope of this list/project. The separate pgJDBC project team would need to decide to take that up.
Michael,
While your proposed end goal sounds as a desirable thing to me, there are certain obstacles to that, unfortunately.
First, consider that the URL support appeared in libpq after, not before the support in JDBC driver.
Second, the common subset of allowed connection parameters between the two is only as big as "host", "port", "database", "user" and "password".
Additionally, libpq understands the "ssl=true" parameter for JDBC compatibility, though I don't think that was a good idea in the end. For one, in the JDBC world "ssl=false" is treated exactly the same as "ssl=true" or any other value, which is questionable design in the first place. And even if you could use exactly the same URL in both libpq-based and JDBC clients, without running into syntax errors, the semantics of "ssl=true" is subtly different between the two: in the former case, the client does not attempt to validate certificate, nor the hostname, as opposed to the latter.
As to your actual example, the part of syntax that is treated differently in libpq is the the "userinfo": https://tools.ietf.org/html/rfc3986#section-3.2.1
The JDBC driver could be extended to support this as well, as such a change is backwards-compatible. As David has pointed out, this question should be asked to the PgJDBC project.
Lastly, the RFC provides some good arguments as to why providing username and, especially, password in the connection URL is undesirable. While the "user:password@host" or "?user=fred&password=secret" syntax can be handy for local testing, this is definitely not something that should be used in production. Both libpq and the JDBC driver provide ways to pass login credentials in a more secure manner.
Kind regards,
--
Alex