search

petoju / terraform-provider-mysql

Terraform MySQL provider – unofficial fork
https://registry.terraform.io/providers/petoju/mysql
Mozilla Public License 2.0
63 stars 40 forks source link

Errors with mysql_grant granting roles in MySQL8 #89

Closed vmsilvia closed 2 months ago

vmsilvia commented 10 months ago

Hello,

We are trying to deploy MySQL 8 Aurora clusters to AWS and running into issues with mysql_grant, specifically when adding roles rather than privileges.

Terraform Version

Affected Resource(s)

Terraform Configuration Files

(Some data redacted)

provider "mysql" {
  alias            = "mysql-v-test"
  endpoint         = module.mysql-v-test.endpoint
  username         = module.mysql-v-test.admin_username
  password         = module.mysql-v-test.db_password
  tls              = "skip-verify"
}

module "mysql-v-test" {
   providers = {
      mysql = mysql.mysql-v-test                     
    }
    name                    = "mysql-v-test"
    service_name            = "mysql-v-test"               
    database_name           = "mysql_v_test"                     
    rds_instance_class      = "db.r6g.large"                     
    engine_version          = "8.0.mysql_aurora.3.03.0"               
    engine_mode             = "provisioned"
    instance_count          = 2                                  
    apply_immediately       = false                            
    backup_retention_period = 14                                  
    preferred_backup_window = "07:00-09:00"                      
    deletion_protection     = true                              
    manage_users            = true                          
    availability_zones      = local.availability_zones 
}

resource "mysql_grant" "adminuser_roles" {
  count    = var.manage_users && module.mysql.role_length ? 1 : 0
  user     = mysql_user.adminuser[count.index].user
  host     = mysql_user.adminuser[count.index].host
  database = "*"
  grant    = true
  roles    = module.mysql.all_roles
}

resource "mysql_grant" "adminuser_privs" {
  count      = var.manage_users ? 1 : 0
  user       = mysql_user.adminuser[count.index].user
  host       = mysql_user.adminuser[count.index].host
  database   = "*"
  grant      = true
  privileges = module.mysql.all_privileges
}

manage_users in this case is just a flag to tell the module whether to create or delete a handful of users we create internally by default.

Also, all_privileges and all_roles are defined as:

variable "all_privileges" {
  type    = list(string)
  default = ["SELECT", "INSERT", "UPDATE", "DELETE", "CREATE", "DROP", "RELOAD", "PROCESS", "REFERENCES", "INDEX", "ALTER", "SHOW DATABASES", "CREATE TEMPORARY TABLES", "LOCK TABLES", "EXECUTE", "REPLICATION SLAVE", "REPLICATION CLIENT", "CREATE VIEW", "SHOW VIEW", "CREATE ROUTINE", "ALTER ROUTINE", "CREATE USER", "EVENT", "TRIGGER"]
}

variable "all_roles" {
  type    = list(string)
  default = ["AWS_LOAD_S3_ACCESS", "AWS_SELECT_S3_ACCESS", "AWS_LAMBDA_ACCESS", "AWS_SAGEMAKER_ACCESS", "AWS_COMPREHEND_ACCESS"]
}

Expected Behavior

Cluster and default users created and user privilege and role grants correctly applied.

Actual Behavior

Terraform errors with the following output:

Error: Provider produced inconsistent result after apply

When applying changes to
module.mysql-v-test.mysql_grant.adminuser_roles[0], provider
"provider[\"registry.terraform.io/petoju/mysql\"].mysql-v-test" produced an
unexpected new value: Root resource was present, but now absent.

This is a bug in the provider, which should be reported in the provider's own
issue tracker.

Resources and users appear to be created successfully, and specifically adminuser_privs is present and destroyed when tearing down, but specifically adminuser_roles fails.

Steps to Reproduce

  1. terraform apply

Important Factoids

Error results when using unspecified provider version or pinning the provider version to v3.0.38+. No errors when version is pinned to v3.0.37. Also no errors when deploying MySQL 5.7 rather than MySQL8, suggesting the issue is related to the dynamic grant functionality for MySQL8 added in v3.0.38.

