lemon-wire-69305
01/03/2023, 11:05 PMhost
as "localhost", as the host given in the connection string is `project:region:db_instance_name`_._
I can see in the following in the DB logs when it attempts to connect:
authorizationInfo: [
0: {
granted: true
permission: "cloudsql.instances.connect"
resource: "projects/my-project/instances/my-db-instance-8898595"
resourceAttributes: {
name: "projects/my-project/instances/my-db-instance-8898595"
service: "<http://sqladmin.googleapis.com|sqladmin.googleapis.com>"
type: "<http://sqladmin.googleapis.com/Instance|sqladmin.googleapis.com/Instance>"
}}]
methodName: "cloudsql.instances.connect"
So can see it's correctly reaching out for the right db etc, but that's the only message in the logs.
The ping code is basically doing:
driverName := "cloudsql-postgres"
cleanup, err := pgxv4.RegisterDriver(driverName, cloudsqlconn.WithIAMAuthN())
connStr := fmt.Sprintf("host=%v user=%v dbname=%v sslmode=disable", host, user, name)
db, err := sql.Open(driverName, connStr)
ctx, cancelfunc := context.WithTimeout(context.Background(), 30*time.Second)
pingErr := db.PingContext(ctx) // times out here
if pingErr != nil {
log.Fatalf("server: Got error: %v\n", pingErr) // logs error here due to timeout
}
and my k8s IAM setup is basically:
const saJob = new gcp.serviceaccount.Account("sa-job", {
accountId: "sa-for-job",
displayName: "My Service Account for the ping Job",
});
const iamDBEditor = new gcp.projects.IAMBinding("iam-db-editor", {
project: gcpProject,
role: "roles/cloudsql.editor",
members: [pulumi.interpolate`serviceAccount:${saJob.email}`],
}, {
parent: saJob
});
const iamDBUser = new gcp.projects.IAMBinding("iam-db-user", {
project: gcpProject,
role: "roles/cloudsql.instanceUser",
members: [pulumi.interpolate`serviceAccount:${saJob.email}`],
}, {
parent: saJob
});
const k8sSAJob = new k8s.core.v1.ServiceAccount("k8s-sa-job", {
metadata: {
namespace: namespaceName,
annotations: {
"<http://iam.gke.io/gcp-service-account|iam.gke.io/gcp-service-account>": saJob.email,
},
}
},
{
provider: clusterProvider,
})
const iamK8sBinding = new gcp.serviceaccount.IAMBinding("iam-k8s-db-editor", {
serviceAccountId: saJob.name,
role: "roles/iam.workloadIdentityUser",
members: [pulumi.interpolate`serviceAccount:${gcpProject}.svc.id.goog[${namespaceName}/${k8sSAJob.metadata.name}]`],
})
/* Create a user with the configured IAM credentials (linked to the service account).
For more info:
* <https://cloud.google.com/sql/docs/postgres/iam-logins>
* <https://cloud.google.com/sql/docs/postgres/add-manage-iam-users#creating-a-database-user>
*/
const dbUserJobAPIServerMigration = new gcp.sql.User("api_server_migrator", {
instance: db.instance.name,
name: saJob.email.apply((v) =>
v.replace(".<http://gserviceaccount.com|gserviceaccount.com>", "")
),
project: gcpProject,
type: "CLOUD_IAM_SERVICE_ACCOUNT",
});
I've also tested locally connecting to CloudSQL with pretty much the same golang code except using a cloudsqlconn.WithCredentialsFile("/Users/me/creds/sa-creds.json")
file instead of `cloudsqlconn.WithIAMAuthN()`along with a different db user and the ping code works as expected.
So, I'm thinking the problem might be something related to perhaps kubernetes and IAM auth, but I'm honestly just a bit stuck and hence the reach out.
Apologies for the long post, I'm hoping someone's been through this pain before and can offer a tip or two on troubleshooting or what might be going on. Thanks for your help!dry-keyboard-94795
01/04/2023, 9:43 AMlemon-wire-69305
01/04/2023, 8:02 PMdry-keyboard-94795
01/05/2023, 9:20 AM