[ISSUE] Issue with `databricks_sql_visualization` resource - changes to `option` from `inline` to `file()` strips permissions from referenced `sql_query` resource

[crankswagon]

trafficstars

when attempting to move databricks_sql_visualization.options from inline json to a file reference:

• terraform plan succeeds
• terraform apply fails to apply databricks_sql_visualization but also strips permissions from databricks_sql_query and breaks terraform state (subsequent terraform plan is not able to accss the referenced query)

Configuration

note that this bug was seen when moving from current state to desired state (both configurations are shown below)

current_state

### current_state
## creates a query for every .sql file in databricks/jobs/redacted/analytics/dashboards
resource "databricks_sql_query" "redacted_analytics_queries" {
  for_each       = fileset("${path.module}/jobs/redacted/analytics/dashboards", "*.sql")
  data_source_id = data.terraform_remote_state.dataeng-deltalake-platform.outputs.redacted-analytics-warehouse-data-source-id
  name           = split(".", "${each.value}")[0]
  query          = file("${path.module}/jobs/redacted/analytics/dashboards/${each.value}")
  run_as_role    = "viewer"

  parameter {
    name  = "Vertical"
    title = "Vertical"
    enum {
      options = ["a", "b", "c"]
      values   = ["a"]

      multiple {
        prefix    = "\""
        suffix    = "\""
        separator = ","
      }
    }
  }

}

resource "databricks_permissions" "redacted_analytics_queries-permissions" {
  for_each     = databricks_sql_query.redacted_analytics_queries
  sql_query_id = each.value.id

  access_control {
    group_name       = data.terraform_remote_state.dataeng-deltalake-platform.outputs.dataeng-group
    permission_level = "CAN_RUN"
  }
}

resource "databricks_sql_visualization" "test_pie" {
  query_id    = databricks_sql_query.redacted_analytics_queries["rpt_test.sql"].id
  type        = "CHART"
  name        = "Test Pie"
  description = "Derp"

  # The options encoded in this field are passed verbatim to the SQLA API.

  # in sql.queries.create_new_visualization context, open the network tab in your browser (F12) and look for `post` calls with `visualizations` file
  # then copy out the `options` object from request payload and chuck it in here verbatim
  options = jsonencode(
    {
      "alignYAxesAtZero" : false,
      "coefficient" : 1,
      "columnConfigurationMap" : {
        "series" : {
          "column" : "AccountId",
          "id" : "column_7915eaf48"
        },
        "x" : {
          "column" : "vertical",
          "id" : "column_7915eaf47"
        },
        "y" : [
          {
            "column" : "dollar_ammount",
            "id" : "column_7915eaf49",
            "transform" : "SUM"
          }
        ]
      },
      "dateTimeFormat" : "YYYY-MM-DD HH:mm",
      "direction" : {
        "type" : "counterclockwise"
      },
      "error_y" : {
        "type" : "data",
        "visible" : true
      },
      "globalSeriesType" : "pie",
      "legend" : {
        "traceorder" : "normal"
      },
      "missingValuesAsZero" : true,
      "numberFormat" : "0,0[.]00000",
      "percentFormat" : "0[.]00%",
      "series" : {
        "error_y" : {
          "type" : "data",
          "visible" : true
        },
        "stacking" : "stack"
      },
      "seriesOptions" : {
        "column_7915eaf49" : {
          "name" : "dollar_ammount",
          "type" : "pie",
          "yAxis" : 0
        }
      },
      "showDataLabels" : true,
      "sizemode" : "diameter",
      "sortX" : true,
      "sortY" : true,
      "swappedAxes" : false,
      "textFormat" : "",
      "useAggregationsUi" : true,
      "valuesOptions" : {},
      "version" : 2,
      "xAxis" : {
        "labels" : {
          "enabled" : true
        },
        "type" : "-"
      },
      "yAxis" : [
        {
          "type" : "-"
        },
        {
          "opposite" : true,
          "type" : "-"
        }
      ]
    }
  )
}

