Feature: Support MariaDB roles

[taiidani]

Terraform Version

v0.9.6

Affected Resource(s)

• mysql_grant

Although this is technically a feature request for something like mysql_grant_role, assuming you want to reuse the existing provider for MariaDB-specific functionality.

Terraform Configuration Files

If the existing mysql_grant were used it would look something like this:

resource "mysql_grant" "user" {
  user       = "user"
  host       = "host"
  database   = "app"
  privileges = ["job_developer"]
}

For a brand new resource I'd expect something more like this:

resource "mysql_grant_role" "user" {
  user       = "user"
  host       = "host"
  roles = ["job_developer"]
}

Expected Behavior

Terraform should issue a GRANT ROLE job_developer to user to MariaDB.

Actual Behavior

When using mysql_grant:

* mysql_grant.user: Received #1064 error from MySQL server: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'on app.* TO 'user'@'%'' at line 1"

[apparentlymart]

Hi @taiidani! Thanks for this feature request.

The MySQL provider is not currently an area of focus for the Terraform team at Hashicorp, but we're happy to review any PRs the community may be willing to contribute for this or other MySQL provider features.

This looks like an area where MySQL and MariaDB are entirely incompatible, since MariaDB uses a very different syntax for the GRANT statement. If there is some way we could make mysql_grant just "do the right thing" depending on which server is in use then that would be most ideal, but it seems like the privilege model is pretty different in MariaDB, so maybe that isn't possible. :confounded:

[taiidani]

@apparentlymart Understood on the low focus, and I agree the ROLE feature is entirely MariaDB and has no MySQL equivalent.

If I were to try my hand at a PR, which way would be the best "terraformy" way to go? I see three possible options:

• Upgrade the logic on mysql_grant to detect if...I dunno, the database property is missing? A roles property is present? Either way use the presence/absence of properties to determine which string to pass to MySQL.
• Add a separate mysql_grant_role resource copied from mysql_grant that simply assumes the backend is MariaDB and looks for the relevant properties.
• Create an entirely new provider for MariaDB based on the current MySQL provider.

Given my lack of familiarity with Terraform & Go I could probably only pull off the second option...or maybe the first option...

[apparentlymart]

Unfortunately so far this sort of situation -- what was once a single provider splits into two incompatible forks -- hasn't arisen much, so we don't have a ready pattern for dealing with it. Thinking aloud, the way I'd approach this decision is to see how the community of users of these systems perceive it... is the convention/expectation in the MariaDB community that third-party admin tools are generally built to support both MySQL and MariaDB transparently, or has MariaDB evolved away enough that it usually has its own separate admin tools these days?

There's also a layer of maintainer intent here... is the intention of the MariaDB team that they will remain broadly compatible with MySQL over time, or do they think of it as being a separate product that happens to have a shared history with MySQL?

Answers to both of these questions I think would be the main driver for deciding whether there should be a separate mariadb provider or if the mysql provider should just attempt to support both at once.

Not being in the community I can't quickly answer those questions, but if you're closer and have some thoughts I'd love to hear them.

[taiidani]

Hmm, well MariaDB is strongly advertised as staying protocol-compliant with MySQL. Whether or not they've achieved that is up for debate, but they've kept it compatible enough that all MySQL tools are able to connect to MySQL/MariaDB servers interchangeably.

There are additions though such as new storage engine types, column types, configuration behaviors and the aforementioned role support. Quite a few additions, and more keep getting added every release.

Given the above caveats, to answer your questions simply:

• Yes it's the community expectation for admin tools to be able to support both.
• The original intent of the MariaDB team was (and AFAIK still is) to remain compatible, but given their velocity in adding new features that may not always continue to be their intent.

That doesn't help much on the Terraform side, eh? ¯_(ツ)_/¯

[apparentlymart]

Perhaps the distinction being made here is admin vs. usage? I could understand the argument that MariaDB would remain compatible for the DDL and query statements, but drift away for the sysadmin-ish stuff like users, roles, and settings. If that were true, given that the focus of this Terraform provider is on the sysadmin side of things, I find myself leaning towards a separate provider, though not very strongly.

Adding a new resource and then mentioning clearly in the documentation that mysql_grant is for MySQL only while mysql_grant_role is for MariaDB only seems reasonable for now, until we get a clearer picture of how things are diverging.

It also occurred to me looking at this again that mysql_grant_role reads like a verb, while Terraform resources are normally nouns. So perhaps mysql_role_grant would fit the style better, if you can consider "a grant" to be a kind of object resulting from executing the GRANT command. :man_shrugging:

[taiidani]

Small clarification so you're aware. While MariaDB expects administrators to grant roles to users, it still supports granting privileges to them as well ala the mysql_grant resource. Furthermore it expects the roles themselves (stored in the mysql.users table alongside other user accounts) to be granted privileges so that users who are granted those roles inherit the privileges.

After trying it out I'm definitely leaning towards a separate provider as well. Here are some of the resources I ended up creating.

• mysql_role To manage the role itself
• mysql_role_grant To grant the role itself to the user
• mysql_user_default_role To assign the role as the default when the user logs in. I wanted to add this to mysql_user as a property but it needs both the user to exist and the role granted to it before it can run so I opted for a separate resource to avoid the cyclic dependency.

I still haven't gotten the mysql_grant resource to work on roles though, which is entirely the point (to assign grants on a per-role basis instead of per-user). MariaDB doesn't like the user@host format when GRANTing permissions to roles -- it needs just user.

Here are the tf definitions I used for testing:

# Creates the user
resource "mysql_user" "tester" {
  user     = "tester"
  host     = "%"
  password = "password"
}

# Creates the role
resource "mysql_role" "role_example" {
  role     = "role_example"
}

# Would grant privileges to the created role, but does not work due to the host property messing
# with the GRANT format expected for roles
# resource "mysql_grant" "role_example" {
#   user       = "${mysql_role.role_example.role}"
#   host       = ""
#   database   = "test"
#   privileges = ["SELECT", "UPDATE"]
# }

# Grants the role onto the user, allowing them to call SET ROLE to use it
resource "mysql_role_grant" "tester_role_example" {
  user       = "${mysql_user.tester.user}"
  host       = "${mysql_user.tester.host}"
  role       = "${mysql_role.role_example.role}"
}

# Sets the previous role as the default for the user, automatically SETing it as they log in
resource "mysql_user_default_role" "tester" {
  user     = "${mysql_user.tester.user}"
  host     = "${mysql_user.tester.host}"
  role     = "${mysql_role_grant.tester_role_example.role}"
}

[ghost]

I'm going to lock this issue because it has been closed for 30 days ⏳. This helps our maintainers find and focus on the active issues.

If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.