search

petoju / terraform-provider-mysql

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

mysql_grant failing to grant permission since version 3.0.23 on mysql 8.0.28 (with partial revokes ON) #47

Closed av-mathiaspius closed 1 year ago

av-mathiaspius commented 1 year ago

Hi there, I've encountered an issue on versions 3.0.23+ targeting mysql 8.0.28 with partial_revokes on, where attempting to create even narrowly defined grants fails. 3.0.22 and earlier appear to work fine.

Terraform Version

Terraform v1.2.4 on linux_amd64 petoju/mysql 3.0.23+

Affected Resource(s)

Terraform Configuration Files

resource "mysql_grant" "this" {
  user       = "dbusername"
  host       = "%"
  database   = "databasename"
  privileges = ["SELECT"]
}

Debug Output

I'll include the abbreviated debug output, since I'm somewhat certain where the issue is occurring:

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

With TF_LOG=trace enabled, this also appears in the log:

2022-11-10T19:03:33.657Z [INFO]  provider.terraform-provider-mysql_v3.0.23: 2022/11/10 19:03:33 Executing statement: GRANT SELECT ON `databasename`.* TO 'dbusername'@'%': timestamp=2022-11-10T19:03:33.657Z
2022-11-10T19:03:33.700Z [INFO]  provider.terraform-provider-mysql_v3.0.23: 2022/11/10 19:03:33 [WARN] GRANT not found for 'dbusername'@'%' (%!s(<nil>)) - removing from state: timestamp=2022-11-10T19:03:33.700Z

More context here

Expected Behavior

I expected the grant to be created.

Actual Behavior

The above error occurred.

Steps to Reproduce

Attempt to create the mysql_grant as described above on a MySQL 8.0.28 instance with partial_revokes = ON using a version of the provider equal to or newer than 3.0.23.

Important Factoids

I was able to narrow the issue down to the change between 3.0.22 and 3.0.23. That is to say, 3.0.22 works as expected, but 3.0.23 and 3.0.24 crashes as shown above. I haven't had time to dive into the deeper logic surrounding this, but a cursory glance suggests maybe this section of the diff could be the culprit. It looks like the scope might be closing before the following code, rather than encompassing it, meaning the grant length code proceeds, even if an error has occurred.

References

Seems to be related to, or a continuation of the partial_revokeissues identified in this issue: https://github.com/petoju/terraform-provider-mysql/issues/3

petoju commented 1 year ago

Your description is missing something

mysql> select @@global.partial_revokes;
+--------------------------+
| @@global.partial_revokes |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0,00 sec)

For me, it applies well https://gist.github.com/petoju/5c64ed802e9cd155326e954ba0fc588d

Terraform: https://gist.github.com/petoju/f83e73880a67fe88dca8aa3422f7cad9

av-mathiaspius commented 1 year ago

Here's the partial_revokes from the database I am using:

MySQL [(none)]> select @@global.partial_revokes;
+--------------------------+
| @@global.partial_revokes |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.018 sec)

I just tried running your terraform against the database again, only changing the provider. Here's the gist with the logs from the respective runs: https://gist.github.com/av-mathiaspius/e01775f4430f2bfe19e5761f67915bce

The database in question is provided by a third party, so I can't rule out that there's some kind of configuration setting or privilege which my user is missing, which is causing this issue.

etchevef commented 1 year ago

I just got the exact same error happen to me, with :

 % terraform version
Terraform v1.3.7
on linux_amd64
+ provider registry.terraform.io/hashicorp/helm v2.8.0
+ provider registry.terraform.io/ovh/ovh v0.26.0
+ provider registry.terraform.io/petoju/mysql v3.0.28
+ provider registry.terraform.io/terraform-provider-openstack/openstack v1.49.0

mysql version 8, deployed by OVH.

I did manage to manually grant privilege with the same configured user with : 

MySQL [(none)]> GRANT ALL PRIVILEGES ON maela_identity.* to 'user_identity'@'%' WITH GRANT OPTION;
MySQL [(none)]> show grants for user_identity;
+-------------------------------------------------------------------------------------+
| Grants for user_identity@%                                                          |
+-------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO "user_identity"@"%"                                           |
| GRANT ALL PRIVILEGES ON "maela_identity".* TO "user_identity"@"%" WITH GRANT OPTION |
+-------------------------------------------------------------------------------------+

so it looks like it should work

petoju commented 1 year ago

To debug this, we need at least some of these:

  1. At least somewhat skilled programmed to try the exact query and find the cause. Ideally someone able to write a simple test case with docker-compose.
  2. Configuration you are trying to apply.
  3. Terraform logs showing what was executed.
  4. State of MySQL before and after applying terraform. That means grants via SHOW GRANTS FOR .... The identity after FOR should match whatever is provided in your configuration.
  5. Manual execution of GRANT if it does not work.

Especially, I have no idea, where the issue is: a) is it when GRANTing rights? b) is it when verifying the GRANT is in place?

etchevef commented 1 year ago

1 - According to the logs, exact query is : GRANT ALL ON `db_identity`.* TO 'user_identity'@'%' 2 - see attached files 3 - see attached file 4 - State of mysql before : 

MySQL [(none)]> show grants for user_identity;
+-------------------------------------------+
| Grants for user_identity@%                |
+-------------------------------------------+
| GRANT USAGE ON *.* TO "user_identity"@"%" |
+-------------------------------------------+
1 row in set (0.015 sec)

State after : 

MySQL [(none)]> show grants for user_identity;
+-------------------------------------------------------------------+
| Grants for user_identity@%                                        |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO "user_identity"@"%"                         |
| GRANT ALL PRIVILEGES ON "maela_identity".* TO "user_identity"@"%" |
+-------------------------------------------------------------------+
2 rows in set (0.016 sec)

If needed, the user used to configure the provider : 

MySQL [(none)]> show grants for user_dba;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for user_dba@%                                                                                                                                                                                                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 ON *.* TO "user_dba"@"%" WITH GRANT OPTION |
| GRANT REPLICATION_APPLIER,ROLE_ADMIN ON *.* TO "user_dba"@"%" WITH GRANT OPTION                                                                                                                                                                                                                                                    |
| REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "metrics_user_telegraf".* FROM "user_dba"@"%"                                                                                       |
| REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "mysql".* FROM "user_dba"@"%"                                                                                                       |
| REVOKE INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON "sys".* FROM "user_dba"@"%"                                                                                                         |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.017 sec)
MySQL [(none)]> select @@global.partial_revokes;
+--------------------------+
| @@global.partial_revokes |
+--------------------------+
|                        1 |
+--------------------------+
1 row in set (0.016 sec)

5 - looks like it does work on the database side

logs.txt database.tf.txt provider.tf.txt

petoju commented 1 year ago

@etchevef thanks for the logs and all the commands. It looks double quotes could be to blame.

I released provider 3.0.29, that should fix that and it also adds more detailed logging so we should be able to pinpoint the issue. Or at least come closer to it.

Could you test it, please?

etchevef commented 1 year ago

Yup, upgrade to 3.0.29 solved the problem for me. Thanks for your reactivity !

petoju commented 1 year ago

@etchevef also thanks for assistance! Closing.