Proposal: grantee-owned privilege/role sets (`grants` / `roles` on `user` and `role`)

[JTCunning]

Current Design Flaws

• Two independent clickhousedbops_grant_privilege stanzas can target the same grantee and silently overlap/conflict. Overlap detection exists, but only per-resource at create time — there is no authoritative view of the whole set. (clickhouse cloud: The grant operation was successful but it didn't create the expected entry in system.grants table #105, The grant operation was successful but it didn't create the expected entry in system.grants table within a cluster #92)
• Granting N privileges to a grantee means N resources and N+ round trips to the server. (clickhousedbops_grant_privilege: reduce query count at scale #194, Granting CREATE or DROP privileges leads to context deadline exceeded #170)
• Revoking is implicit (delete the resource), and the "what does this user actually have" question can't be answered or validated from a single place. (User being recreated does not always force role to be reassigned #125)

Proposed Solution

1. Reusable privilege bundles are not a managed resource. A bundle holds only privileges + flags and never names a grantee, so it has no server-side object to create/read/update/delete. Model it as a data source (data "clickhousedbops_grant_privileges", akin to aws_iam_policy_document) that assembles and validates a privilege list, or simply as typed locals. Modeling it as a resource would be a "resource that manages nothing".

2. Grantees own their sets. Add to both clickhousedbops_user and clickhousedbops_role:

   • grants — the union of one or more privilege bundles (replaces scattered
     clickhousedbops_grant_privilege resources).
   • roles — role memberships (GRANT role TO grantee, replaces scattered clickhousedbops_grant_role resources), carrying admin_option per entry.

3. The grantee is where validation + batching happen. Because the grantee owns the full union, the provider can (a) reject conflicting/overlapping privileges before issuing SQL and (b) collapse the set into a minimal batch of GRANT/REVOKE statements. Removing an entry becomes a revoke; the resource is authoritative over that grantee's access.

This keeps bundles grantee-agnostic (define once, attach to a user or a role) and makes the symmetric model: every grantee owns a validated, batched grants set and roles set.

Example

data "clickhousedbops_grant_privileges" "readonly_analytics" {
  privilege {
    privilege_name = "SELECT"
    database_name  = "analytics"
  }
  privilege {
    privilege_name = "SHOW TABLES"
    database_name  = "analytics"
  }
}

data "clickhousedbops_grant_privileges" "etl_writer" {
  privilege {
    privilege_name = "INSERT"
    database_name  = "analytics"
    table_name     = "events"
    grant_option   = true
  }
  privilege {
    privilege_name = "ALTER UPDATE"
    database_name  = "analytics"
    table_name     = "events"
  }
}

data "clickhousedbops_grant_privileges" "user_admin" {
  privilege {
    privilege_name = "CREATE USER"
    user_name      = "session-*"
    grant_option   = true
  }
  privilege {
    privilege_name = "SHOW USERS"
  }
}

resource "clickhousedbops_role" "base_reader" {
  name   = "base_reader"
  grants = data.clickhousedbops_grant_privileges.readonly_analytics.privileges
}

resource "clickhousedbops_role" "analyst" {
  name   = "analyst"
  grants = data.clickhousedbops_grant_privileges.readonly_analytics.privileges
  roles  = [{ name = clickhousedbops_role.base_reader.name }]
}

resource "clickhousedbops_role" "etl" {
  name = "etl"
  grants = concat(
    data.clickhousedbops_grant_privileges.readonly_analytics.privileges,
    data.clickhousedbops_grant_privileges.etl_writer.privileges,
  )
  roles = [{ name = clickhousedbops_role.base_reader.name }]
}

resource "clickhousedbops_user" "john" {
  name                            = "john"
  password_sha256_hash_wo         = sha256("changeme")
  password_sha256_hash_wo_version = 1
  grants                          = data.clickhousedbops_grant_privileges.user_admin.privileges
  roles = [
    { name = clickhousedbops_role.analyst.name },
    { name = clickhousedbops_role.etl.name, admin_option = true },
  ]
}

resource "clickhousedbops_user" "jane" {
  name                            = "jane"
  password_sha256_hash_wo         = sha256("changeme")
  password_sha256_hash_wo_version = 1
  roles                           = [{ name = clickhousedbops_role.analyst.name }]
}

Benefits

• A grantee's full access lives in one place and is evaluated as a set.
• Conflict/overlap validation across the union, before any SQL is emitted.
• Fewer round trips; grants applied in a batch per grantee.
• Reusable, named bundles shared across users and roles (single source of truth).
• Removing an entry is an explicit revoke.

Open questions

• Are roles entries plain strings or objects? Objects are required to carry admin_option per membership (example above uses objects).
• Data source vs. plain locals for bundles: do we want named bundles that appear in plan output and can validate within a bundle, or is config composition enough?
• Authoritative ownership: once a grantee is managed this way, anything granted out-of-band (or by a legacy grant_privilege/grant_role resource) would be revoked/moved-blocked. Needs to be loud in docs and likely opt-in, or most likely invalidate any runs that combine both old and new usages.
• Should cluster_name live on the grantee, the bundle, or both?
• Does grant_option / scope validation (GLOBAL/COLUMN/USER_NAME/…) move into the bundle's validation, the grantee's plan-time validation, or both? (clickhousedbops_grant_privilege rejects all-databases scoping for COLUMN/DICTIONARY/VIEW-scoped privileges. #202, Support the ability to control USER and ROLE grants through clickhousedbops_grant_privilege. #201)

Backwards compatibility

• Additive: existing clickhousedbops_grant_privilege and clickhousedbops_grant_role resources remain but receive a deprecation warning. The new grants/roles
  attributes are opt-in.
• A grantee should not be managed by both the authoritative sets and standalone grant resources simultaneously; document and ideally detect/deny this.

Alternatives considered

• privilege {} / revoke {} blocks directly on clickhousedbops_grant_privilege. Cleaner than a generic dynamic block, but still lets multiple independent stanzas target the same grantee and conflict, and keeps privileges grantee-bound rather than reusable.
• A clickhousedbops_grant_privileges resource holding only grants+flags. Rejected: with no grantee it manages nothing server-side, so it shouldn't be a managed resource.