creating user on aws rds mysql 8.0.35

[russell-morris-BAT]

Just looking for some advice, I am trying to create a user with the mysql user resource, but using the caching_sha2_password authentication method.

I cannot alter the default authentication method as aws has made that setting non accessible to users.

I have tried the following:

resource "mysql_user" "user_russell_morris_alt" { user = "user_russell_morris_altpass" auth_plugin = caching_sha2_password auth_string_hashed = password value from our vault (plain text) host = "%" }

resource "mysql_user" "user_russell_morris_alt" { user = "user_russell_morris_altpass" auth_plugin = caching_sha2_password auth_string_hashed = hashing the password using sha2(password, 256) host = "%" }

consistently facing Error: failed executing SQL: Error 1827 (HY000): The password hash doesn't have the expected format.

Am i using the wrong parameter combination or does it require the default authentication being altered at the RDS level?

Thank you for you patience

[petoju]

@russell-morris-BAT ah, I see there are conflicts between how to create the password.

In any case, my guess is that the hash is incorrect. I believe MySQL doesn't use just "straight" sha2 hash - you can try base64decode("JEEkMDA1JAxeeUA6dxZPZwUNBCceLToWME9BUC8vUzQ3SGZVbWJBSGFCVUdxaDZIV05pSzJvcUNXdHU0NmgyQkZRNnFkQQ==") that is password "password123". But I don't have a function to generate this - I just generated it in Docker container and then dumped it.

Or you could just set your password in plaintext_password without specifying auth_plugin.

Or we could implement having plaintext password with selected auth plugin, but that's a bit more work.

[russell-morris-BAT]

Hey @petoju thanks for the reply

I will try the base64encode way. Unfortunately not specifying the plug in is not possible because the default in rds is to use the native plugin.

After discussing with Amazon there's currently no plans to alter the default plug in used.

I will come back to you with the results.

[petoju]

@russell-morris-BAT you can use base64, but getting the hash will be complicated (except that hash that I sent you).

The best idea would be to extend the provider, but it needs some time and tests.

[Tenzer]

I don't think it would be a lot of work to support creating users authenticated via the cached_sha2_password auth plugin. It can be used with plaintext_password so MySQL can take care of hashing the password, in which case the necessary change for the mysql_user resource probably only is something like this:

diff --git mysql/resource_user.go mysql/resource_user.go
index 583a98e4..48aeb083 100644
--- mysql/resource_user.go
+++ mysql/resource_user.go
@@ -58,7 +58,7 @@ func resourceUser() *schema.Resource {
                                Optional:         true,
                                ForceNew:         true,
                                DiffSuppressFunc: NewEmptyStringSuppressFunc,
-                               ConflictsWith:    []string{"plaintext_password", "password"},
+                               ConflictsWith:    []string{"password"},
                        },

                        "aad_identity": {
@@ -193,6 +193,9 @@ func CreateUser(ctx context.Context, d *schema.ResourceData, meta interface{}) d

        if authStm != "" {
                stmtSQL = stmtSQL + authStm
+               if password != "" {
+                       stmtSQL = stmtSQL + fmt.Sprintf(" BY '%s'", password)
+               }
        } else if password != "" {
                stmtSQL = stmtSQL + fmt.Sprintf(" IDENTIFIED BY '%s'", password)
        }

Does that look reasonable? If yes, I can look at making a PR with the changes and adding tests for it.

[petoju]

@Tenzer If I were changing it, I'd add something similar to the block

if v, ok := d.GetOk("auth_string_hashed"); ok {

under that block to assign authStm differently. That's the first part that you also tackled.

The second part is changing how to do UpdateUser - currently, we change password with auth_plugin change - but we would also need to build the change password query.

Besides that, it's mostly fine if tests will pass - if they won't, they should be fixed and not deleted.