petoju commented 9 months ago

@vmsilvia thanks for the report.

Could you please send what TF log shows?

I'm interested mostly in lines like these and following ones

[DEBUG] Parsed grants are ...
[WARN] GRANT not found for ...

and anything failed.

You can get it by setting these

export TF_LOG=DEBUG
export TF_LOG_PATH=some-log-file

please don't provide your credentials in there.

vmsilvia commented 9 months ago

Hi @petoju I ran it in debug and there's only one error - the same as the TF output:

2023-09-12T12:16:28.164-0700 [ERROR] vertex "module.mysql-v-test.mysql_grant.adminuser_roles[0]" error: Provider produced inconsistent result after apply

There is a warning for a missing grant:

2023-09-12T12:16:28.158-0700 [INFO]  provider.terraform-provider-mysql_v3.0.41: 2023/09/12 12:16:28 [WARN] GRANT not found for 'adminuser'@'%' (%!s(<nil>)) - removing from state: timestamp=2023-09-12T12:16:28.153-0700

The parsed grants that apply to that specific user show up like this:

2023-09-12T12:16:28.158-0700 [INFO]  provider.terraform-provider-mysql_v3.0.41: 2023/09/12 12:16:28 [DEBUG] Parsed grants are: [{Database=,Table=,Privileges=[],Roles=[AWS_COMPREHEND_ACCESS AWS_LAMBDA_ACCESS AWS_LOAD_S3_ACCESS AWS_SAGEMAKER_ACCESS AWS_SELECT_S3_ACCESS],Grant=false}]: timestamp=2023-09-12T12:16:28.152-0700
2023-09-12T12:16:28.759-0700 [INFO]  provider.terraform-provider-mysql_v3.0.41: 2023/09/12 12:16:28 [DEBUG] Parsed grants are: [{Database=*,Table=*,Privileges=[SELECT INSERT UPDATE DELETE CREATE DROP RELOAD PROCESS REFERENCES INDEX ALTER SHOW DATABASES CREATE TEMPORARY TABLES LOCK TABLES EXECUTE REPLICATION SLAVE REPLICATION CLIENT CREATE VIEW SHOW VIEW CREATE ROUTINE ALTER ROUTINE CREATE USER EVENT TRIGGER],Roles=[],Grant=true} {Database=,Table=,Privileges=[],Roles=[AWS_COMPREHEND_ACCESS AWS_LAMBDA_ACCESS AWS_LOAD_S3_ACCESS AWS_SAGEMAKER_ACCESS AWS_SELECT_S3_ACCESS],Grant=false}]: timestamp=2023-09-12T12:16:28.759-0700

Also, not sure if they are relevant or not, but there are a lot of warnings that look like this for the default users:

2023-09-12T12:16:28.420-0700 [WARN]  Provider "registry.terraform.io/petoju/mysql" produced an invalid plan for module.mysql-v-test.mysql_grant.infra[0], but we are tolerating it because it is using the legacy plugin SDK.
     The following problems may be the cause of any confusing errors from downstream operations:
       - .table: planned value cty.StringVal("*") for a non-computed attribute
       - .grant: planned value cty.False for a non-computed attribute
       - .tls_option: planned value cty.StringVal("NONE") for a non-computed attribute

There is also a similar warning for the specific user role grants:

2023-09-12T12:16:27.851-0700 [WARN]  Provider "registry.terraform.io/petoju/mysql" produced an invalid plan for module.mysql-v-test.mysql_grant.adminuser_roles[0], but we are tolerating it because it is using the legacy plugin SDK.
     The following problems may be the cause of any confusing errors from downstream operations:
       - .tls_option: planned value cty.StringVal("NONE") for a non-computed attribute
       - .table: planned value cty.StringVal("*") for a non-computed attribute
petoju commented 9 months ago

@vmsilvia based on the output, I'd try this (not that it's solution, it's only to prove this is what doesn't work):

