Grants Non-Deterministic

[dehume]

Grants are difficult to manage in Terraform because unlike other resources, they do not maintain a single id within an system catalog. Instead grant ids are maintained as a concatenation of the relevant roles, permission and objects. As a result, users are able to create multiple grants that are duplicates.

resource "materialize_table_grant_default_privilege" "de_update" {
  privilege        = "UPDATE"
  grantee_name     = "de"
  target_role_name = materialize_role.target_complex.name
  database_name    = materialize_database.database.name
  schema_name      = materialize_schema.schema.name
}

resource "materialize_table_grant_default_privilege" "de_insert" {
  privilege        = "INSERT"
  grantee_name     = "de"
  target_role_name = materialize_role.target_complex.name
  database_name    = materialize_database.database.name
  schema_name      = materialize_schema.schema.name
}

resource "materialize_table_grant_default_privilege" "duplicate" {
  privilege        = "UPDATE"
  grantee_name     = "de"
  target_role_name = materialize_role.target_complex.name
  database_name    = materialize_database.database.name
  schema_name      = materialize_schema.schema.name
}

When applied this will be successful and grant the correct privileges.

SELECT * FROM mz_default_privileges where grantee = ‘u12’;
 role_id | database_id | schema_id | object_type | grantee | privileges
---------+-------------+-----------+-------------+---------+------------
 u8      | u2          | u7        | table       | u12     | aw
(1 rows)

However deleting the duplicate resource will revoke the UPDATE privilege even though there is still a resource privilege defining that permission

SELECT * FROM mz_default_privileges where grantee = ‘u12’;
 role_id | database_id | schema_id | object_type | grantee | privileges
---------+-------------+-----------+-------------+---------+------------
 u8      | u2          | u7        | table       | u12     | a
(1 rows)

This can be particularly difficult for users with more complex Terraform projects that dynamically map resources.