Issue creating `mysql_grant` in Aurora 3/MySQL 8.0

[jonathan-russo]

Hi there,

I am having receiving errors while trying to create a mysql_grant for a MySQL User on a Aurora 3/MySQL 8.0 cluster.

Terraform Version

1.1.4

Affected Resource(s)

mysql_grant

Terraform Configuration Files

resource "mysql_user" "user" {
  count = length(var.mysql_user_map)

  user               = var.mysql_user_map[count.index].username
  host               = "%"
  plaintext_password = var.mysql_user_map[count.index].password
}

resource "mysql_grant" "user_grant" {
  depends_on = [mysql_user.user]
  count = length(var.mysql_user_map)

  user       = var.mysql_user_map[count.index].username
  host       = "%"
  database   = "*"
  privileges = ["DELETE","DROP","INSERT","SELECT","UPDATE","CREATE","CREATE TEMPORARY TABLES"]
}

Debug Output

[0mmysql_grant.user_grant[0]: Creating...
2022-03-08T09:01:34.861-0500 [INFO]  Starting apply for mysql_grant.user_grant[0]
2022-03-08T09:01:34.861-0500 [TRACE] provider.terraform-provider-mysql_v3.0.11: Received request: @module=sdk.proto tf_rpc=ApplyResourceChange @caller=github.com/hashicorp/terraform-plugin-go@v0.5.0/tfprotov5/tf5server/server.go:595 tf_proto_version=5 tf_provider_addr=provider tf_req_id=5d43f1eb-72f0-4065-fe7a-92cbc1a2c0ae tf_resource_type=mysql_grant timestamp=2022-03-08T09:01:34.861-0500
2022-03-08T09:01:34.861-0500 [TRACE] provider.terraform-provider-mysql_v3.0.11: Calling downstream: tf_req_id=5d43f1eb-72f0-4065-fe7a-92cbc1a2c0ae @module=sdk.proto tf_proto_version=5 tf_provider_addr=provider tf_resource_type=mysql_grant tf_rpc=ApplyResourceChange @caller=github.com/hashicorp/terraform-plugin-go@v0.5.0/tfprotov5/tf5server/server.go:602 timestamp=2022-03-08T09:01:34.861-0500
2022-03-08T09:01:34.861-0500 [DEBUG] mysql_grant.user_grant[4]: applying the planned Create change
2022-03-08T09:01:34.862-0500 [TRACE] GRPCProvider: ApplyResourceChange
2022-03-08T09:01:34.862-0500 [TRACE] provider.terraform-provider-mysql_v3.0.11: Received request: tf_req_id=e4bc8edd-76eb-a251-d578-c8474c3775e9 tf_resource_type=mysql_grant tf_rpc=ApplyResourceChange @caller=github.com/hashicorp/terraform-plugin-go@v0.5.0/tfprotov5/tf5server/server.go:595 tf_proto_version=5 tf_provider_addr=provider @module=sdk.proto timestamp=2022-03-08T09:01:34.862-0500
2022-03-08T09:01:34.862-0500 [TRACE] provider.terraform-provider-mysql_v3.0.11: Calling downstream: tf_proto_version=5 tf_resource_type=mysql_grant tf_rpc=ApplyResourceChange tf_provider_addr=provider tf_req_id=e4bc8edd-76eb-a251-d578-c8474c3775e9 @caller=github.com/hashicorp/terraform-plugin-go@v0.5.0/tfprotov5/tf5server/server.go:602 @module=sdk.proto timestamp=2022-03-08T09:01:34.862-0500
2022-03-08T09:01:34.862-0500 [DEBUG] mysql_grant.user_grant[0]: applying the planned Create change
2022-03-08T09:01:34.862-0500 [TRACE] GRPCProvider: ApplyResourceChange
2022-03-08T09:01:34.862-0500 [TRACE] provider.terraform-provider-mysql_v3.0.11: Received request: tf_req_id=c68e7f6f-354e-412b-b399-7473415ca865 tf_resource_type=mysql_grant @caller=github.com/hashicorp/terraform-plugin-go@v0.5.0/tfprotov5/tf5server/server.go:595 @module=sdk.proto tf_proto_version=5 tf_provider_addr=provider tf_rpc=ApplyResourceChange timestamp=2022-03-08T09:01:34.862-0500
2022-03-08T09:01:34.862-0500 [TRACE] provider.terraform-provider-mysql_v3.0.11: Calling downstream: @module=sdk.proto tf_proto_version=5 tf_provider_addr=provider tf_req_id=c68e7f6f-354e-412b-b399-7473415ca865 tf_resource_type=mysql_grant tf_rpc=ApplyResourceChange @caller=github.com/hashicorp/terraform-plugin-go@v0.5.0/tfprotov5/tf5server/server.go:602 timestamp=2022-03-08T09:01:34.862-0500
2022-03-08T09:01:34.931-0500 [INFO]  provider.terraform-provider-mysql_v3.0.11: 2022/03/08 09:01:34 Executing statement: GRANT UPDATE, CREATE, CREATE TEMPORARY TABLES, SELECT ON *.* TO 'my.user'@'%': timestamp=2022-03-08T09:01:34.930-0500
2022-03-08T09:01:35.038-0500 [INFO]  provider.terraform-provider-mysql_v3.0.11: 2022/03/08 09:01:35 [WARN] GRANT not found for 'my.user'@'%' (failed to parse grant statement: REVOKE UPDATE, CREATE, CREATE TEMPORARY TABLES ON `mysql`.* FROM `my.user`@`%`) - removing from state: timestamp=2022-03-08T09:01:35.038-0500
2022-03-08T09:01:35.038-0500 [TRACE] provider.terraform-provider-mysql_v3.0.11: Called downstream: @module=sdk.proto tf_proto_version=5 tf_provider_addr=provider tf_resource_type=mysql_grant @caller=github.com/hashicorp/terraform-plugin-go@v0.5.0/tfprotov5/tf5server/server.go:608 tf_req_id=5a8cdced-108c-0ec7-ab10-8a078277485e tf_rpc=ApplyResourceChange timestamp=2022-03-08T09:01:35.038-0500
2022-03-08T09:01:35.038-0500 [TRACE] provider.terraform-provider-mysql_v3.0.11: Served request: tf_rpc=ApplyResourceChange tf_proto_version=5 tf_provider_addr=provider tf_req_id=5a8cdced-108c-0ec7-ab10-8a078277485e tf_resource_type=mysql_grant @caller=github.com/hashicorp/terraform-plugin-go@v0.5.0/tfprotov5/tf5server/server.go:614 @module=sdk.proto timestamp=2022-03-08T09:01:35.038-0500

Panic Output

╷
│ Error: Provider produced inconsistent result after apply
│ 
│ When applying changes to mysql_grant.user_grant[0], 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.
╵

Expected Behavior

What should have happened?

The provider should have created the grants properly and not errored out.

Actual Behavior

What actually happened?

The provider created the grants but errored out and didn't store the state. Subsequent plans/applys request the creation of the mysql_grant, but fail in the same way.

Steps to Reproduce

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

1. terraform apply

[petoju]

Hi @jonathan-russo , it seems you are using partial revokes - see the documentation here: https://dev.mysql.com/doc/refman/8.0/en/partial-revokes.html

This terraform provider doesn't support partial revokes yet - nowadays, it cannot work with them. Quick fix for you: disable partial revokes.

I've just built a provider version 3.0.12, that could be able to co-exist with partial revokes. That said, it's only a quick fix and the proper fix may take some time (I'm open to PRs).

[jonathan-russo]

Hi @petoju . Unfortunately it appears that in Aurora 3/MySQL 8.0 you cannot disable partial_revokes (AWS Docs). Here is further proof on my live system.

mysql> select CURRENT_USER();
+----------------+
| CURRENT_USER() |
+----------------+
| admin@%        |
+----------------+
1 row in set (0.03 sec)

mysql> show grants for 'admin'@'%';
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                                                                                                                                                                                                                                                                      |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT 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, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,BACKUP_ADMIN,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,SHOW_ROUTINE ON *.* TO `admin`@`%` WITH GRANT OPTION                                                                                                                                                                                 |
| REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, CREATE ROUTINE, ALTER ROUTINE ON `mysql`.* FROM `admin`@`%`                                                                                                                                                                               |
| GRANT `AWS_COMPREHEND_ACCESS`@`%`,`AWS_LAMBDA_ACCESS`@`%`,`AWS_LOAD_S3_ACCESS`@`%`,`AWS_SAGEMAKER_ACCESS`@`%`,`AWS_SELECT_S3_ACCESS`@`%`,`rds_superuser_role`@`%` TO `admin`@`%`                                                                                                                                                                        |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.03 sec)

mysql> show variables like 'partial_revokes';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| partial_revokes | ON    |
+-----------------+-------+
1 row in set (0.04 sec)

mysql> SET PERSIST partial_revokes = OFF;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER or SYSTEM_VARIABLES_ADMIN privilege(s) for this operation

Your quick fix on Version 3.0.12 appears to work for my use case. Thank you very much for the speedy help.

[petoju]

@jonathan-russo you could still stop using partial revokes - by granting admin all the permissions including revoked ones.

I'm leaving this issue open - we should implement a data model to support partial revokes and to correctly apply them. With partial revokes, order of applies matters and one cannot easily modify and extend some grants. That will need more work to get expected results under all circumstances.

[jonathan-russo]

@petoju unfortunately the admin user is the highest privileged MySQL user AWS Aurora provides you with. They do not allow you to use the SUPER or SYSTEM_VARIABLES_ADMIN privileges, only allowing you the following.

Please let me know If I'm misunderstanding you though.

I'm fine with leaving the issue open, definitely understand it's not a permanent solution.

[jonathan-russo]

Actually I spoke too soon on the quick fix working. Attempting to destroy the users resulted in the following error:

│ Error: error revoking ALL (REVOKE DROP, UPDATE, CREATE, CREATE TEMPORARY TABLES, SELECT, DELETE, INSERT ON *.* FROM 'my.user'@'%'): Error 3879: Access denied for AuthId `admin`@`%` to database 'mysql'.

[petoju]

@jonathan-russo could you please try running commands, that you'd like terraform to run and report whether they work or what should be done differently?

I believe any GRANT should be revoked by running REVOKE and any CREATE USER means that deletion should be done by DROP USER.

Your description seems to suggest, that we could not revoke all the granted permissions. The question is then - what should we do?

[jonathan-russo]

@petoju

So after running some tests manually I receive the same error when running the commands manually via a mysql console

test-db> revoke SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, CREATE TEMPORARY TABLES ON *.* from `my.user`@`%`
[2022-03-15 14:03:46] [HY000][3879] Access denied for AuthId `admin`@`%` to database 'mysql'.

So it appears that the issue is a combination of the limited permissions AWS provides and partial revoke. This seems to mean that there are a few ways past this:

1. Petition AWS to allow turning off partial_revoke. This is out of both of our control
2. Create a mysql_grant resource for every database on the system. This is not ideal because of the additional configuration complexity and doesn't allow permissions on databases created in the future.
3. Implement a more complete solution for partial_revoke support in this plugin.
4. Stop managing MySQL users with Terraform. This is obviously not ideal but likely the direction I will go until either solution 1 or 3 are implemented.

While debugging this I actually ran into a more serious problem that should be addressed first. It seems that the update in v3.0.12 causes terraform to hang upon initial plan. In fact, it seems to do the same thing in all versions of your provider(I went back to v3.0.8). Switching to winebarrel/mysql was the only thing that resolved it.

This issue comes when performing a terraform plan on a config that has no resources provisioned(including the RDS Aurora Cluster). I receive the following error messages from Terraform:

2022-03-15T09:36:16.313-0400 [INFO]  provider.terraform-provider-mysql_v3.0.12: 2022/03/15 09:36:16 [TRACE] Waiting 10s before next try: timestamp=2022-03-15T09:36:16.313-0400
2022-03-15T09:36:18.158-0400 [TRACE] dag/walk: vertex "mysql_grant.user_grant (expand)" is waiting for "mysql_user.user (expand)"
2022-03-15T09:36:18.158-0400 [TRACE] dag/walk: vertex "provider[\"registry.terraform.io/petoju/mysql\"] (close)" is waiting for "mysql_grant.user_grant (expand)"
2022-03-15T09:36:18.158-0400 [TRACE] dag/walk: vertex "mysql_user.user (expand)" is waiting for "provider[\"registry.terraform.io/petoju/mysql\"]"
2022-03-15T09:36:21.019-0400 [TRACE] dag/walk: vertex "root" is waiting for "provider[\"registry.terraform.io/petoju/mysql\"] (close)"

These messages continue forever and I am forced to cancel the command. I can only get past it if I comment out the mysql_user and mysql_grant configs, apply the config without them, uncomment them, and re-apply. This seems to indicate it is failing because the MySQL DB is not present, despite a dependency in my provider config.

provider "mysql" {
  endpoint = aws_rds_cluster.db_cluster.endpoint
  username = "admin"
  password = var.admin_password
}

I hope this is helpful and I apologize that I can't be of more assistance. I don't have any experience with Terraform plugin development.

[petoju]

@jonathan-russo Ok, so we found out the issue is in the way we do grants / revokes.

So to address the issue with grants (I don't want to specially consider any implementation right now), I'd like to know these in your situation. Please call them manually by connecting to DB - that will also verify that commands could work.

1. What should happen when creating a grant? Current implementation calls this and success means it went fine:

   mysql> GRANTS SELECT, UPDATE, INSERT ON `database`.`table` TO 'user'@'host';

2. How can one verify the grants are there? Current implementation does this and checks grants there:

   mysql> SHOW GRANTS FOR 'user'@'host';
   +---------------------------------------------------------------------+
   | Grants for user@host                                                |
   +---------------------------------------------------------------------+
   | GRANT USAGE ON *.* TO `user`@`host`                                 |
   | GRANT SELECT, INSERT, UPDATE ON `database`.`table` TO `user`@`host` |
   +---------------------------------------------------------------------+

3. How does one delete grants? Current implementation does this:

   mysql> REVOKE SELECT, INSERT, UPDATE ON `database`.`table` FROM 'user'@'host';

I'm not asking for implementation as you said you have no experience with that. I'm asking you to go over these 3 steps and try creating, verifying and deleting a grant. Please try grants as you'd like them to work (the same set of permissions to the same databases - including * if you wanted to use * somewhere). If you'd like to use ALL PRIVILEGES, just use ALL PRIVILEGES instead of my SELECT, INSERT, UPDATE. Do manual revokes work? What should change?

Notes: as you were able to connect before, I believe that last error was caused by some kind of rate-limiting. I'd skip it for now. I'm also intentionally skipping creation of users as it seems it works fine.

[jonathan-russo]

@petoju

Here is the output from your commands:

mysql> grant SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, CREATE TEMPORARY TABLES ON *.* to `my.user`@`%`;
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for 'my.user'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for my.user@%                                                                                 |
+------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, CREATE TEMPORARY TABLES ON *.* TO `my.user`@`%`  |
| REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, CREATE TEMPORARY TABLES ON `mysql`.* FROM `my.user`@`%` |
+------------------------------------------------------------------------------------------------------+

mysql> REVOKE SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, CREATE TEMPORARY TABLES ON *.* from 'my.user'@'%';
ERROR 3879 (HY000): Access denied for AuthId `admin`@`%` to database 'mysql'.

I don't believe the last error(Terraform hanging) was a rate-limiting error. I believe the connection worked before because the database was already pre-existing. In this case it was not. It appears the plugin can't handle that case.

[petoju]

@jonathan-russo ok, so how should the revoke work? Can you describe a command, that terraform should call?

[jonathan-russo]

@petoju TBH I am not sure. This seems like a failure on AWS's side regarding global privileges. Maybe there is some better way to grant/revoke access in MySQL 8 that I am unaware of?

Only solution from your side I can think of is to enumerate all databases on the system and issue revoke statements against all of them except mysql? Seems a bit hacky though.

If you don't want to do that, perhaps its best to just leave this issue open to communicate to other users that grants/revokes on *.* do not work with this plugin on Aurora 3?

[petoju]

@jonathan-russo

Maybe there is some better way to grant/revoke access in MySQL 8 that I am unaware of?

I believe you'd have to do it per-database to work based on your replies. We could force that for all Aurora users, but they can do it themselves.

Only solution from your side I can think of is to enumerate all databases on the system and issue revoke statements against all of them except mysql?

That would not work properly.

Let's say we start with 3 DBs: secret1, public1, public2 with

GRANT ALL PRIVILEGES ON *.* TO 'user'@'host';
REVOKE ALL PRIVILEGES ON `secret1`.* FROM 'user'@'host';

# This works with these grants even by that user mentioned.
SELECT a FROM public1.tbl;

# You're saying revokes to all the tables would help us - but that's not the case
REVOKE ALL PRIVILEGES ON `public1`.* FROM 'user'@'host';
REVOKE ALL PRIVILEGES ON `public2`.* FROM 'user'@'host';

# Now imagine someone created a database `secret2`.

# Now, the 'user'@'host' can do this - it's not the same state as before granting privileges to *.*
SELECT * FROM secret2.tbl;

If you don't want to do that

I don't want to do that, because it doesn't revoke all permissions that were granted. If there was a solution we could use, we could use that in our implementation.

But yes, grants to *.* may be tricky. Maybe also some other grants with crippled MySQL like Aurora has.