Try setting database="" in role assignment. I believe the issue is we stopped setting random database/table for the roles.

Then I'll have to think about some mitigation of this issue, where we just cannot extract this.

vmsilvia commented 9 months ago

@petoju Sure. I'll have to figure out how to do that with our modules, but I can give it a shot and report back. Thanks for the advice.

vmsilvia commented 9 months ago

@petoju Same error, same log output if I switch the database value in our module from "*" to "".

petoju commented 9 months ago

@vmsilvia I found the issue. We didn't really support roles with ADMIN OPTION - last refactoring only caused that to fail.

The fix is in v3.0.42.

Could you please verify that works?

vmsilvia commented 9 months ago

@petoju Awesome! It does look like if I pin the version to 3.0.42, the deploy completes successfully. Thanks for the quick turnaround!

Nirm01 commented 9 months ago

@petoju I am seeing this issue with 3.0.42 as well privileges = "ALTER,CREATE VIEW,CREATE,DELETE,DROP,GRANT OPTION,INDEX,INSERT,REFERENCES,LOCK TABLES,REFERENCES,SELECT,SHOW VIEW,TRIGGER,UPDATE"

Error: Provider produced inconsistent result after apply │ │ When applying changes to mysql_grant.grant["app_schema"], provider │ "provider[\"registry.terraform.io/petoju/mysql\"]" produced an unexpected │ new value: Root resource was present, but now absent. │ │ This is a bug in the provider, which should be reported in the provider's │ own issue tracker.

seeing the issue when using "GRANT OPTION" privilege

petoju commented 9 months ago

@Nirm01 what server are you using?

fdammeke commented 7 months ago

3.0.42 @petoju I am seeing this issue with 3.0.42 as well privileges = "ALTER,CREATE VIEW,CREATE,DELETE,DROP,GRANT OPTION,INDEX,INSERT,REFERENCES,LOCK TABLES,REFERENCES,SELECT,SHOW VIEW,TRIGGER,UPDATE"

Error: Provider produced inconsistent result after apply │ │ When applying changes to mysql_grant.grant["app_schema"], provider │ "provider["registry.terraform.io/petoju/mysql"]" produced an unexpected │ new value: Root resource was present, but now absent. │ │ This is a bug in the provider, which should be reported in the provider's │ own issue tracker.

seeing the issue when using "GRANT OPTION" privilege

Bumping in this one also:

Testing with:

Server version: 8.0.34-26 Percona Server (GPL), Release 26, Revision 0fe62c85
Terraform v1.5.7
on darwin_amd64
+ provider registry.terraform.io/petoju/mysql v3.0.43

Output:

│ Error: Provider produced inconsistent result after apply
│
│ When applying changes to module.mysql_grant_horizon["application_role"].mysql_grant.grant, provider "provider[\"registry.terraform.io/petoju/mysql\"].horizon" produced an unexpected new value: Root resource was
│ present, but now absent.
│
│ This is a bug in the provider, which should be reported in the provider's own issue tracker.
╵
╷
│ Error: Provider produced inconsistent result after apply
│
│ When applying changes to module.mysql_grant_horizon["developer_role"].mysql_grant.grant, provider "provider[\"registry.terraform.io/petoju/mysql\"].horizon" produced an unexpected new value: Root resource was
│ present, but now absent.
│
│ This is a bug in the provider, which should be reported in the provider's own issue tracker.

Resources:

