CREATE DATABASE FROM SHARE failing with "SQL compilation error: Share .. does not exist or not authorized."

[chrisweis]

Important Note: Please read my "Additional Information" section below first

Terraform CLI and Provider Versions

Terraform v1.6.6
on darwin_arm64
+ provider registry.terraform.io/snowflake-labs/snowflake v0.87.0

Your version of Terraform is out of date! The latest version
is 1.7.4. You can update by downloading from https://www.terraform.io/downloads.html

Terraform Configuration

resource "snowflake_database" "PRD_REDACTED_DB" {
  # create DATABASE IDENTIFIER('"PRD_REDACTED_DB"') FROM SHARE IDENTIFIER('PUBLISHER.REDACTED."PUBLISHER_RAW_EVENTS_REDACTED"');
  name     = "PRD_REDACTED_DB"
  comment  = "Contact: REDACTED.  A Snowflake data share purchased from redacted in early 2024 that provides access to data for analysis of redacted. In redacted this is configured at Menu > Option.  Documentation: https://www.redacted.com/docs/"
  from_share = {
    provider = "PUBLISHER.REDACTED"
    share    = "PUBLISHER_RAW_EVENTS_REDACTED"
  }
}

Expected Behavior

I expect the Terraform provider to generate the same SQL generated when I use the Snowsight GUI to attach this share:

create DATABASE IDENTIFIER('"PRD_REDACTED_DB"') FROM SHARE IDENTIFIER('PUBLISHER.REDACTED."PUBLISHER_RAW_EVENTS_REDACTED"');

Actual Behavior

CREATE DATABASE "PRD_REDACTED_DB" FROM SHARE "PUBLISHER.REDACTED"."PUBLISHER_RAW_EVENTS_REDACTED" COMMENT = 'Contact: REDACTED.  A Snowflake data share purchased from redacted in early 2024 that provides access to data for analysis of redacted. In redacted this is configured at Menu > Option.  Documentation: https://www.redacted.com/docs/'

Snowflake error: SQL compilation error: Share '"PUBLISHER.REDACTED.PUBLISHER_RAW_EVENTS_REDACTED"' does not exist or not authorized.

The Terraform apply:

Terraform used the selected providers to generate the following execution plan. Resource actions are indicated with the following symbols:
  + create

Terraform will perform the following actions:

  # snowflake_database.PRD_REDACTED_DB will be created
  + resource "snowflake_database" "PRD_REDACTED_DB" {
      + comment                     = "Contact: REDACTED.  A Snowflake data share purchased from redacted in early 2024 that provides access to data for analysis of redacted. In redacted this is configured at Menu > Option.  Documentation: https://www.redacted.com/docs/"
      + data_retention_time_in_days = -1
      + from_share                  = {
          + "provider" = "PUBLISHER.REDACTED"
          + "share"    = "PUBLISHER_RAW_EVENTS_REDACTED"
        }
      + id                          = (known after apply)
      + is_transient                = false
      + name                        = "PRD_REDACTED_DB"
    }

Plan: 1 to add, 0 to change, 0 to destroy.

------------------------------------------------------------------------

Cost Estimation:

Resources: 0 of 56 estimated
           $0.0/mo +$0.0

------------------------------------------------------------------------

Do you want to perform these actions in workspace "redacted"?
  Terraform will perform the actions described above.
  Only 'yes' will be accepted to approve.

  Enter a value: yes

snowflake_database.PRD_REDACTED_DB: Creating...
╷
│ Error: error creating database PRD_REDACTED_DB: [errors.go:17] object does not exist or not authorized
│ 
│   with snowflake_database.PRD_REDACTED_DB,
│   on database.tf line 21, in resource "snowflake_database" "PRD_REDACTED_DB":
│   21: resource "snowflake_database" "PRD_REDACTED_DB" {
│ 
╵
Operation failed: failed running terraform apply (exit 1)

Steps to Reproduce

1. Confirm that Terraform has ACCOUNTADMIN rights (no permissions issues) and the share has been dropped.
2. Run terraform apply
3. Copy the SQL from Snowflake query monitoring and re-run it using other ACCOUNTADMIN account and confirm it gets the same error.

How much impact is this issue causing?

Medium

Logs

https://gist.github.com/chrisweis/68870058eb1ba7fe2cf3bd83a79cceb3

Additional Information

Ah!! Just before submitting this bug I realized that this combination enabled it to finally work.

resource "snowflake_database" "PRD_REDACTED_DB" {
  name     = "PRD_REDACTED_DB"
  from_share = {
    provider = "PUBLISHER\".\"REDACTED"
    share    = "PUBLISHER_RAW_EVENTS_REDACTED"
  }
}

Request: Since this took me a LONG time to discover, could we please improve the documentation for the database resource to clarify this so it's easier to understand and remember? This is the example in the docs that steered me in a challenging direction:

resource "snowflake_database" "from_share" {
  name    = "testing_4"
  comment = "test comment"
  from_share = {
    provider = "org1.account1"
    share    = "share1"
  }
}

Thanks!

Related issues:
#2168
#2277
#1770

[sfc-gh-asawicki]

Hey @chrisweis. Thanks for reporting the issue.

We will address database resource soon as part of https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#supporting-all-snowflake-ga-features. We have improving identifiers on our roadmap too https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/ROADMAP.md#identifiers-rework. We will address it then.

[hhobson]

Also hit this issue. It's a regression that was introduced in version 0.87.0, I believe as part of #2524.

Shares can be created without issue when using version 0.86.0

[hhobson]

@sfc-gh-asawicki as this is a code regression are you able to look into this issue?

We'd like to upgrade our version to make use of other fixes you've added, can't as this causes errors for new stages

[sfc-gh-asawicki]

Hey @hhobson, @chrisweis. Please take a look at https://github.com/Snowflake-Labs/terraform-provider-snowflake/blob/main/MIGRATION_GUIDE.md#behavior-change-external-object-identifier-changes, there was a change in what we expect in the from_share.provider, it was earlier working "by accident".

Also, the workaround described by @chrisweis should temporarily work but it is also working like that unintentionally, so it may change with the future versions of the provider.

[denzhel]

Saved us from burning a good few hours, thanks @chrisweis !

To automate the fix, you can use the following:

from_share = {
    provider = replace(each.value.from_share.provider, ".", "\".\"")
    share    = each.value.from_share.share
  }

❤️

[sfc-gh-jcieslak]

Hi all 👋
The new version (v0.93.0) contains the new database resources. If you can, please try to migrate to/import the snowflake_shared_database. It has a slightly different structure for specifying from_share and should be more stable than the previous version. Closing the ticket as snowflake_database now represents the standard database and the snowflake_database_old (previously snowflake_database) is no longer supported. If you encounter any issues with the new resources, please report a new issue. We have to still go through rework of identifiers, so it is advised to specify escaped fully qualified names (e.g. from_share = ""organization_name"."account_name"."share_name"").
Thank You.