Unable to Set Role Permissions on Remote Instance With mysql_grant

Hi there,

Thank you for opening an issue. Please note that we try to keep the Terraform issue tracker reserved for bug reports and feature requests. For general usage questions, please see: https://www.terraform.io/community.html.

Terraform Version

Run terraform -v to show the version. If you are not running the latest version of Terraform, please upgrade because your issue may have already been fixed.

TF version: 1.3.6 Provider version: 3.0.29

Affected Resource(s)

Please list the resources as a list, for example:

Terraform State File
MySQL Roles

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "mysql_grant" "role-permissions" {
    for_each   = var.db-roles
    role       = "${each.key}-role"
    database   = each.value.schema
    privileges = each.value.default_table_privileges
    #host       = var.db-cluster-endpoint
}

Expected Behavior

What should have happened?

Terraform would create the role permissions on the host specified in the provider config (in this case, an AWS RDS Database running MySQL 8.0)

Actual Behavior

What actually happened?

Terraform tries to create the role permissions on localhost, times out, and breaks TF state because it can never connect to the local mysql server which doesn't exist. This is because the role and host arguments on the mysql_grant resource are incompatible, and host defaults to localhost. If it defaulted to whatever endpoint is specified in the provider config, that would solve this issue entirely.

I also tried setting the db endpoint as the MYSQL_ENDPOINT environment variable which is mentioned in the documentation, but this still doesn't work.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

terraform apply

Important Factoids

Are there anything atypical about your accounts that we should know? For example: Running in EC2 Classic? Custom version of OpenStack? Tight ACLs?

Running Terraform from my local Windows machine as a POC
DB is RDS Aurora MySQL Serverless V2

I believe you did not configure your provider like this (example usage, provider section): https://registry.terraform.io/providers/petoju/mysql/latest/docs#example-usage

Host is documented here: https://registry.terraform.io/providers/petoju/mysql/latest/docs/resources/grant - it has nothing to do with database host.

If you believe it could be documented better, please send a PR with a fix.

Hi, thanks for the reply.

I actually did configure the provider as described, I'm pulling the user info from an AWS Secrets Manager secret obtained through a Data block. I completely terminated everything I was working on over the weekend, and recreated everything this morning using "localhost" as the host argument and I'm still experiencing the same issue.

it errors on the create, but I confirmed that the users were created on the database itself:

│ Error: failed executing SQL: Error 1396 (HY000): Operation CREATE USER failed for 'service4'@'localhost'
│
│   with module.dbs-n-users.mysql_user.user["service4"],
│   on ..\..\modules\dbs-n-users\main.tf line 35, in resource "mysql_user" "user":
│   35: resource "mysql_user" "user" {
│
╵
╷
│ Error: failed executing SQL: Error 1396 (HY000): Operation CREATE USER failed for 'service3'@'localhost'
│
│   with module.dbs-n-users.mysql_user.user["service3"],
│   on ..\..\modules\dbs-n-users\main.tf line 35, in resource "mysql_user" "user":
│   35: resource "mysql_user" "user" {
│

and after running terraform plan afterwards it appears to be looking at my local machine for the mysql server even though the config for the provider has not changed.

╷
│ Error: failed to connect to MySQL: could not connect to server: dial tcp 127.0.0.1:3306: connectex: No connection could be made because the 
target machine actively refused it.
│
│   with module.dbs-n-users.mysql_user.clone-user["service3"],
│   on ..\..\modules\dbs-n-users\main.tf line 42, in resource "mysql_user" "clone-user":
│   42: resource "mysql_user" "clone-user" {
│
╵
╷
│ Error: failed to connect to MySQL: could not connect to server: dial tcp 127.0.0.1:3306: connectex: No connection could be made because the 
target machine actively refused it.
│
│   with module.dbs-n-users.mysql_user.clone-user["service4"],
│   on ..\..\modules\dbs-n-users\main.tf line 42, in resource "mysql_user" "clone-user":
│   42: resource "mysql_user" "clone-user" {
│
╵
╷
│ Error: failed to connect to MySQL: could not connect to server: dial tcp 127.0.0.1:3306: connectex: No connection could be made because the 
target machine actively refused it.
│
│   with module.dbs-n-users.mysql_database.app["service3"],
│   on ..\..\modules\dbs-n-users\main.tf line 49, in resource "mysql_database" "app":
│   49: resource "mysql_database" "app" {
│
╵
╷
│ Error: failed to connect to MySQL: could not connect to server: dial tcp 127.0.0.1:3306: connectex: No connection could be made because the 
target machine actively refused it.
│
│   with module.dbs-n-users.mysql_database.app["service4"],
│   on ..\..\modules\dbs-n-users\main.tf line 49, in resource "mysql_database" "app":
│   49: resource "mysql_database" "app" {
│
╵

do you have any insight on this, or do you know a way I could debug this?

Terraform provider configuration using data resources is not supported anywhere, with any provider. That's a pretty old issue - you'll have to hardcode your config. There are also some ways to resolve it (targeted apply), but it doesn't scale.

If you have any suggestion about how it could work, then feel free to suggest it.

For further debugging, it's useful to include minimal viable terraform reproducer. That includes also provider configuration. You can replace your secrets with XXXXXX or even even better a description like so it's clear, where is what.

petoju / terraform-provider-mysql