Permission denied when i try grant role_admin in RDS

[SamuelMolling]

I'm creating a migration role and creating a user called usr_migration. When I try to grant migration permission to usr_migration in Amazon RDS, I get a permission denied error. I did some research and discovered that it is necessary to grant the ROLE_ADMIN grant to the admin user. When I do it manually, this works well and I can then give migration permission to usr_migration. The problem is that when I try to give a grant to the admin user of role_admin using mysql_grant, it gives me errors that the user has permissions, but they are not being managed by Terraform. Which makes sense, but I don't know how to solve the problem.

I need a grant on the admin user, before granting permission to another user.

Here's what I tested.

# ------------------------------------------------------------------------------
# MYSQL ROLE_ADMIN
# ------------------------------------------------------------------------------
resource "mysql_grant" "default" {
  user       = "root"
  database   = "*"
  privileges = ["ROLE_ADMIN"]
  host       = "%"
}

╷
│ Error: user/role {root %} already has unmanaged grant &{* * [ALTER ALTER ROUTINE CREATE CREATE ROUTINE CREATE TEMPORARY TABLES CREATE USER CREATE VIEW DELETE DROP EVENT EXECUTE INDEX INSERT LOCK TABLES PROCESS REFERENCES RELOAD REPLICATION CLIENT REPLICATION SLAVE SELECT SHOW DATABASES SHOW VIEW TRIGGER UPDATE] true {root %} NONE} - import it first
│ 
│   with module.mysql_roles.mysql_grant.default,
│   on .terraform/modules/mysql_roles/db_initial_config_mysql/main.tf line 11, in resource "mysql_grant" "default":
│   11: resource "mysql_grant" "default" {
│ 
╵

Has anyone ever experienced this?

[SamuelMolling]

@petoju Can you tell?

[petoju]

@SamuelMolling the issue is how permissions and drift detection work and potential side-effects.

Original MySQL provider does not detect drift or existing resources. That means if you're unlucky, you may end up with different permission set in terraform and in reality - generally, some users would complain about missing permissions despite them being in terraform. Nothing would suggest anything is wrong.

This provider does both drift detection and conflict detection.

Exact cases, where it almost always goes wrong without these features:

1. You grant different privileges to the same user/host/database (+optionally table). What should the provider do? Revoke permissions and grant that "missing" on every run?
2. Without conflict detection, you wouldn't directly see something like this has happened. Esp. with large configs, people often do the same thing twice in 2 places because they think it's missing0.
3. Without drift detection, same resource twice means deletion of one removes both. And you won't find out.
4. We could automatically "import" these resources, but that could mean changes to your existing resources.

...and that is why we try to prevent this.

Your solution is written in the error message - adjust your grant to match all granted privileges and import that grant. But if you did it with your config (privileges, no grant option), it would revoke your root's other permissions and that wouldn't end well.

Or you can just do import and then call "plan" (without apply) and adjust your config by adding more privileges and adjusting GRANT OPTION to minimize the diff.

Or just read, which privileges your need to add, fix that and then import the resource.

[SamuelMolling]

Importing is not an option. The developer who creates the resource is the one who doesn't know terraform or infrastructure. He just wants his database with the user.

Is it not possible to create a resource to execute a query so I would grant the grant via SQL? Or a resource that only manages privileges that were granted by terraform?

The problem is that RDS does not give the necessary permissions by default to the root user (role_admin, admin or super). He has certain privileges that come by default, but I just wanted to grant one role.

╷
│ Error: Error running SQL (GRANT application_write TO 'usr_testesamuel'@'localhost'): Error 1227 (42000): Access denied; you need (at least one of) the WITH ADMIN, ROLE_ADMIN, SUPER privilege(s) for this operation
│ 
│   with module.user_write.mysql_grant.default,
│   on .terraform/modules/user_write/db_user_mysql/main.tf line 6, in resource "mysql_grant" "default":
│    6: resource "mysql_grant" "default" {
│ 
╵

[SamuelMolling]

I managed to solve it using the sql provider and running grant in sql.

[petoju]

Importing is not an option. The developer who creates the resource is the one who doesn't know terraform or infrastructure. He just wants his database with the user.

FYI if you created some new user (I mean really new, not with existing permissions), there wouldn't be any issues like this. And revoke/extension would work as expected with terraform.

Importing is not that difficult - with new import block, one has to find out only import string and call "apply".

[SamuelMolling]

I cannot create a user granting the role I need, if my user does not have the role in RDS. Maybe with import and then granting it, it would work too.