moving to desired state

## creates a query for every .sql file in databricks/jobs/redacted/analytics/dashboards
resource "databricks_sql_query" "redacted_analytics_queries" {
  for_each       = fileset("${path.module}/jobs/redacted/analytics/dashboards", "*.sql")
  data_source_id = data.terraform_remote_state.dataeng-deltalake-platform.outputs.redacted-analytics-warehouse-data-source-id
  name           = split(".", "${each.value}")[0]
  query          = file("${path.module}/jobs/redacted/analytics/dashboards/${each.value}")
  run_as_role    = "viewer"

  parameter {
    name  = "Vertical"
    title = "Vertical"
    enum {
      options = ["a", "b", "c"]
      values   = ["a"]

      multiple {
        prefix    = "\""
        suffix    = "\""
        separator = ","
      }
    }
  }

}

resource "databricks_permissions" "redacted_analytics_queries-permissions" {
  for_each     = databricks_sql_query.redacted_analytics_queries
  sql_query_id = each.value.id

  access_control {
    group_name       = data.terraform_remote_state.dataeng-deltalake-platform.outputs.dataeng-group
    permission_level = "CAN_RUN"
  }
}

resource "databricks_sql_visualization" "test_pie" {
  query_id    = databricks_sql_query.redacted_analytics_queries["rpt_test.sql"].id
  type        = "CHART"
  name        = "Test Pie"
  description = "Derp"

  # The options encoded in this field are passed verbatim to the SQLA API.

  # in sql.queries.create_new_visualization context, open the network tab in your browser (F12) and look for `post` calls with `visualizations` file
  # then copy out the `options` object from request payload and chuck it in here verbatim
  options = jsonencode(file("${path.module}/jobs/aml/analytics/visualization/rpt_viz.json")) ### @databricks, this change broken our terraform state
}

rpt_test.sql file referenced in the configuration, stored in ${path.module}/jobs/redacted/analytics/dashboards

SELECT
'{{ Vertical }}' as vertical
,{{ AccountId }} as AccountId
,1000 * rand() as dollar_ammount

rpt_viz.json file referenced in the configuration, stored in ${path.module}/jobs/redacted/analytics/visualization

{
    "alignYAxesAtZero" : false,
    "coefficient" : 1,
    "columnConfigurationMap" : {
      "series" : {
        "column" : "AccountId",
        "id" : "column_7915eaf48"
      },
      "x" : {
        "column" : "vertical",
        "id" : "column_7915eaf47"
      },
      "y" : [
        {
          "column" : "dollar_ammount",
          "id" : "column_7915eaf49",
          "transform" : "SUM"
        }
      ]
    },
    "dateTimeFormat" : "YYYY-MM-DD HH:mm",
    "direction" : {
      "type" : "counterclockwise"
    },
    "error_y" : {
      "type" : "data",
      "visible" : true
    },
    "globalSeriesType" : "pie",
    "legend" : {
      "traceorder" : "normal"
    },
    "missingValuesAsZero" : true,
    "numberFormat" : "0,0[.]00000",
    "percentFormat" : "0[.]00%",
    "series" : {
      "error_y" : {
        "type" : "data",
        "visible" : true
      },
      "stacking" : "stack"
    },
    "seriesOptions" : {
      "column_7915eaf49" : {
        "name" : "dollar_ammount",
        "type" : "pie",
        "yAxis" : 0
      }
    },
    "showDataLabels" : true,
    "sizemode" : "diameter",
    "sortX" : true,
    "sortY" : true,
    "swappedAxes" : false,
    "textFormat" : "",
    "useAggregationsUi" : true,
    "valuesOptions" : {},
    "version" : 2,
    "xAxis" : {
      "labels" : {
        "enabled" : true
      },
      "type" : "-"
    },
    "yAxis" : [
      {
        "type" : "-"
      },
      {
        "opposite" : true,
        "type" : "-"
      }
    ]
  }

Expected Behavior

