error grant permissions to a user in AWS RDS MySQL 8.0

[stefankuehne415]

Hello,

I'm not able to grant permission to login from everywhere to a user for an AWS RDS MySQL 8.0.32 serverless v2 instance.

Terraform Version

Terraform v1.3.6

resource "mysql_database" "db" {
  name = var.database_name
}

resource "random_string" "password" {
  length  = 32
  special = false
}

resource "mysql_user" "db" {
  user               = var.database_name
  plaintext_password = random_string.password.result
}

resource "mysql_grant" "db" {
  user       = mysql_user.db.user
  host       = "%"
  database   = mysql_database.db.name
  privileges = ["ALL"]
}

Error message:

 Error: Error running SQL (GRANT ALL ON `crm-application`.* TO 'crm-application'@'%'): Error 1410 (42000): You are not allowed to create a user with GRANT
│
│   with mysql_grant.db,
│   on main.tf line 34, in resource "mysql_grant" "db":
│   34: resource "mysql_grant" "db" {
│

Following host values work: "localhost" and "10.10.10.10". Following values don't work: "example.com" and "%"

If I set the host parameter in mysql_user resource, it is the same behaviour. I would like to set in the permission to login from everywhere..

Maybe this is related to: [https://serverfault.com/questions/581807/root-is-not-allowed-to-grant-privileges-on-a-mysql-database]( Serverfault error).

What did I do wrong?

[petoju]

@stefankuehne415 the issue is you are creating different user from what you see.

There are more things to understand:

• MySQL identifies user by tuple (username, hostname) generally written as username@hostname. So user stefan@localhost is different from stefan@%
• This provider defaults host for mysql_user resource to localhost. Maybe that's not the best idea, but making it a required parameter would break bunch of people.
• As user is identified by a tuple username@hostname, also mysql_grant needs such tuple and it needs to match username@hostname.

Because of that, running mysql_grant on non-existent user fails.

Now there is a question, what should mysql_grant do when the user doesn't exist. When you run GRANT on MySQL 5.x in default settings, it automatically creates a user with password being not specified. That may be difficult to debug when written as IaC - also terraform doesn't know about that hidden action and therefore cannot delete such user later.

My solution is to set NO_AUTO_CREATE_USER that prevents MySQL from automatically creating such user. That's why you see the error message, that the user cannot be created automatically.

Footnote: the original MySQL provider did not care about creating new users. Because of that, it could not care when a specified user already existed. That led to some undetected typos on our side, so my fix is to avoid auto-creating users and also to check, whether a user doesn't exist yet.

[stefankuehne415]

Hi Petoju,

thanks you so much! You're absolutly right.

The code:

resource "mysql_user" "db" {
  user               = var.database_name
  host               = "%"
  plaintext_password = random_string.password.result
}

resource "mysql_grant" "db" {
  depends_on = [mysql_user.db]
  user       = var.database_name
  host       = "%"
  database   = mysql_database.db.name
  privileges = ["ALL"]
}

works fine :-)...