Failed to get driver instance for jdbcUrl=jdbc:postgresql:///<dbname> error for CloudSQL-CodePudding

I am trying to connect to my GCP projects PostgreSQL CloudSQL instance from my local machine. The PostgreSQL doesn't have a public IP, only private.

    Properties connProps = new Properties();
    connProps.setProperty("user", "[email protected]");
    connProps.setProperty("password", "password");
    connProps.setProperty("sslmode", "disable");
    connProps.setProperty("socketFactory", "com.google.cloud.sql.postgres.SocketFactory");
    connProps.setProperty("cloudSqlInstance", "coral-XXX-XXXX:us-central1:mdm");
    connProps.setProperty("enableIamAuth", "true");

    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(jdbcURL);
    config.setDataSourceProperties(connProps);
    config.setConnectionTimeout(10000); // 10s

    HikariDataSource connectionPool = new HikariDataSource(config);

I get the below error

Failed to get driver instance for jdbcUrl=jdbc:postgresql:///mdm

java.sql.SQLException: No suitable driver

I have verified that my username, instancename, IAM connectivity is all working fine. The IAM service account I am using is my compute engine's default service account.

Should I be able to connect to this PostgreSQL instance from my local machine?

CodePudding user response：

First, make sure you're configuring your JDBC URL correctly.

The URL should look like this:

jdbc:postgresql:///<DATABASE_NAME>?cloudSqlInstance=<INSTANCE_CONNECTION_NAME>&socketFactory=com.google.cloud.sql.postgres.SocketFactory&user=<POSTGRESQL_USER_NAME>&password=<POSTGRESQL_USER_PASSWORD>

See the docs for details.

Second, if your Cloud SQL instance is Private IP only, your local machine won't have a network path to it, unless you've explicitly configured one (see this answer for options).

Generally, the simplest way to connect to a private IP instance is to run a VM in the same VPC as the instance, and connect from that VM.

CodePudding user response：

While it is a good practice from the security point to have only the private IP enabled and remove public IP from the Cloud SQL instance, there are some considerations to be kept in mind when thinking about the connectivity.

With the Cloud SQL instance having only the private IP enabled there is no direct way in which you can connect to it from the local machine, neither by using private IP nor by using Cloud SQL proxy.

To establish the connection from an external resource you can make use of a Cloud VPN Tunnel or a VLAN attachment for Dedicated Interconnect or Partner Interconnect as mentioned here.

Also to connect to the Cloud SQL having only private IP we can enable port forwarding via a Compute Engine VM instance. This is done in two steps -

Connect the Compute Engine to the Cloud SQL instance via the private IP.
Forward the machine database connection request to the Compute Engine to reach the Cloud SQL instance through Cloud SQL Proxy tunnel.

To get a detailed description of the above you can go through this article.

Now, in your case, as you mentioned you have only private IP enabled in the Cloud SQL instance, that is the reason you are getting the error.