Search code examples
postgresqlgoogle-cloud-platformgoogle-cloud-sqlgoogle-cloud-data-fusion

Issues connecting Data Fusion to CloudSQL Postgres instance - getting a 403 "The client is not authorized to make this request"

I've got DataFusion and Cloud SQL Postgres up and running in a single GCP project.

I can connect to my postgres instance successfully using pgAdmin (as long as I add my IP address to the allowed networks in the postgres instance settings), and also via the GCP cloud shell.

However, I cannot, no matter what I try, get the datafusion instance to connect successfully.

No matter what I've tried, this is the error I get:

Failed to create connection to database via connection string: jdbc:postgresql:///myDatabase?cloudSqlInstance=myProject-388422:us-central1:myInstance&socketFactory=com.google.cloud.sql.postgres.SocketFactory and arguments: {user=myUser}. Error: GoogleJsonResponseException: 403 Forbidden { "code" : 403, "errors" : [ { "domain" : "global", "message" : "The client is not authorized to make this request.", "reason" : "notAuthorized" } ], "message" : "The client is not authorized to make this request." }. Make sure you specify the correct connection properties.

Here is what I've tried:

  1. Added the following roles to the autocreated service user - Cloud Data Fusion Runner
    Cloud SQL Admin
    Cloud SQL Client
    Cloud SQL Editor
    Cloud SQL Instance User
    Cloud SQL Service Agent
    Cloud SQL Viewer
    Dataproc Worker
    Editor

  2. Added the same to my own user.

  3. Created a new service user and added the same roles.

  4. Enabled the cloud sql and cloud sql admin APIs.

None of the above has helped, and I cannot seem to find any other suggestions that can help me. (I'm not sure if I need to "set" the datafusion instance to use a specific service user or not...which might be a dumb thing to say).

I am plenty technical, but am not a GCP pro, so might be missing something obvious. Dearly hoping someone can help.

(also, apologies if this question has been asked elsewhere on this forum. I couldn't quite find it if so)

Solution

  • The service account used by the JDBC driver is service-CUSTOMER_PROJECT_NUMBER@gcp-sa- datafusion.iam.gserviceaccount.com (see Service accounts in Cloud Data Fusion).

    You just need to add the role roles/cloudsql.client to your Service Account and you should be able to connect to the database.