Google Cloud SQL with mysql8: Revoke OR change of grants fails for CLOUD_IAM_USER accounts

[TreZc0]

Ever since updating our CloudSQL mysql instances to 8, we struggle to remove grants from our users.

╷
│ Error: error revoking REVOKE ALTER, CREATE, DELETE, DROP, INDEX, INSERT, PROCESS, REFERENCES, SELECT, SHOW VIEW, SHOW_ROUTINE, UPDATE ON . FROM 'firstname.lastname'@'%': Error 3879 (HY000): Access denied for AuthId root@% to database 'mysql'.
│
│
╵

All our sql users are CLOUD_IAM_USER objects in GCP, embedded into the GCP IAM workflow. Before v8, it was simple to remove users, now, the above error occurs, and users need to be manually removed. The same happens when we try to change the grants of a user.

resource "google_sql_user" "api-dev-users" {
  for_each = toset(concat(var.cloudsql_read_users, var.cloudsql_write_users))
  name     = each.value
  instance = google_sql_database_instance.api-dev.name
  type     = "CLOUD_IAM_USER"
}

resource "mysql_grant" "developer-read-users" {
  for_each   = toset(var.cloudsql_read_users)
  user       = split("@", each.value)[0]
  host       = "%"
  database   = "*"
  privileges = ["SELECT", "SHOW VIEW", "PROCESS", "SHOW_ROUTINE"]
  provider   = mysql.api-dev
  depends_on = [
    google_sql_user.api-dev-users
  ]
}

The only fix seems to be to manually destroy the cloud_iam_user element and then apply the mysql_grant resource again. I tried messing with the depends_on in the mysql_grant resource, but to no effect. The only workaround i could find is to run a time resource inbetween that forces the google_sql_user element to be messed with first and then let the grant depend on the time element, but that only helps for destruction of users, not for altering of grants.

[petoju]

Can you run that revoke manually as root? Just whether you have permissions to revoke something.

Basically: we need to know, which commands work and which don't work when calling it manually. Then we can build a provider around it (or advise you something).

[TreZc0]

Unfortunately no, it seems like we can't. I fear this might be a classic issue of inconsistent expected roomup / destroy strategies that Terraform notoriously cannot handle. If the IAM User is removed first, it's fine. Obviously, that is not possible for the creation though, so changing the depends_on is not an option :/

[petoju]

@TreZc0 I don't understand this.

Please provide whole end-to-end manual test and output.

So: do you want to create a user (possibly via terraform), add grant, revoke grant and then remove user? Why wouldn't that work? How are you running grant and how are you doing revoke? Does revoking just part of privileges work?

[TreZc0]

Yes, pretty much.
The users are pre-existing google cloud iam users (provided by an existing google workspace identity).
They are then created as a https://registry.terraform.io/providers/hashicorp/google/latest/docs/resources/sql_user.html via Terraform, specifically as type cloud_iam_user, which means they are not authenticating with a password, but use pre-existing google oidc auth tokens to login into a cloud sql instance.

Then we use the mysql provider to hand out grants to the google_sql_user resources, in this case through a variable "cloudsql_read_users", which only contains a list of e-mail addresses.

That works just fine.

However, when a user is removed from that variable list, the mysql provider tries to remove the grant and fails with above error. Removing the google_sql_user is still possible, but terraform is stopping beforehand cause the mysql grant removal stops with an error first.

The same happens if you try to change the grant of a user. For example, imagine another resource like this

resource "mysql_grant" "developer-write-users"" {
  for_each   = toset(var.cloudsql_write_users)
  user       = split("@", each.value)[0]
  host       = "%"
  database   = "*"
  privileges = ["SELECT", "INSERT", "UPDATE", "DELETE", "CREATE", "DROP", "INDEX", "ALTER", "SHOW VIEW", "PROCESS", "REFERENCES"]
  provider   = mysql.api-dev
  depends_on = [
    google_sql_user.api-dev-db-users
  ]
}

same deal really, just a different variable to feed from and different privileges. If a user is moved from one variable to another, the provider tries removing the old grant in the first step, and fails.
To resolve this, it seems like manually wiping the google_sql_user element first allows the mysql provider to run the commands as desired.

[petoju]

@TreZc0 you are still describing terraform to me.

Does this mean that you can run GRANT manually, but cannot run the very same REVOKE? Does your user have permissions to revoke permissions?

(Terraform does more "magic" and I'd like to understand it without this part - like if you wipe google_sql_user manually, then the resource is not there anymore and terraform doesn't run REVOKE)

[TreZc0]

This is indeed interesting.

mysql> show grants for 'user'@'%';
+------------------------------------------------------+
| Grants for user@%                                     |
+------------------------------------------------------+
| GRANT SELECT, PROCESS, SHOW VIEW ON *.* TO `user`@`%` |
| GRANT SHOW_ROUTINE ON *.* TO `user`@`%`               |
+------------------------------------------------------+
2 rows in set (0,02 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER, SHOW VIEW ON *.* to 'user'@'%';
Query OK, 0 rows affected (0,03 sec)

mysql> show grants for 'user'@'%';
+----------------------------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                                     |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER, SHOW VIEW ON *.* TO `user`@`%` |
| GRANT SHOW_ROUTINE ON *.* TO `user`@`%`                                                                               |
| REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `mysql`.* FROM `user`@`%`                                |
| REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON `sys`.* FROM `user`@`%`                                  |
+----------------------------------------------------------------------------------------------------------------------+
4 rows in set (0,03 sec)

mysql> REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER, SHOW VIEW ON *.* FROM 'user'@'%';
ERROR 3879 (HY000): Access denied for AuthId `root`@`%` to database 'sys'.

It seems that when for a user, writing grants have been handed out, cloud sql automatically reacts by settings revokes for that user in the mysql and sys databases (these were auto generated when i ran the grant command). and these fixed revokes can no longer be removed, neither by the root user and thus of course not by the provider.

[petoju]

@TreZc0 so this is a difficult thing to solve - maybe Google Support could push for different implementation, but I doubt they would be willing to. As you can see, the revoke doesn't work as ability to change or remove something is the basic idea behind terraform.

Maybe I'd consider setting off partial_revokes (on whole server), but I don't know whether that's good enough.

Generally, this provider doesn't work well with partial_revokes.