I am using GCP Cloud SQL instances for getting data. This SQL instance when accessed with its public IP address, connection is happening and data is visible. But due to security constraint I will have to access it only via private IP address.

I made code changes as said in Google documentations for connection via private IP address: (https://cloud.google.com/sql/docs/postgres/connect-app-engine-standard#private-ip_1) (https://cloud.google.com/vpc/docs/configure-serverless-vpc-access#java-8)

1. In ConnectionPoolContextListener.java file: iptypes=Private added

config.addDataSourceProperty("ipTypes", "PRIVATE");

2. In appengine-web.xml file: serverless vpc connector element added

projects/PROJECTNAME/locations/europe-west1/connectors/CONNECTORNAME all-traffic

3. In gitlab-ci.yml file: line to deploy the connector service added

deploy_env-name: script:-gcloud app deploy src/main/webapp/WEB-INF/appengine-web.xml

These changes are not working and the API calls made are failing giving CORS errors(cross origin resource sharing error preflight missing allow origin header)(refer to screenshot) UI CORS Error

App engines logs are as follows: com.zaxxer.hikari.pool.HikariPool throwPoolInitializationException: HikariPool-1 - Exception during pool initialization. (HikariPool.java:587) org.postgresql.util.PSQLException: The connection attempt failed. ... Caused by: java.io.IOException: Connection refused

Everything is working when public IP address is used no CORS error also. But with private IP address connection is failing, not sure what is wrong here.

DB Connection code:

private DataSource createConnectionPool() {
HikariConfig config = new HikariConfig();
config.setJdbcUrl(String.format("jdbc:postgresql://google/%s", 
DB_NAME));
config.setUsername(DB_USER);
config.setPassword(DB_PASS); 
config.addDataSourceProperty("socketFactory", 
"com.google.cloud.sql.postgres.SocketFactory");
config.addDataSourceProperty("cloudSqlInstance", 
CLOUD_SQL_CONNECTION_NAME);
config.addDataSourceProperty("ipTypes", "PRIVATE");
config.setMaximumPoolSize(10);
config.setMinimumIdle(5);
config.setConnectionTimeout(10000); // 10 seconds
config.setIdleTimeout(600000); // 10 minutes
config.setMaxLifetime(1800000); // 30 minutes
DataSource pool = new HikariDataSource(config);
return pool;
}

CodePudding user response：

There was something wrong in VPC connector that I had created before. I created a new VPC connector with same network and region as those of the cloudsql instance and assigned an IP address range and now this DB connection is happening and data is getting loaded and CORS error has gone.

So to connect to a SQL instance via Private IP address from App engine I had to make only the following changes:

1. Create a Serverless VPC connector.
2. add vpc-connector element in appengine.yml file
3. include property "iptypes" in ConnectionPoolContextListener.java file

CodePudding user response：

Here's the problem. The connection string has an error in it:

// Not correct
config.setJdbcUrl(String.format("jdbc:postgresql://google/%s", DB_NAME));

// Should be
config.setJdbcUrl(String.format("jdbc:postgresql:///%s", DB_NAME));

See the documentation for details: https://github.com/GoogleCloudPlatform/cloud-sql-jdbc-socket-factory/blob/main/docs/jdbc-postgres.md#creating-the-jdbc-url.