Support `ALL PRIVILEGES`

[dehume]

Currently the privilege resources do not support ALL PRIVILEGES, the user needs to create a resource object for each desired privilege.

[aidan-melen]

++

[benesch]

@aidan-melen and I discussed this a bit last week ad-hoc.

The caveat I mentioned on the call was that supporting ALL PRIVILEGES means there are now two ways to specify the same privilege for the same role for the same resource. You might have one TF resource that grants SELECT on a table to dennis@materialize.com and another TF resource that grants ALL on a table to dennis@materialize.com. Those resources will now compete over the state of that table's grants. Whereas the current design of the TF provider makes it very difficult to write resources with overlapping role grants.

As a workaround, I suggested writing a module in TF module that wraps up the necessary resources. E.g., you could write a table_all_privileges module that grants all privileges on a table to a role by creating a resource for each privilege (SELECT, INSERT, UPDATE, etc.).

@aidan-melen said he'd tried that with other TF providers but that it made terraform refresh very slow, as each resource's state needs to be refreshed.

Still, on reflection, I would be interested in understanding just how slow the module approach is in Materialize. In theory, TF should be able to refresh resources in parallel and Materialize should be very fast at responding to those resource queries, since the mz_introspection cluster has all the system catalog relations indexed.

[aidan-melen]

There are definitely a lot of challenges to consider when supporting ALL in a SQL based terraform provider (e.g. 318, 1883, etc). Without proper terraform support; operators will likely lean towards a SQL script to grant all privileges outside of terraform where it is supported. Alternatively, we could develop a terraform-provider-materialize-sql that supports arbitrary statements such as this terraform-provider-snowsql example . However, this doesn't prevent a grant select resource from competing with a grant all privileges resource.

There are a few cases in the terraform-aws-provider where resource incompatibilities exists due to the API constraints. For example:

• aws_security_group competes with aws_security_group_rule
• aws_s3_bucket_notification competes with itself.

The maintainers of this provider compromise by placing a big yellow warning banner at the top of the resource documentation. Perhaps it is fair to push this responsibility to the operator rather than adding complexity to this provider.

[benesch]

snowflakedb/terraform-provider-snowflake#318 is gold. Thanks for linking that, @aidan-melen!

My quick take is that we should follow Snowflake's approach here of publishing modules that translate ALL into the constituent resources. I think there's work we could do on the Materialize side to make this fast, if it isn't just super fast by default.

[benesch]

Actually, did Snowflake end up reverting that change? The current Snowflake provider documentation suggests that ALL PRIVILEGES is a valid privilege.

[josharenberg]

FYI for anyone who finds themselves here because they are having this problem, this felt like an acceptable workaround to me:

variable "all_cluster_schema_database_perms" {
  type    = set(string)
  default = [ "USAGE", "CREATE" ]
}

resource "materialize_schema_grant" "prod_schema_to_prod" {
  for_each      = var.all_cluster_schema_database_perms
  role_name     = materialize_role.prodrole.name
  privilege     = "${each.value}"
  database_name = materialize_database.main_database.name
  schema_name   = materialize_schema.prod.name
}

[chaas]

@josharenberg neat! Is this a snippet/guidance we shout put in the terraform docs?

[josharenberg]

Probably! Here's another pattern that I just sorted out, to apply all permissions on all existing tables in a schema... I might turn this into a module or something:

variable "all_table_perms" {
  type    = set(string)
  default = [ "INSERT", "SELECT", "UPDATE", "DELETE" ]
}

data "materialize_table" "prod_tables" {
    database_name = materialize_database.main_database.name
    schema_name = materialize_schema.prod.name
}

locals {
  prod_table_perms = {
    for pair in setproduct(flatten([for table in data.materialize_table.prod_tables.tables : table.name]), var.all_table_perms) :
      "${pair[0]}-${pair[1]}" => {
        table_name  = pair[0]
        permission  = pair[1]
      }
  }
}

resource "materialize_table_grant" "prod_table_grant" {
    for_each = local.prod_table_perms
    table_name = each.value.table_name
    database_name = materialize_database.main_database.name
    role_name = materialize_role.prodrole.name
    privilege = each.value.permission
    schema_name = materialize_schema.prod.name
}