Terraform will perform the following actions:

  # module.mysql_database_horizon["db01"].mysql_database.database will be created
  + resource "mysql_database" "database" {
      + default_character_set = "utf8mb4"
      + default_collation     = "utf8mb4_general_ci"
      + id                    = (known after apply)
      + name                  = "db01"
    }

  # module.mysql_grant_horizon["application_role"].mysql_grant.grant will be created
  + resource "mysql_grant" "grant" {
      + database   = "db01"
      + grant      = false
      + host       = "localhost"
      + id         = (known after apply)
      + privileges = [
          + "INSERT",
          + "SELECT",
        ]
      + role       = "application"
      + tls_option = "NONE"
    }

  # module.mysql_grant_horizon["developer_role"].mysql_grant.grant will be created
  + resource "mysql_grant" "grant" {
      + database   = "db01"
      + grant      = false
      + host       = "localhost"
      + id         = (known after apply)
      + privileges = [
          + "SELECT",
        ]
      + role       = "developer"
      + tls_option = "NONE"
    }

  # module.mysql_grant_horizon["user01-grant01"].mysql_grant.grant will be created
  + resource "mysql_grant" "grant" {
      + grant      = false
      + host       = "%"
      + id         = (known after apply)
      + roles      = [
          + "developer",
        ]
      + tls_option = "NONE"
      + user       = "user01"
    }

  # module.mysql_role_horizon["application"].mysql_role.role will be created
  + resource "mysql_role" "role" {
      + id   = (known after apply)
      + name = "application"
    }

  # module.mysql_role_horizon["developer"].mysql_role.role will be created
  + resource "mysql_role" "role" {
      + id   = (known after apply)
      + name = "developer"
    }

  # module.mysql_user_horizon["user01"].mysql_user.user will be created
  + resource "mysql_user" "user" {
      + host               = "%"
      + id                 = (known after apply)
      + plaintext_password = (sensitive value)
      + tls_option         = "NONE"
      + user               = "user01"
    }

Plan: 7 to add, 0 to change, 0 to destroy.

Run:

module.mysql_database_horizon["db01"].mysql_database.database: Creating...
module.mysql_role_horizon["developer"].mysql_role.role: Creating...
module.mysql_role_horizon["application"].mysql_role.role: Creating...
module.mysql_user_horizon["user01"].mysql_user.user: Creating...
module.mysql_grant_horizon["user01-grant01"].mysql_grant.grant: Creating...
module.mysql_grant_horizon["developer_role"].mysql_grant.grant: Creating...
module.mysql_grant_horizon["application_role"].mysql_grant.grant: Creating...
module.mysql_database_horizon["db01"].mysql_database.database: Creation complete after 0s [id=db01]
module.mysql_user_horizon["user01"].mysql_user.user: Creation complete after 1s [id=user01@%]
module.mysql_role_horizon["application"].mysql_role.role: Creation complete after 1s [id=application]
module.mysql_role_horizon["developer"].mysql_role.role: Creation complete after 1s [id=developer]
module.mysql_grant_horizon["user01-grant01"].mysql_grant.grant: Creation complete after 1s [id=user01@%:``]

The same happens in the case an apply fails, a user has to be created before the grant is applied, this can be fixed in terraform with depends_on, but the second run failure looks a bit bad:

│ Error: Error running SQL (GRANT select ON `db01`.* TO 'user01'@'%'): Error 1410 (42000): You are not allowed to create a user with GRANT
│
│   with module.mysql_grant_horizon["user01-grant01"].mysql_grant.grant,
│   on ../terraform-modules-mysql/modules/mysql-grant/resource.tf line 1, in resource "mysql_grant" "grant":
│    1: resource "mysql_grant" "grant" {

during a second apply the same error occurs:

module.mysql_grant_horizon["user01-grant01"].mysql_grant.grant: Creating...
╷
│ Error: Provider produced inconsistent result after apply
│
│ When applying changes to module.mysql_grant_horizon["user01-grant01"].mysql_grant.grant, provider "provider[\"registry.terraform.io/petoju/mysql\"].horizon" produced an unexpected new value: Root resource was
│ present, but now absent.
│
│ This is a bug in the provider, which should be reported in the provider's own issue tracker.

Small update on this, I've set the database and table value to empty string "" to test the previous use-case, this was causing this issue. Resetting the value to default null makes the resource behave correctly: │ Error: user/role 'user01'@'%' already has unmanaged grant to db01.* - import it first

petoju commented 2 months ago

Ok, so I believe this was fixed. If it was not, feel free to object here or create another issue.