no change should have been detected, as the options values are the same, it was just to facilitate easier organisation of visulization configs in our repo

Actual Behavior

terraform plan succeeds terraform apply fails and locks up state and appears to strip permissions from the referenced sql-query

even account admin were no longer able to access the sql-query, but it was still visible in workspace UI

error shown during subsequent terraform plan is simply Internal Server Error

it required manual edits of the state file for us to restore

Steps to Reproduce

1. terraform apply state defined in current state hcl file
2. terraform apply state defined in desired state hcl file

Terraform and provider versions

version 1.2.1; there does not look to be any changes to this resource between 1.2.1 and 1.3.0 at first glance https://github.com/databricks/terraform-provider-databricks/releases/tag/v1.3.0

Debug Output

TF_LOG=TRACE or TF_LOG=DEBUG supplies no additional debug information other than internal server error

Important Factoids

we submitted a support ticket with databricks and this behaviour was replicated by support team

[nkvuong]

@crankswagon looks like this is an issue with the databricks_permissions resource. Could you validate this by creating a databricks_query without databricks_permission, and then refactoring it and see if the error appears? The other test is to create a simple databricks_query with databricks_permission, re-run terraform plan to see if the error appears.

If the above is correct, then it is similar to #1504

[crankswagon]

hi @nkvuong, thank you for the reply and suggestion; i've just had another play with this in our dev environment and it does not appear to be related to databricks_permissions resource, but rather to the validation of value passed in options in the databricks_sql_visualization resource

what appears to have broken our state was an additional (unnecessary) call to the jsonencode function

jsonencode(file("${path.module}/jobs/redacted/analytics/visualization/rpt_viz.json"))

rather than the correct declaration (as terraform will translate this to an jsonencode during plan/apply):

file("${path.module}/jobs/redacted/analytics/visualization/rpt_viz.json")

I tested running terraform plan on a test.tf with a file() ref to an existing state applied by an inline {}, the resource runs correctly and calculates no diff.

However, when I tested running terraform plan on a test.tf with jsonencode(file()) to the same existing state applied by inline {}, i get the following diff breaking_change.log (it's not super obvious but notice the double jsonencode call)

i went back and looked at our terraform cloud logs, and found the same behavior in the run that broke our state

so in summary, the resource is behaving as expected if using the correct file() ref, but the bug is that it is missing some validations to prevent typos from breaking terraform state during an apply (though i'm not sure whether that validation should be done by backend APIs or by the provider module)

[nkvuong]

@crankswagon that makes sense, the proper fix will be to add validations on the backend APIs - basically options should be JSON formatted string, which file() would already return.

we'll track the API bug internally, but will also add an example to the doc for clarity

[crankswagon]

thank you @nkvuong, i'm happy to contribute a docs PR with an example

[crankswagon]

hi @nkvuong , i was thinking through this again and had a closer look at the issue you linked initially #1504 ; perhaps that is actually the root cause of the permission stripping. And lack of validation of options in the visualization resource is just a surface level issue. Because a failed apply of visualization resource shouldn't be stripping permissions for the sql_query it is referencing. I didn't think it was related because we were able to create everything with the permission attached when everything is correctly declared in vis module; but maybe the bug is that permission stripping only happens when an apply does not fail gracefully.

Looking through the permission module referenced in #1504 , is there a reason to limit ensureCurrentUserCanManageObject to only certain API prefixes with shouldExplicitlyGrantCallingUserManagePermissions? i would think that the caller role (in most cases a service user account that only CI uses) should always be allowed to manage resources as that role owns the resource?

[crankswagon]

just an update to this bug -> it seems that any query resource that has been historically impacted by this cannot have its name re-used (even when the resource id changes)

expanding on this, the current work around for any sql-query that has had its permission stripped is to just delete any reference to that id from terraform state file; but this does not remove the query from the workspace, which means it's just left in a broken state with no permission for anyone on the account to modify/delete it. (we haven't tried removing it with an API call yet though)