When importing a resource, for example by running $ terraform import snowflake_user.<username> <USERNAME> , it does not appear to honor the role used in the resource's provider.

Steps to Reproduce

1. Create a user in Snowflake:

USE ROLE "SECURITYADMIN";
CREATE USER CANNOTIMPORTME PASSWORD = '<password goes here>';

2. Create a terraform file with a provider alias using the appropriate role

# main.tf
provider "snowflake" {
  version = "~> 0.10"
  account = "<account name here>"
  role    = "SYSADMIN"
}

provider "snowflake" {
  alias   = "securityadmin"
  version = "~> 0.10"
  account = "<account name here>"
  role    = "SECURITYADMIN"
}


resource "snowflake_user" "cannot_import_me" {
  provider          = "snowflake.securityadmin"
  name              = "CANNOTIMPORTME"
  default_role      = "${snowflake_role.<role resource>.name}"
  default_warehouse = "${snowflake_warehouse.<warehouse resource>.name}"
  default_namespace = "${snowflake_database.<database resource>.name}"
  comment           = "something something"
}

( do the usual terraform setup, install the provider plugin, terraform init etc)

3. Import the user

$ terraform import snowflake_user.cannot_import_me CANNOTIMPORTME
snowflake_user.cannot_import_me: Importing from ID "CANNOTIMPORTME"...
snowflake_user.cannot_import_me: Import complete!
  Imported snowflake_user (ID: CANNOTIMPORTME)
snowflake_user.cannot_import_me: Refreshing state... (ID: CANNOTIMPORTME)

Error: snowflake_user.cannot_import_me (import id: CANNOTIMPORTME): 1 error occurred:
	* import snowflake_user.cannot_import_me result: CANNOTIMPORTME: snowflake_user.cannot_import_me: 003001 (42501): SQL access control error:
Insufficient privileges to operate on account '<account name goes here>'

Attempts to Resolve

• The error Insufficient privileges to operate on account occurs because the user is owned by SECURITYADMIN, and terraform import is using the main snowflake provider, not the aliased provider snowflake.securityadmin

• I tried setting the environment variable export SNOWFLAKE_ROLE=SECURITYADMIN but it made no difference

• I tried changing my snowsql config role to SECURITYADMIN and it made no difference

• I tried changing the role of the main provider, and it imported successfully:

provider "snowflake" {
  version = "~> 0.10"
  account = "<account name here>"
  role    = "SECURITYADMIN"
}

Next Steps

Given the way Snowflake provider encapsulates the role used to connect, it is something unique to the Snowflake provider and not Terraform generally.

What Role do you recommend using in the main provider? It feels like ACCOUNTADMIN is too privileged for Terraform use.

Originally posted by @ryanking in https://github.com/chanzuckerberg/terraform-provider-snowflake/diffs

Hi Team CZ,

I think there's still a schema privilege issue related to #68. When I try to grant the "ALL" privilege using snowflake_schema_grant, I get the following error:

