java - 在本地和通过 App Engine Flex 连接到 Google Cloud SQL (PostgreSQL)

Question

本地:我的本地环境是 Mac OS。我正在尝试使用 Java 8 连接到我的 Cloud SQL (PostgreSQL) 数据库。我已将我的 IP 列入白名单，并且能够使用 pgadmin 连接到数据库。我的服务帐户也是托管数据库的项目和应用引擎环境的项目所有者

这是我用于本地连接的教程:

https://cloud.google.com/sql/docs/postgres/connect-external-app#java

和我的实际代码:

        private static void jdbcUrl = String.format(
            "jdbc:postgresql://google/%s?socketFactory=com.google.cloud.sql.postgres.SocketFactory"
                    + "&cloudSqlInstance=%s",
            Configuration.getDatabaseName(),
            Configuration.getInstanceConnectionName());

    public static String getDbConnection() throws Exception {
        LOGGER.info(jdbcUrl);

        Connection connection = DriverManager.getConnection(jdbcUrl, Configuration.getUsername(), Configuration.getPassword());

...

这是在我的代码中生成的 url 调用(出于显而易见的目的，我已经更改了原始代码):

jdbc:postgresql://google/users?socketFactory=com.google.cloud.sql.postgres.SocketFactory&cloudSqlInstance=my-project:europe-west1:my-db

由于这个错误，我无法连接这个电话:

Nov 29, 2018 7:13:11 PM com.google.cloud.sql.postgres.SocketFactory createSocket
INFO: Connecting to Cloud SQL instance [my-project:europe-west1:my-db] via ssl socket.
Nov 29, 2018 7:13:11 PM com.google.cloud.sql.core.SslSocketFactory getInstance
INFO: First Cloud SQL connection, generating RSA key pair.
Nov 29, 2018 7:13:12 PM com.google.cloud.sql.core.SslSocketFactory fetchInstanceSslInfo
INFO: Obtaining ephemeral certificate for Cloud SQL instance [my-project:europe-west1:my-db].
Nov 29, 2018 7:13:13 PM com.google.cloud.sql.core.SslSocketFactory createAndConfigureSocket
INFO: Connecting to Cloud SQL instance [my-project:europe-west1:my-db] on IP [30.100.40.150].
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:195)
    at org.postgresql.Driver.makeConnection(Driver.java:454)
    at org.postgresql.Driver.connect(Driver.java:256)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:247)
    at com.ri.ccm.utils.DbConnection.getDbConnection(DbConnection.java:23)
    at com.ri.ccm.routes.DummyRoute.serviceCategoryDetail(DummyRoute.java:22)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory.lambda$static$0(ResourceMethodInvocationHandlerFactory.java:76)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:148)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:191)
    at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$ResponseOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:200)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:103)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:493)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:415)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:104)
    at org.glassfish.jersey.server.ServerRuntime$1.run(ServerRuntime.java:277)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:272)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:268)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:316)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:298)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:268)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:289)
    at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:256)
    at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:703)
    at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:416)
    at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:370)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:389)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:342)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:229)
    at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:833)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1634)
    at org.eclipse.jetty.websocket.server.WebSocketUpgradeFilter.doFilter(WebSocketUpgradeFilter.java:206)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1621)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:541)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:143)
    at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:548)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:190)
    at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1593)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:188)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1239)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:168)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:481)
    at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1562)
    at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:166)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1141)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:213)
    at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:118)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:132)
    at org.eclipse.jetty.server.Server.handle(Server.java:564)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:279)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:110)
    at org.eclipse.jetty.io.ChannelEndPoint$2.run(ChannelEndPoint.java:124)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:672)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:590)
    at java.lang.Thread.run(Thread.java:748)
Caused by: java.net.SocketException: Connection reset
    at java.net.SocketInputStream.read(SocketInputStream.java:210)
    at java.net.SocketInputStream.read(SocketInputStream.java:141)
    at sun.security.ssl.InputRecord.readFully(InputRecord.java:465)
    at sun.security.ssl.InputRecord.read(InputRecord.java:503)
    at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:983)
    at sun.security.ssl.SSLSocketImpl.performInitialHandshake(SSLSocketImpl.java:1385)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1413)
    at sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:1397)
    at com.google.cloud.sql.core.SslSocketFactory.createAndConfigureSocket(SslSocketFactory.java:210)
    at com.google.cloud.sql.core.SslSocketFactory.create(SslSocketFactory.java:160)
    at com.google.cloud.sql.postgres.SocketFactory.createSocket(SocketFactory.java:96)
    at org.postgresql.core.PGStream.<init>(PGStream.java:62)
    at org.postgresql.core.v3.ConnectionFactoryImpl.tryConnect(ConnectionFactoryImpl.java:91)
    at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:192)
    ... 64 more

但是，我可以通过此调用连接并取回数据:

jdbc:postgresql://30.100.40.150/users

唯一的问题是，我必须将每个 ip (0.0.0.0/24) 列入白名单，以便此 url 在 App Engine Flex(包括我的)中工作!现在，出于安全原因，我显然不想这样做。

通过 App Engine Flex:

jdbc:postgresql://30.100.40.150/users

我可以在 App Engine Flex 中连接此调用^，但我必须再次将每个 IP 列入白名单才能正常工作。该文档指出，我不必将 App Engine Flex 中的 IP 列入白名单就可以正常工作(它应该只使用服务帐户)。我猜是原始调用，在文档中就是这样做的。但是，这不起作用。所以我停滞不前。

拜托，看在上帝的份上，有人可以帮助我。连接到数据库应该不难!

我想要做的就是通过我的本地环境和我的 App Engine Flex 环境连接到我的远程 Cloud SQL PostgreSQL 数据库，而不必将每个 IP 地址列入白名单(并以“正确”的方式进行)。我正在使用 Maven 配置文件来区分我的环境。

我还尝试设置 Cloud SQL 代理以供本地使用，但它不起作用，我收到此错误:

couldn't connect to "my-project:europe-west1:my-db": read tcp 10.75.120.100:49663->30.100.40.150:3307: read: connection reset by peer

我真的没有想法了，这可能是防火墙端口问题吗？请帮助:(

Answer 1

使用 Cloud SQL 代理或套接字工厂时需要出站 tcp 端口 3307 访问权限，但使用 native PostgreSQL JDBC 驱动程序时则不需要。确保您的 Mac 或本地网络上没有防火墙阻止出站端口 3307。