add support for Google's Cloud SQL CLOUD_IAM_USER and CLOUD_IAM_SERVICE_ACCOUNT

[czka]

Google's Cloud SQL allows adding users of type = "CLOUD_IAM_USER" and type = CLOUD_IAM_SERVICE_ACCOUNT using an IAM principal's email. See [1], [2]. IAM Cloud users can log on to a Cloud SQL instance using their IAM credentials if the instance has cloudsql.iam_authentication flag set and the IAM principal is bound to an IAM role which has cloudsql.instances.login permission. Eg. the built-in roles/cloudsql.instanceUser.

It would be great if PostgreSQL provider with scheme = "gcppostgres" supported CLOUD_IAM_USER and CLOUD_IAM_SERVICE_ACCOUNT user types.

After adding a someone@somedomain.com Cloud IAM user with GCP web console, on a newly provisioned PostgreSQL 14.4 instance, \du shows he's a member of cloudsqliamuser role:

         Role name          |                         Attributes                         |           Member of            
----------------------------+------------------------------------------------------------+--------------------------------
 cloudsqladmin              | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 cloudsqlagent              | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqliamserviceaccount  | Cannot login                                               | {}
 cloudsqliamuser            | Cannot login                                               | {}
 cloudsqlimportexport       | Create role, Create DB                                     | {cloudsqlsuperuser}
 cloudsqlreplica            | Replication                                                | {pg_monitor}
 cloudsqlsuperuser          | Create role, Create DB                                     | {pg_monitor,pg_signal_backend}
 someone@somedomain.com     |                                                            | {cloudsqliamuser}
 postgres                   | Create role, Create DB                                     | {cloudsqlsuperuser}

Interestingly, cloudsqliamuser role can't be granted or revoked normally:

postgres=> GRANT "cloudsqliamuser" TO "postgres";
ERROR:  grant or revoke of role "cloudsqliamuser" is not allowed

postgres=> REVOKE "cloudsqliamuser" FROM "someone@somedomain.com";
ERROR:  grant or revoke of role "cloudsqliamuser" is not allowed

FYI, Google's Cloud SQL Terraform module already supports Cloud IAM users, including the IAM binding setup [3].

[1]https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_user [2]https://cloud.google.com/sql/docs/postgres/authentication [3]https://github.com/terraform-google-modules/terraform-google-sql-db/blob/v11.0.0/modules/postgresql/main.tf#L214

[BigDwarf]

@cyrilgdn do you mind if i'll work on this issue ?

[philip-harvey]

I'm not sure this will be possible since as far as I can tell it's not possible to grant the cloudsqliamuser role. I get this error: Error: could not grant role cloudsqliamuser to user@gmail.com: pq: grant or revoke of role "cloudsqliamuser" is not allowed

I think the work around for this is to create the roles using the Google provider and then update the role using this provider, however currently this isn't possible since this providers role resource forces create and doesn't support update. https://github.com/cyrilgdn/terraform-provider-postgresql/issues/234