Error: expected privilege to be one of [MODIFY MONITOR USAGE CREATE TABLE CREATE VIEW CREATE FILE FORMAT CREATE STAGE CREATE PIPE CREATE STREAM CREATE TASK CREATE SEQUENCE CREATE FUNCTION CREATE PROCEDURE], got ALL

  on roles.tf line 36, in resource "snowflake_schema_grant" "internal_schema_grant_bkfs_raw":
  36: resource "snowflake_schema_grant" "internal_schema_grant_bkfs_raw" {

Shouldn't "ALL" be a valid option?
https://docs.snowflake.net/manuals/user-guide/security-access-control-privileges.html#schema-privileges

Thanks!

It would be great if we can make the schema of this map explicit.

I need to read some more, but I think the way other providers do it is to make it a TypeList with size limit 1 and an Elem of type schema.Resource.

See https://www.terraform.io/docs/extend/writing-custom-providers.html#implementing-a-more-complex-read

Originally posted by @ryanking in https://github.com/chanzuckerberg/terraform-provider-snowflake/pull/52/files

We use the pipe character '|' internally in the project to delimit fields in the terraform state.

We should either validate that the relevant identifiers do not contain this character or escape it when constructing the keys.

Add a resource to create and manage tables. But I am curious how others are managing table objects without this resource available at this time.

Reference

Warehouses should support the following:

WAREHOUSE_SIZE = XSMALL | SMALL | MEDIUM | LARGE | XLARGE | XXLARGE | XXXLARGE | X4LARGE
MAX_CLUSTER_COUNT = <num>
MIN_CLUSTER_COUNT = <num>
SCALING_POLICY = STANDARD | ECONOMY
AUTO_SUSPEND = <num> | NULL
AUTO_RESUME = TRUE | FALSE
INITIALLY_SUSPENDED = TRUE | FALSE
RESOURCE_MONITOR = <monitor_name>
COMMENT = '<string_literal>'
MAX_CONCURRENCY_LEVEL = <num>
STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = <num>
STATEMENT_TIMEOUT_IN_SECONDS = <num>

whenever we have a statement in a snowflake_view that includes a character substitution (%s, %f, etc), the provider will use Go's default behavior of replacing these for %MISSING% which then messes up the statements.

SQL compilation error: syntax error line 1 at position 5,080 unexpected 'MISSING'. syntax error line 1 at position 5,087 unexpected ')'. syntax error line 1 at position 5,089 unexpected 'THEN'.

When using snowflake_database_grant with privilege = ALL, the Terraform plan will keep asking to recreate the resource.

I'm guessing this is because, as noted in another issue, ALL is an alias in Snowflake.

Steps to reproduce

The code:

resource "snowflake_database" "test_database" {
  name    = "TEST_DATABASE"
  comment = ""
}

resource "snowflake_role" "all_role" {
  name     = "ALL_ROLE"
}

resource "snowflake_database_grant" "all" {
  database_name = "${snowflake_database.test_database.name}"
  privilege = "ALL"
  roles = ["${snowflake_role.all_role.name}"]
}

Then terraform apply it once to apply the change.

Then run terraform apply again and see the same pending change:

Terraform will perform the following actions:

-/+ snowflake_database_grant.all (new resource required)
      id:               "TEST_DATABASE|||ALL" => <computed> (forces new resource)
      database_name:    "TEST_DATABASE" => "TEST_DATABASE"
      privilege:        "ALL" => "ALL"
      roles.#:          "0" => "1" (forces new resource)
      roles.3951068007: "" => "ALL_ROLE" (forces new resource)

Reference

Reader accounts (formerly known as “read-only accounts”) enable providers to share data with consumers who are not already Snowflake customers, without requiring the consumers to become Snowflake customers.

Right now, the provider does not support having more than one grant resource block refer to the same object (i.e. they "overlap"). This feature request is to throw a fatal error when this configuration is detected, instead of silently allowing it. The documentation is clear:

These resources do not enforce exclusive attachment of a grant, it is the user's responsibility to enforce this.

However, I would like to ask if it's possible to detect and throw an error in these cases.

Example of an invalid configuration containing multiple resource blocks that grant the same role:

resource "snowflake_role_grants" "foo" {
  role_name = "TEST_ROLE"
  roles = [
    "ROLE_A",
    "ROLE_B",
  ]
}
resource "snowflake_role_grants" "bar" {
  role_name = "TEST_ROLE"  # Same as previous grant.
  roles = [
    "ROLE_C",
    "ROLE_D",
  ]
}

Tangentially related: once, we have noticed that while running v0.4.0 of the provider, this would result in a ping-pong effect: each subsequent terraform apply will pick one or the other, whichever is opposite to the one currently in place. That was quite difficult to debug!

Grant ownership fails when there is already a dependent grant on the object.

snowflake_role.role_one: Creating...
snowflake_database.database_one: Creating...
snowflake_role.role_two: Creating...
snowflake_database.database_one: Creation complete after 2s [id=database_one]
snowflake_schema.schema_one: Creating...
snowflake_role.role_one: Creation complete after 2s [id=role_one]
snowflake_role_grants.role_one: Creating...
snowflake_role.role_two: Creation complete after 2s [id=role_two]
snowflake_role_grants.role_two: Creating...
snowflake_role_grants.role_one: Creation complete after 0s [id=role_one]
snowflake_schema.schema_one: Creation complete after 0s [id=database_one|schema_one]
snowflake_schema_grant.create_table: Creating...
snowflake_role_grants.role_two: Creation complete after 1s [id=role_two]
snowflake_schema_grant.create_table: Creation complete after 1s [id=database_one|schema_one||CREATE TABLE]
snowflake_schema_grant.ownership: Creating...

Error: 003036 (23001): SQL execution error: Dependent grant of privilege 'CREATE TABLE' on securable '"database_one"."schema_one"' to role '"role_one"' exists. It must be revoked first. More than one dependent grant may exist: use 'SHOW GRANTS' command to view them. To revoke all dependent grants while transferring object ownership, use convenience command 'GRANT OWNERSHIP ON <target_objects> TO <target_role> REVOKE CURRENT GRANTS'.

on main.tf line 42, in resource "snowflake_schema_grant" "ownership":
42: resource "snowflake_schema_grant" "ownership" {

The following is an example used to create the error above

provider "snowflake" {
  account = "SNOWFLAKEACCOUNT"
  // running as ACCOUNTADMIN TO AVOID dealing with security
  // not a good practise
  role = "ACCOUNTADMIN"
}

resource "snowflake_database" "database_one" {
    name = "database_one"
}

resource "snowflake_schema" "schema_one" {
    name        = "schema_one"
    database    = snowflake_database.database_one.name
}

// CREATE ROLE_ONE and grant to SYSADMIN
resource "snowflake_role" "role_one" {
    name    = "role_one"
}
resource "snowflake_role_grants" "role_one" {
    role_name   = snowflake_role.role_one.name
    roles       = ["SYSADMIN"]
}

// CREATE ROLE_TWO and grant to SYSADMIN
resource "snowflake_role" "role_two" {
    name    = "role_two"
}
resource "snowflake_role_grants" "role_two" {
    role_name   = snowflake_role.role_two.name
    roles       = ["SYSADMIN"]
}

resource "snowflake_schema_grant" "create_table" {
    schema_name   = snowflake_schema.schema_one.name
    database_name = snowflake_database.database_one.name
    privilege     = "CREATE TABLE"
    roles         = [snowflake_role.role_one.name]    
}

resource "snowflake_schema_grant" "ownership" {
    schema_name   = snowflake_schema.schema_one.name
    database_name = snowflake_database.database_one.name
    privilege     = "OWNERSHIP"
    roles         = [snowflake_role.role_two.name]   
    // use the depends_on to force the order 
    depends_on    = [snowflake_schema_grant.create_table]
}

I believe the fix is to use the COPY CURRENT GRANTS clause see https://docs.snowflake.com/en/sql-reference/sql/grant-ownership.html

Hi! This provider works really well for us, nice work! 🙌

Any plans to add support for "network policy"? (Ref. https://docs.snowflake.net/manuals/sql-reference/sql/create-network-policy.html)

Cheers,
Anders

Due to the concurrent nature of terraform resource creation, the use of patterns like USE WAREHOUSE, USE DATABASE etc. is buggy. Each SQL statement needs to be self-contained since the DB connection is shared across all resources and hence different resources could be causing clashes on the USE statements.

This is especially true for statements like select "name", "comment", "size" from table(result_scan(last_query_id())) - there's no way to guarantee that the last query on the DB was the one from the current resource

If the comment parameter on a snowflake_schema resource contains an apostrophe, that's probably not escaped correctly during resource creation because it causes snowflake syntax errors and fails the creation.

I haven't tested other resources, but I suspect it might be also problematic elsewhere.

I'd paste the error I got, but it's a syntax error on the next token following the apostrophe, which isn't going to be consistent for this bug.

It seems that the snowflake_schema_grant resource has an incorrect ValidateFunc set on its privilege attribute:

https://github.com/chanzuckerberg/terraform-provider-snowflake/blob/master/pkg/resources/schema_grant.go#L32

validViewPrivileges contains only "SELECT", which is not even a valid schema privilege. As is, it cannot be used to grant any other privilege besides the default value ("USAGE").

It may instead require something like the following list:

var validSchemaPrivileges = []string{
	"ALL",
        "MODIFY",
        "MONITOR",
        "USAGE",
        "CREATE TABLE",
        "CREATE VIEW",
        "CREATE FILE FORMAT",
        "CREATE STAGE",
        "CREATE PIPE",
        "CREATE STREAM",
        "CREATE TASK",
        "CREATE SEQUENCE",
        "CREATE FUNCTION",
        "CREATE PROCEDURE",
}

Which is in accordance with snowflake docs: https://docs.snowflake.net/manuals/sql-reference/sql/grant-privilege.html#syntax

Looking at the docs and the code, doesn't seem like storage_integration is not being handled.
We use storage_integrations on all of our stages, and can't make use of this provider for them.

Example from https://docs.snowflake.net/manuals/sql-reference/sql/create-stage.html#external-stages:

create or replace stage my_ext_stage
  url='s3://load/files/'
  storage_integration = myint;

Storage Integrations consist in linking Snowflake accounts with AWS, Azure or GCP accounts via Roles.

It's currently the best practice for connecting with your cloud environment, and it definitely one of the main benefits from being on Terraform : you can create the AWS Role (for example) and pass it as parameter to the Snowflake StorageIntegration 👍

https://docs.snowflake.net/manuals/sql-reference/sql/create-storage-integration.html

I think a first step would be AWS, then Azure and GCP (GCP was in preview until recently)

I've started using this provider and it looks to for fill some of our use cases really well.

I'm just wanting to check if this is continued to be used, or have you as the contributor started using another terraform provider for snowflake?

Reference

A share is used to provide access control across accounts

Originally posted by @ryanking in #52

The functionality to add a Snowpipe as the CREATE FILE FORMAT SQL performs is currently missing from this provider. It'd be nice to be able to manage these objects via Terraform.

https://docs.snowflake.net/manuals/sql-reference/sql/create-file-format.html

If you try to create an internal stage like:

resource snowflake_stage my_stage {
  name     = "MY_STAGE"
  database = "TEST"
  schema   = "PUBLIC"
}

When you do a terraform apply, you get Error: sql: Scan error on column index 9, name "region": converting NULL to string is unsupported

This apparently is because region cannot be nil. I'm not sure what could be done to solve this problem.

I can create a new schema if one does not exist but I cannot seem to import already existing schema. I tried two formats:

snowflake_schema.schema TEST_SCHEMA
snowflake_schema.schema DB_NAME.TEST_SCHEMA

both are failing.

$ terragrunt import snowflake_schema.schema TEST_SCHEMA
snowflake_schema.schema: Importing from ID "TEST_SCHEMA"...
snowflake_schema.schema: Import prepared!
Prepared snowflake_schema for import
snowflake_schema.schema: Refreshing state... [id=TEST_SCHEMA]

Error: 2 fields allowed

Any suggestions?

If I fork the repo and create a PR from my fork then Travis CI builds fail since it doesn't use a username/password for external PR's.
However I am getting permission denied to create a branch in this repo. What is the proper way to create a PR that will also build?

https://travis-ci.com/chanzuckerberg/terraform-provider-snowflake/jobs/269044056#L183

Encrypted environment variables have been removed for security reasons.
See https://docs.travis-ci.com/user/pull-requests/#pull-requests-and-security-restrictions

Feel free to close this issue if I missed something or simply not following the correct pattern to create a PR.

Originally posted by @ryanking in https://github.com/chanzuckerberg/terraform-provider-snowflake/diffs

The functionality to add a Snowpipe as the CREATE PIPE SQL performs is currently missing from this provider. It'd be nice to be able to manage Snowpipes via Terraform. Details:

https://docs.snowflake.net/manuals/sql-reference/sql/create-pipe.html
https://docs.snowflake.net/manuals/user-guide/data-load-snowpipe.html

Is there a way to use this provider to do the following:

GRANT USAGE on DATABASE <DB_NAME> to <ROLE_NAME>;

GRANT MODIFY on DATABASE <DB_NAME> to <ROLE_NAME>;

It seems like there are three active providers:

• https://github.com/chanzuckerberg/terraform-provider-snowflake
• https://github.com/Preston4tw/terraform-provider-snowflake
• https://github.com/ShopRunner/terraform-provider-snowflake

Any chance of the three of you combining efforts? I want to contribute but first I'll need to work out which one is close enough to what I need (all three are missing different bits and pieces).

Hi! I'm getting this error during the terraform plan.
This plan should not generate any changes to the resources.
In the region where I'm using snowflake, there were 2 snowflake releases since my last apply. I look at the changelog but I didn't see any change that can be breaking the provider.
Thanks for your help

EDIT 1:
It can be related to the snowflake_stage, when I try to do the terraform apply in a completely new environment, the error come from the stage object.

Snowflake supports two more schema privileges:

CREATE EXTERNAL TABLE
CREATE MATERIALIZED VIEW

Currently provider does not have above two privileges listed.

https://github.com/chanzuckerberg/terraform-provider-snowflake/blob/master/pkg/resources/schema_grant.go#L28

privilegeCreateExternalTable,
privilegeCreateMaterializedView,

Can you please add requested privileges?

Originally posted by @ryanking in https://github.com/chanzuckerberg/terraform-provider-snowflake/diffs

The ability to modify grants (database grants, schema grants, warehouse grants etc.) without completely re-creating them.

Motivation: As our Snowflake userbase grows, more people (and automation users) are running queries at any given time. More and more often, people complain that they don't have permission to access certain resources that they should have access to. This is typically caused by the temporarily loss of some grants while we are applying a terraform plan due to grant resources that have ForceNew enabled on the roles list.

Via the Snowflake web UI, I believe it is technically possible to modify individual grants to users/roles without affecting other users/roles which share that same grant object.

Hi @ryanking,

Just wondering what your plans are for this repo - due to internal requirements I have now added another 8 resources on the @Viostream fork plus some enhancements and bugfixes to existing resources.

Given that I have a couple of open PRs that seem to be stalled, keen to understand if you're still planning on developing/maintaining this - if so I can work out the best way to break the remaining changes back up into sensible PRs.

Cheers!

I tested new feature in 0.10.0

• create snowflake_pipe

but it fails to use specified database.

The plan looks good:

  # snowflake_pipe.pipe will be created
  + resource "snowflake_pipe" "pipe" {
      + auto_ingest          = false
      + comment              = "Snowpipe for my amazing data"
      + copy_statement       = "COPY INTO SOME DATABASE.SCHEMA "
      + database             = "MY-DB"
      + id                   = (known after apply)
      + name                 = "snow-pipe-data"
      + notification_channel = (known after apply)
      + owner                = (known after apply)
      + schema               = "PUBLIC"
    }

However the apply fails to use specified DATABASE:

Error: error creating pipe snow-pipe-data: 090105 (22000): Cannot perform operation. This session does not have a current database. Call 'USE DATABASE', or use a qualified name.

  on pipe.tf line 1, in resource "snowflake_pipe" "pipe":
   1: resource "snowflake_pipe" "pipe" {

A resource monitor is correctly created, but then on a subsequent execution of plan credit_quota causes an error

Error: sql: Scan error on column index 1, name "credit_quota": converting driver.Value type string ("50.00") to a int64: invalid syntax

Snowflake UI presents the quota as 50.00

Reference

A view allows the result of a query to be accessed as if it were a table.

I saw issue #109 about converting the credit quota from an int to a float in resource monitors but when I try and create a monitor I'm getting a data type error.

Error: error creating resource monitor EXAMPLE_MONITOR: 001008 (22023): SQL compilation error:
invalid value [5.00] for parameter 'CREDIT_QUOTA'

Using the generated SQL but using 5 instead of 5.00 creates the object successfully.

I'm getting this error when I try to create a snowflake_stage. Is it a known bug or I'm doing something wrong?

We have a terraform project consisting of about 130 resources (including users, roles, databases, schemas, all sorts of grants, etc.) and we're bumping into an issue with what appears to be snowflake rate limiting while running terraform plan.

Running terraform plan no longer works because while it is refreshing state, it will suddenly stop at a random resource (nondeterministic) and hang there forever until I am forced to interrupt it. I have seen it hang on a single resource overnight!

$ /opt/terraform/terraform-0.12.7 plan 
Refreshing Terraform state in-memory prior to plan...
The refreshed state will be used to calculate this plan, but will not be
persisted to local or remote state storage.

snowflake_role.tableau_service_role: Refreshing state... [id=TABLEAU_SERVICE_ROLE]
snowflake_warehouse.tableau_service: Refreshing state... [id=TABLEAU_SERVICE]
snowflake_role.all_human_user_roles["<redacted>"]: Refreshing state... [id=<redacted>_ROLE]
snowflake_role.stitch_loader_role: Refreshing state... [id=STITCH_LOADER_ROLE]
snowflake_role.all_human_user_roles["<redacted>"]: Refreshing state... [id=<redacted>_ROLE]
snowflake_role.all_human_user_roles["<redacted>"]: Refreshing state... [id=<redacted>_ROLE]
snowflake_role.all_human_user_roles["<redacted>"]: Refreshing state... [id=<redacted>_ROLE]
[...SKIP 76 LINES...]
snowflake_database_grant.prod_readwrite_usage: Refreshing state... [id=PROD|||USAGE]
snowflake_warehouse_grant.transforming_usage: Refreshing state... [id=TRANSFORMING|||USAGE]
snowflake_user.dbt_trasformer: Refreshing state... [id=DBT_TRASFORMER]
snowflake_user.all_human_users["<redacted>"]: Refreshing state... [id=<redacted>]
snowflake_user.all_human_users["<redacted>"]: Refreshing state... [id=<redacted>]
snowflake_user.all_human_users["<redacted>"]: Refreshing state... [id=<redacted>]
^CInterrupt received.
Please wait for Terraform to exit or data loss may occur.
Gracefully shutting down...
^CTwo interrupts received. Exiting immediately. Note that data
loss may have occurred.

Error: operation canceled

However, running a specific target succeeds, presumably because rate limiting was not encountered:

$ /opt/terraform/terraform-0.12.7 plan  -target=snowflake_role_grants.tableau_service_user_role_grant
Refreshing Terraform state in-memory prior to plan...
The refreshed state will be used to calculate this plan, but will not be
persisted to local or remote state storage.

snowflake_role.tableau_service_role: Refreshing state... [id=TABLEAU_SERVICE_ROLE]
snowflake_warehouse.tableau_service: Refreshing state... [id=TABLEAU_SERVICE]
snowflake_user.tableau_service: Refreshing state... [id=TABLEAU_SERVICE]
snowflake_role_grants.tableau_service_user_role_grant: Refreshing state... [id=TABLEAU_SERVICE_ROLE]

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

No changes. Infrastructure is up-to-date.

This means that Terraform did not detect any differences between your
configuration and real physical resources that exist. As a result, no
actions need to be performed.

As a workaround, I am able to refresh and apply all 130 resources this way, one at a time. However, this severely limits the usability of terraform, and precludes our ability to use tools such as Atlantis.

Does anybody else run medium size snowflake projects with 100+ resources? Do you run into rate limiting?

Hi Team CZ

We've just adopted Snowflake, so I've just started using your cool TF provider. Just a suggestion - can you add "email" as a property for the snowflake_user resource?

The reasoning is that, without associating an email with a new Snowflake user, it is impossible for that user to use the "Forgot Password" functionality. Thanks!

can we add something here that tests non-default privileges?

Originally posted by @ryanking in https://github.com/chanzuckerberg/terraform-provider-snowflake/pull/52/files

I think strings.TrimRight might be more clear here.

Originally posted by @ryanking in https://github.com/chanzuckerberg/terraform-provider-snowflake/diffs

The functionality to add a Snowpipe as the CREATE STAGE SQL performs is currently missing from this provider. It'd be nice to be able to manage these objects via Terraform. This functionality would undoubtedly be more difficult than other objects, due to the platform abstraction leaking and the need to specify platform-specific parameters.

https://docs.snowflake.net/manuals/sql-reference/sql/create-stage.html

Reference

We provision schemas via terraform, then populate tables in them via some other automation. However, there's no way for other users to view tables in this pre-populated schema unless I run:

GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO ...

Currently, we supplement our terraform with a carefully curated list of such statements, but I would much rather we just managed everything in terraform, including table grants.