aidanmelen / terraform-provider-snowsql Goto Github PK

View Code? Open in Web Editor NEW

21.0 4.0 12.0 430 KB

Terraform SnowSQL provider

Home Page: https://registry.terraform.io/providers/aidanmelen/snowsql/latest

License: Other

Makefile 8.06% Go 91.94%

snowflake snowsql terraform terraform-provider

terraform-provider-snowsql's Introduction

Terraform Provider SnowSQL

The snowsql provider allows for custom Terraform CRUD management of Snowflake objects using SnowSQL.

Note: This provider is not a drop in replacement for the robust resources implemented by terraform-provider-snowflake. For example, use the snowflake_warehouse resource if you need to create a virtual warehouse, Use this provider when you require fine grain control of DCL commands or to implement Snowflake objects that are unsupported by the Snowflake provider resources.

Similiar to the terraform-provider-shell; this provider

this is a backdoor into the Terraform runtime. You can do some pretty dangerous things with this and it is up to you to make sure you don't get in trouble. Since this provider is rather different than most other provider, it is recommended that you at least have some familiarity with the internals of Terraform before attempting to use this provider.

Build provider

Run the following command to build the provider

$ go build -o terraform-provider-snowsql

Test sample configuration

First, build and install the provider.

$ make install

Then, navigate to the examples directory.

$ cd examples/resources/exec/basic

Run the following command to initialize the workspace and apply the sample configuration.

$ terraform init && terraform apply

Credits

see CREDITS for more information.

terraform-provider-snowsql's People

Contributors

Stargazers

Watchers

Forkers

joncourt aidan-melen bridgecrew-perf1 bridgecrew-perf3 gianniskod ricardobf kthatikonda karthikthatikonda lucasp0r svnb christophkreutzer

terraform-provider-snowsql's Issues

Warehouse declaration not supported in snowsql provider

Hi @aidanmelen ,

I am encountering an issue when having multiple grant statements (more than one) defined in either the create or delete lifecycle. Given such a configuration the terraform apply always errors out complaining about no active warehouse selected. When attempting with the warehouse attribute in the snowsql provider configuration, an error is given stating the warehouse is not a supported argument.

Terraform Version

Terraform v1.0.11

Affected Resource(s)

snowsql_exec

Terraform Configuration Files

provider "snowsql" {
  account          = var.account
  region           = var.region
  username         = var.snowsql_username
  private_key_path = var.private_key_path

  warehouse = "compute_wh"
}

resource "snowsql_exec" "snowsql_exec_test" {
  name = "snowsql_exec_test"

  create {
    statements = <<-EOT
    grant select on view CITIBIKE.PUBLIC.TRIPS_V to role ${snowflake_role.role.name};
    grant select on view CITIBIKE.PUBLIC.TRIPS_V to role ${snowflake_role.role.name};
    EOT
  }

  delete {
    statements = <<-EOT
    revoke select on view CITIBIKE.PUBLIC.TRIPS_V from role ${snowflake_role.role.name};
    revoke select on view CITIBIKE.PUBLIC.TRIPS_V from role ${snowflake_role.role.name};
    EOT
  }

}

Expected Behavior

What should have happened?

An active warehouse is not required while executing grant statements
The attribute warehouse should be supported in the snowsql provider configuration similar to the snowflake provider. This line of code indicates it should have been a valid argument.

Actual Behavior

What actually happened?

An error popped up when multiple grant statements are declared

Error: 000606 (57P03): No active warehouse selected in the current session.  
Select an active warehouse with the 'use warehouse' command.
│ 
│ 
│   with snowsql_exec.snowsql_exec_test,
│   on test.tf line 67, in resource "snowsql_exec" "snowsql_exec_test":
│   67: resource "snowsql_exec" "snowsql_exec_test" {

An error popped up when attempting to declare warehouse in the snowsql provider

Error: Unsupported argument
│ 
│   on main.tf line 30, in provider "snowsql":
│   30:   warehouse = "compute_wh"
│ 
│ An argument named "warehouse" is not expected here.

Update resource lifecycle

Hi,

I wanted to ask if it was a deliberate decision to only allow create/delete, but no update lifecycle? We have a use case where it would be "more beautiful" if it could be seen as "in-place update" instead of a force replacement.

Would you be open for a PR on that topic?

Terraform provider changed

Hi there,

Thank you for opening an issue. Please note that we try to keep the Terraform issue tracker reserved for bug reports and feature requests. For general usage questions, please see: https://www.terraform.io/community.html.

Terraform Version

1.2.1

Affected Resource(s)

-snowsql_exec

Terraform provider path for snowflake changed from:

github.com/chanzuckerberg/terraform-provider-snowflake
to:
github.com/Snowflake-Labs/terraform-provider-snowflake

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

terraform init

Error when update block is removed

Terraform Version

1.1.0

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "snowsql_exec" "dcl" {
  name = var.name

  create {
    statements = "CREATE USER IF NOT EXISTS ${var.name} PASSWORD=${var.temporary_user_password} DEFAULT_ROLE = myrole DEFAULT_SECONDARY_ROLES = ('ALL') MUST_CHANGE_PASSWORD = TRUE;"
  }

  # read {
  #   statements = "SHOW USERS LIKE '${var.name}';"
  # }

  # comment after apply
  # update {
  #   statements = "ALTER USER IF EXISTS user1 SET EMAIL = '${var.name}@email.com';"
  # }

  delete {
    statements = "DROP USER IF EXISTS ${var.name};"
  }
}

Debug Output

Expected Behavior

an in-place update where the update changes to null as the snowlake object is unchanged.

Actual Behavior

state error in provider

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

terraform apply with the update block
terraform apply again without the update block

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:

bug: Enters broken state when read statements are bad

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec (v1.2.0)

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "snowsql_exec" "role" {
  name = "my_role"

  create {...}

  read {
    # single quote is not closed 
    statements = "SHOW ROLES LIKE 'my_role; SHOW ROLES LIKE 'ACCOUNTADMIN';"
  }

  delete {...}
}

Panic Output

╷
│ Error: 001003 (42000): SQL compilation error:
│ syntax error line 1 at position 43 unexpected 'ACCOUNTADMIN'.
│ parse error line 1 at position 57 near '<EOF>'.
│ 
│   with snowsql_exec.role,
│   on main.tf line 1, in resource "snowsql_exec" "role":
│    1: resource "snowsql_exec" "role" {
│ 
╵

Expected Behavior

We should be able to fix the read statement and the updated read statement should be run on the next run.

Actual Behavior

Because the read statements are triggered during the read/refresh lifecycle, the old broken statements run before the new read statements are evaluated. The only way to fix is to manually update the terraform state.

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:

feat: warn user that update statements are ignored during creation

We should warn the user that update statements are ignored when creating a resource while specifying the update block. Only the create and read statements are run during resource creation.

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "snowsql_exec" "role" {
  create {
    statements = "CREATE ROLE IF NOT EXISTS my_role"
  }

  # these will be ignored on creation
  update {
    statements = "ALTER ROLE IF EXISTS my_role SET COMMENT = 'updated with terraform'"
  }

  delete {
    statements = "DROP ROLE IF EXISTS my_role"
  }
}

Expected Behavior

A warning message that says the statements are ignored during creation and until the statements are changed or tainted.

Actual Behavior

The resource silently ignores the update statements.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

terraform apply

snow_exec gives error on multiple statement

Hi,

I keep having this error from time to time, even though I have privilege on this schema and only have 2 statements.
What's is weird is that when I retry without changing anything, it works.

Statements:
GRANT INSERT ON ALL TABLES IN SCHEMA DATABASE.TEST TO ROLE USER_ROLE;
GRANT INSERT ON FUTURE TABLES IN SCHEMA DATABASE.TEST TO ROLE USER_ROLE;

100132 (P0000): JavaScript execution error: Uncaught Execution of multiple statements failed on statement "GRANT INSERT ON FUTURE TABLES ..." (at line 2, position 0).
SQL access control error:
Insufficient privileges to operate on schema 'TEST' in SYSTEM$MULTISTMT at ' throw Execution of multiple statements failed on statement {0} (at line {1}, position {2})..replace('{1}', LINES[i])' position 4
stackstrace:
SYSTEM$MULTISTMT line: 10

Do you have any clue of what is going on ?
i didn't have this type of error on version 1.1.0, I have just migrated !

deletions run from state, not from code

Terraform Version

$ terraform -v
Terraform v0.14.11
+ provider registry.terraform.io/aidanmelen/snowsql v0.2.0

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec

Terraform Configuration Files

resource "snowsql_exec" "use_utc_timezone_for_account" {
  name  = "snowflake-timezone-parameter-utc"

  create {
    statements = <<-EOT
      ALTER ACCOUNT SET TIMEZONE = 'UTC'
    EOT
  }

  delete {
    statements = <<-EOT
      ALTER ACCOUNT UNSET TIMEZONE = 'UTC'
    EOT
  }

  delete_on_create = false
}

Expected Behavior

Deletion statements are based on the terraform code at the time of apply.

Actual Behavior

Deletion statements are based on the terraform state at the time of apply.

Steps to Reproduce

Apply the above infrastructure, which uses invalid SQL for the deletion statement. It will apply correctly upon creation because the creation statement is valid SQL.
Use terraform taint or terraform destroy to attempt to destroy the above infrastructure. This will fail because the deletion statement isn't valid.

Error: 001003 (42000): SQL compilation error:
syntax error line 1 at position 29 unexpected '='.

You notice your mistake and fix your deletion statement. ALTER ACCOUNT UNSET TIMEZONE.
Use terraform taint or terraform destroy again, expecting it to work because you now have valid SQL. But it won't work because the deletion statement isn't coming from your now valid SQL, it's coming from the invalid SQL that's still in the state. You get the same error as above.
The only way to unblock yourself is to terraform state rm ... && terraform import ..., then try the deletion again. This is unintuitive because you're used to having changes in your code reflected in the actions terraform takes.

bug: `number_of_statements` has no effect on `read` statements

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "snowsql_exec" "role_grant_all" {
  name = local.name

  create {
    statements = <<-EOT
      // must be 14 snowsql statements
    EOT
    number_of_statements = 14
  }

  read {
    statements = <<-EOT
      // any number of read query statements
    EOT
    number_of_statements = 100 # This value is not used.
  }

  delete {
    statements = <<-EOT
      // must be 14 snowsql statements
    EOT
    number_of_statements = 14
  }
}

Expected Behavior

The read block should work like the other nested blocks. The following error should be presented to the user when the read.0.statements does not match the read.0.number_of_statements in order to protect against sql injection.

│ Error: failed to execute the create statements.
│ 
│ Statements:
│ 
│   GRANT ALL PRIVILEGES ON ALL TABLES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL VIEWS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL FILE FORMATS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL SEQUENCES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL STREAMS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON ALL PROCEDURES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE TABLES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE VIEWS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE FILE FORMATS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE SEQUENCES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE FUNCTIONS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE STREAMS IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ GRANT ALL PRIVILEGES ON FUTURE PROCEDURES IN DATABASE COMPLETE_DEEP_CRAPPIE TO ROLE COMPLETE_DEEP_CRAPPIE;
│ 
│ 
│ 000008 (0A000): Actual statement count 14 did not match the desired statement count 2.
│ 
│   with snowsql_exec.role_grant_all,
│   on main.tf line 9, in resource "snowsql_exec" "role_grant_all":
│    9: resource "snowsql_exec" "role_grant_all" {
│ 
╵

Actual Behavior

The read.0.statements queries are run and the read.0.number_of_statements is ignored.

No active warehouse selected in the current session

Terraform Version

$ terraform -v
Terraform v1.2.0
on darwin_arm64
+ provider registry.terraform.io/aidanmelen/snowsql v0.4.3
+ provider registry.terraform.io/chanzuckerberg/snowflake v0.25.36

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec

Terraform Configuration Files

terraform {
  required_providers {
    snowflake = {
      source  = "chanzuckerberg/snowflake"
      version = "0.25.36"
    }
    snowsql = {
      source  = "aidanmelen/snowsql"
      version = "0.4.3"
    }
  }
}

provider "snowflake" {
  # See https://guides.snowflake.com/guide/terraforming_snowflake/index.html?index=..%2F..index#2
  # for instructions on how to set up a user account to be used by Terraform.
  // required
  username = "automation"
  account  = "REDACTED"
  # password is exposed as an environment variable
  region = "us-east-1"

  // optional
  role = "ACCOUNTADMIN"
}

provider "snowsql" {
  # See https://guides.snowflake.com/guide/terraforming_snowflake/index.html?index=..%2F..index#2
  # for instructions on how to set up a user account to be used by Terraform.
  // required
  username = "automation"
  account  = "REDACTED"
  # password is exposed as an environment variable
  region = "us-east-1"

  // optional
  role = "ACCOUNTADMIN"
}

resource "snowflake_warehouse" "foo" {
  name           = "FOO"
  comment        = "FOO"
  warehouse_size = "small"
}

resource "snowflake_user" "foo" {
  name                 = "FOO"
  login_name           = "FOO"
  disabled             = false
  display_name         = "FOO"
  email                = "[email protected]"
  must_change_password = false
}

resource "snowsql_exec" "monitor" {
  name = "SANDBOX3-SFMONTOR"

  create {
    statements = <<-EOT
    USE WAREHOUSE "${snowflake_warehouse.foo.name}";

    CREATE OR REPLACE RESOURCE MONITOR "SANDBOX3-SFMONITOR" WITH
      CREDIT_QUOTA = 50
      FREQUENCY = DAILY
      START_TIMESTAMP = IMMEDIATELY
      NOTIFY_USERS = ( ${join(", ", [
    snowflake_user.foo.name,
])} )
      TRIGGERS ON 100 PERCENT DO SUSPEND;

    ALTER ACCOUNT SET RESOURCE_MONITOR = "SANDBOX3-SFMONITOR";
  EOT
}

delete {
  statements = <<-EOT
    ALTER ACCOUNT UNSET RESOURCE_MONITOR;
    DROP RESOURCE MONITOR "SANDBOX3-SFMONITOR";
  EOT
}
}

Expected Behavior

Terraform should executed the expected USE WAREHOUSE statement and then the expected CREATE OR REPLACE RESOURCE MONITOR statement.

Actual Behavior

snowsql_exec.monitor: Creating...
╷
│ Error: 000606 (57P03): No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.
│ 
│ 
│   with snowsql_exec.monitor,
│   on main.tf line 55, in resource "snowsql_exec" "monitor":
│   55: resource "snowsql_exec" "monitor" {

Steps to Reproduce

Paste the above HCL into a terraform file, and then:

terraform init
terraform apply

Important Factoids

The desired USE WAREHOUSE and CREATE OR REPLACE RESOURCE MONITOR statements run fine if I paste them into a worksheet in the console and run them there, using the same credentials/role as terraform uses.

I'll also add that I'm aware of the snowflake_resource_monitor resource in the snowflake provider, and I would prefer to use that instead of using snowsql_exec, but snowflake_resource_monitor doesn't currently support NOTIFY_USERS, so that's how I ended up with snowsql_exec.

feat: `snowsql_exec` name argument should be optional

It would be conviently if the name argument for the snowsql_exec resource was optional and defaulted to a random ID when not provided.

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_query

Terraform Configuration Files

resource "snowsql_exec" "role" {
  # name = "my_role"

  create {
    statements = "CREATE ROLE IF NOT EXISTS my_role"
  }

  delete {
    statements = "DROP ROLE IF EXISTS my_role"
  }
}

Expected Behavior

The resource will be assigned a random ID in terraform state.

Actual Behavior

╷
│ Error: Missing required argument
│ 
│   on main.tf line 1, in resource "snowsql_exec" "role":
│    1: resource "snowsql_exec" "role" {
│ 
│ The argument "name" is required, but no definition was found.
╵

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

terraform apply

Important Factoids

This is how the snowsql_query data source was implemented.

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:

Error: `No active warehouse selected in the current session`

Terraform Configuration Files

provider "snowflake" {
  account  = var.snowflake_account
  username = data.onepassword_item_login.snowflake_login.username
  password = data.onepassword_item_login.snowflake_login.password
  role     = "ACCOUNTADMIN"
}

provider "snowsql" {
  account  = var.snowflake_account
  username = data.onepassword_item_login.snowflake_login.username
  password = data.onepassword_item_login.snowflake_login.password
  role     = "ACCOUNTADMIN"
}

resource "snowsql_exec" "grant_ownership_on_aad_roles_to_aad_provisioner" {
  name = "grant_ownership_on_aad_roles_to_aad_provisioner"

  create {
    statements = <<-EOT
    GRANT OWNERSHIP ON ROLE DEVELOPER TO ROLE AAD_PROVISIONER;
    GRANT OWNERSHIP ON ROLE REPORTER TO ROLE AAD_PROVISIONER;
    EOT
  }

  delete {
    statements = <<-EOT
    REVOKE OWNERSHIP ON ROLE DEVELOPER FROM ROLE ACCOUNTADMIN;
    REVOKE OWNERSHIP ON ROLE REPORTER FROM ROLE ACCOUNTADMIN;
    EOT
  }
}

Expected Behavior

Successful apply.

Actual Behavior

Error: 000606 (57P03): No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.

Steps to Reproduce

terraform apply

bug: The `snowsql_query` data source ID will is always reset even if it has already been set before.

The snowsql_query data source ID will is always reset even if it has already been set before when the name argument is not specified.

Terraform Version

Run terraform -v to show the version. If you are not running the latest version of Terraform, please upgrade because your issue may have already been fixed.

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_query

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

data "snowsql_query" "select_current_user" {
  statements = "SELECT current_user()"
}

Debug Output

$ TF_LOG=debug terraform apply
...
2023-02-24T12:34:39.536-0700 [WARN]  Provider "provider[\"registry.terraform.io/aidanmelen/snowsql\"]" produced an unexpected new value for data.snowsql_query.select_current_user.
      - .id: was cty.StringVal("cfsh2finv5cve7d955og"), but now cty.StringVal("cfsh2jqnv5cvvltgp2t0")
...

Expected Behavior

during the first terraform apply, the data source ID should be set in state. Then all subsequent applies should use this ID.

Actual Behavior

The data source ID always gets reset when the name argument is not specified.

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

TF_LOG=debug terraform apply
TF_LOG=debug terraform apply and we should see the data source ID change in state.

feat: add support for `protocol`and `port` provider argument

Updating snowsql provider to work with new snowflake provider arguments created here.

goreleaser fails on windows_386 build

Expected Behavior

goreleaser running locally

goreleaser build                                                                                                                         main
   • building...      
   • loading config file       file=.goreleaser.yml
   • loading environment variables
   • getting and validating git state
      • building...               commit=b66520a3cccb7a3081b7a89cb09c822a3dc5c01f latest tag=v0.2.0
   • parsing tag      
   • running before hooks
      • running                   hook=go mod verify
   • setting defaults 
      • snapshotting     
      • github/gitlab/gitea releases
      • project name     
      • loading go mod information
      • building binaries
         • DEPRECATED: skipped windows/arm64 build on Go < 1.17 for compatibility, check https://goreleaser.com/deprecations/#builds-for-windowsarm64 for more info.
         • DEPRECATED: skipped darwin/arm64 build on Go < 1.16 for compatibility, check https://goreleaser.com/deprecations/#builds-for-darwinarm64 for more info.
      • universal binaries
      • creating source archive
      • archives         
      • linux packages   
      • snapcraft packages
      • calculating checksums
      • signing artifacts
      • signing docker images
      • docker images    
      • docker manifests 
      • artifactory      
      • blobs            
      • homebrew tap formula
      • gofish fish food cookbook
      • scoop manifests  
      • discord          
      • reddit           
      • slack            
      • teams            
      • twitter          
      • smtp             
      • mattermost       
      • milestones       
      • telegram         
   • checking ./dist  
   • loading go mod information
   • writing effective config file
      • writing                   config=dist/config.yaml
   • building binaries
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_darwin_amd64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_freebsd_arm64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_windows_386/terraform-provider-snowsql_v0.2.0.exe
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_linux_amd64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_freebsd_arm_6/terraform-provider-snowsql_v0.2.0
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_freebsd_amd64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_windows_amd64/terraform-provider-snowsql_v0.2.0.exe
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_freebsd_386/terraform-provider-snowsql_v0.2.0
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_windows_arm_6/terraform-provider-snowsql_v0.2.0.exe
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_linux_arm64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_linux_386/terraform-provider-snowsql_v0.2.0
      • building                  binary=/Users/aidanmelen/workspace/terraform-provider-snowsql/dist/terraform-provider-snowsql_linux_arm_6/terraform-provider-snowsql_v0.2.0
   • build succeeded after 244.47s

I would expect this to also work in github actions

Actual Behavior

goreleaser running in a github action

Run goreleaser/[email protected]
Downloading https://github.com/goreleaser/goreleaser/releases/download/v0.184.0/goreleaser_Linux_x86_64.tar.gz
Extracting GoReleaser
/usr/bin/tar xz --warning=no-unknown-keyword --overwrite -C /home/runner/work/_temp/84768db9-cbb3-451f-95c5-73b4a8e03674 -f /home/runner/work/_temp/a52dc436-6168-4165-ac4c-70b40457c70f
GoReleaser latest installed successfully
v0.2.0 tag found for commit 'b66520a'
/opt/hostedtoolcache/goreleaser-action/0.184.0/x64/goreleaser release --rm-dist
   • releasing...     
   • loading config file       file=.goreleaser.yml
   • loading environment variables
   • getting and validating git state
      • building...               commit=b66520a3cccb7a3081b7a89cb09c822a3dc5c01f latest tag=v0.2.0
   • parsing tag      
   • running before hooks
      • running                   hook=go mod verify
   • setting defaults 
      • snapshotting     
      • github/gitlab/gitea releases
      • project name     
      • loading go mod information
      • building binaries
         • DEPRECATED: skipped windows/arm64 build on Go < 1.17 for compatibility, check https://goreleaser.com/deprecations/#builds-for-windowsarm64 for more info.
         • DEPRECATED: skipped darwin/arm64 build on Go < 1.16 for compatibility, check https://goreleaser.com/deprecations/#builds-for-darwinarm64 for more info.
      • universal binaries
      • creating source archive
      • archives         
      • linux packages   
      • snapcraft packages
      • calculating checksums
      • signing artifacts
      • signing docker images
      • docker images    
      • docker manifests 
      • artifactory      
      • blobs            
      • homebrew tap formula
      • gofish fish food cookbook
      • scoop manifests  
      • discord          
      • reddit           
      • slack            
      • teams            
      • twitter          
      • smtp             
      • mattermost       
      • milestones       
      • telegram         
   • checking ./dist  
   • loading go mod information
   • writing effective config file
      • writing                   config=dist/config.yaml
   • building binaries
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_darwin_amd64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_windows_386/terraform-provider-snowsql_v0.2.0.exe
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_freebsd_amd64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_freebsd_386/terraform-provider-snowsql_v0.2.0
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_freebsd_arm_6/terraform-provider-snowsql_v0.2.0
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_freebsd_arm64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_windows_amd64/terraform-provider-snowsql_v0.2.0.exe
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_windows_arm_6/terraform-provider-snowsql_v0.2.0.exe
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_linux_amd64/terraform-provider-snowsql_v0.2.0
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_linux_386/terraform-provider-snowsql_v0.2.0
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_linux_arm_6/terraform-provider-snowsql_v0.2.0
      • building                  binary=/home/runner/work/terraform-provider-snowsql/terraform-provider-snowsql/dist/terraform-provider-snowsql_linux_arm64/terraform-provider-snowsql_v0.2.0
   ⨯ release failed after 453.00s error=failed to build for windows_386: exit status 2: go: downloading github.com/hashicorp/terraform-plugin-sdk v1.12.0
go: downloading github.com/pkg/errors v0.9.1
go: downloading golang.org/x/crypto v0.0.0-20210921155107-089bfa567519
go: downloading github.com/chanzuckerberg/terraform-provider-snowflake v0.25.24
go: downloading github.com/mitchellh/go-homedir v1.1.0
go: downloading github.com/youmark/pkcs8 v0.0.0-20201027041543-1326539a0a0a
go: downloading github.com/aws/smithy-go v1.8.0
go: downloading github.com/apache/arrow/go/arrow v0.0.0-20211012155236-b2b2cbeb28a7
go: downloading github.com/gabriel-vasile/mimetype v1.3.1
go: downloading github.com/Azure/azure-storage-blob-go v0.14.0
go: downloading github.com/luna-duclos/instrumentedsql v1.1.3
go: downloading github.com/aws/aws-sdk-go-v2 v1.9.2
go: downloading golang.org/x/sys v0.0.0-20211007075335-d3039528d8ac
go: downloading github.com/google/flatbuffers v2.0.0+incompatible
go: downloading github.com/aws/aws-sdk-go-v2/service/s3 v1.16.1
go: downloading github.com/aws/aws-sdk-go-v2/credentials v1.4.3
go: downloading github.com/Azure/azure-pipeline-go v0.2.3
go: downloading github.com/google/uuid v1.3.0
go: downloading github.com/pierrec/lz4/v4 v4.1.8
go: downloading golang.org/x/net v0.0.0-20211011170408-caeb26a5c8c0
go: downloading github.com/mattn/go-ieproxy v0.0.1
go: downloading github.com/hashicorp/terraform-plugin-sdk/v2 v2.8.0
go: downloading github.com/aws/aws-sdk-go-v2/service/internal/accept-encoding v1.3.0
go: downloading github.com/aws/aws-sdk-go-v2/service/internal/s3shared v1.7.2
go: downloading github.com/aws/aws-sdk-go-v2/service/internal/presigned-url v1.3.2
go: downloading github.com/hashicorp/terraform-plugin-go v0.4.0
go: downloading github.com/mattn/go-isatty v0.0.14
go: downloading github.com/agext/levenshtein v1.2.3
go: downloading github.com/zclconf/go-cty v1.9.1
go: downloading github.com/mitchellh/go-wordwrap v1.0.1
go: downloading github.com/golang/protobuf v1.5.2
go: downloading github.com/hashicorp/go-uuid v1.0.2
go: downloading google.golang.org/protobuf v1.27.1
go: downloading github.com/hashicorp/errwrap v1.1.0
go: downloading golang.org/x/text v0.3.7
go: downloading google.golang.org/genproto v0.0.0-20211012143446-e1d23e1da178
go: downloading github.com/apparentlymart/go-textseg v1.0.0
go: downloading github.com/apparentlymart/go-textseg/v13 v13.0.0
# github.com/apache/arrow/go/arrow
Error: ../../../go/pkg/mod/github.com/apache/arrow/go/[email protected]/datatype_extension.go:70:26: registry.LoadAndDelete undefined (type *sync.Map has no field or method LoadAndDelete)
note: module requires Go 1.15

Error: The process '/opt/hostedtoolcache/goreleaser-action/0.184.0/x64/goreleaser' failed with exit code 1

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

run release github action.

Important Factoids

built locally on a mac. github action is running on ubuntu-20.04

feat: data resource to query snowflake

Terraform Version

1.x.x

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec

Terraform Configuration Files

data "snowsql_query" "query" {
  name       = local.name
  statements = "SHOW USERS LIKE '${local.name}';"
}

output "snowsql_query_results" {
  value = jsondecode(nonsensitive(data.snowsql_query.query.results))
}

Expected Behavior

the snowsql_query.results attribute will contain a list of JSON formatted query results.

bug: `read_results` not json decodable when `read` is not specified

The snowsql_exec resource supports an optional read block which will return a JSON encoded string containing the query result set(s). When the read block is not specified, attribute is a non-json decodable empty string which will fail to decode with the jsondecode() terraform function.

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "snowsql_exec" "role" {
  name = "my_role"

  create {
    statements = "CREATE ROLE IF NOT EXISTS my_role"
  }

  delete {
    statements = "DROP ROLE IF EXISTS my_role"
  }
}

output "show_role_results" {
  description = "The SnowSQL query result from the read statements."
  value       = jsondecode(nonsensitive(snowsql_exec.role.read_results))
}

Error Output

╷
│ Error: Error in function call
│ 
│   on outputs.tf line 3, in output "show_role_results":
│    3:   value       = jsondecode(nonsensitive(snowsql_exec.role.read_results))
│     ├────────────────
│     │ snowsql_exec.role.read_results has a sensitive value
│ 
│ Call to function "jsondecode" failed: EOF.
╵

Expected Behavior

That read_results is a empty JSON encoded list so that the jsondecode doesn't failed on a empty string.

> jsondecode("null")
null

Actual Behavior

We are essentially doing this:

> jsondecode("")

╷
│ Error: Error in function call
│ 
│   on <console-input> line 1:
│   (source code not available)
│ 
│ Call to function "jsondecode" failed: EOF.
╵

Steps to Reproduce

Please list the steps required to reproduce the issue, for example:

jsondecode read_results.
terraform apply with the read block not specified.

Use list in statement - resource "snowsql_exec"

Hi @aidanmelen,

I'm trying to use a list in the resource "snowsql_exec" in the statements creation. But its seems not possible in this moment.

Terraform Version

Run terraform -v to show the version. If you are not running the latest version of Terraform, please upgrade because your issue may have already been fixed.

Terraform v1.2.1

Affected Resource(s)

snowsql_exec

Terraform Configuration Files

# Copy-paste your Terraform configurations here - for large Terraform configs,
# please use a service like Dropbox and share a link to the ZIP file. For
# security, you can also encrypt the files using our GPG public key.

resource "snowsql_exec" "dcl" {
  name = local.name
  for_each = snowflake_table_grant.update_schema_future
  triggers = {
    database  = each.value.database_name
    schema    = each.value.schema_name
    roles     = join(",", each.value.roles)
    }
  create {
    statements = <<-EOT
    GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA ${self.triggers.database_name} TO ROLE ${self.triggers.roles};
    GRANT SELECT, REFERENCES ON ALL VIEWS IN SCHEMA ${self.triggers.database_name} TO ROLE ${self.triggers.roles};
    GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA ${self.triggers.database_name} TO ROLE ${self.triggers.roles};
    GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA ${self.triggers.database_name}.${self.triggers.schema} TO ROLE ${self.triggers.roles};
    GRANT SELECT, REFERENCES ON ALL VIEWS IN SCHEMA ${self.triggers.database_name}.${self.triggers.schema} TO ROLE ${self.triggers.roles};
    GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA ${self.triggers.database_name}.${self.triggers.schema} TO ROLE ${self.triggers.roles};
    EOT
  }
  delete {
    statements = <<-EOT
    GRANT UPDATE, INSERT, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA ${self.triggers.database_name} TO ROLE  ${self.triggers.roles};
    GRANT UPDATE, INSERT, DELETE, TRUNCATE ON ALL TABLES IN DATABASE ${self.triggers.database_name}.${self.triggers.schema} TO ROLE ${self.triggers.roles};
    EOT
  }
}

Reference - snowflake_table_grant

resource "snowflake_table_grant" "update_schema_future" {
  for_each      = local.table_schema_future_grants[local.write]
  on_future     = true
  privilege     = "UPDATE"
  database_name = upper(split(".", each.key)[0])
  schema_name   = upper(split(".", each.key)[1])
  roles = [
    for role in each.value :
    upper(role)
  ]
  depends_on = [snowflake_database.databases,
    snowflake_schema.schemas,
    snowflake_role.access,
    snowflake_role.users,
  snowflake_role.app_user_roles]
}

Expected Behavior

What should have happened?

The idea is create a mechanism to managment big envionments.

Actual Behavior

What actually happened?

The error is coming out, how we had a discussion the provider isn't support yet.

Error: Invalid "self" reference

  on implicit_grants_exec.tf line 21, in resource "snowsql_exec" "dcl":
  21:     GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA ${self.triggers.database_name}.${self.triggers.schema} TO ROLE ${self.triggers.roles};

The "self" object is not available in this context. This object can be used
only in resource provisioner and connection blocks.

Important Factoids

Are there anything atypical about your accounts that we should know? For example: Running in EC2 Classic? Custom version of OpenStack? Tight ACLs?

References

Are there any other GitHub issues (open or closed) or Pull Requests that should be linked here? For example:

Link Another ref

Support new `host` parameter similar to snowflake provider

https://github.com/chanzuckerberg/terraform-provider-snowflake/blob/main/pkg/provider/provider.go#L134 has recently introduced a host parameter so we don't have to hack around with the region including extra domains like .privatelink for example.

It would be good if we could do that here too so our snowflake providers all have similar args/experience!

[feature-request] add a changelog?

(I'll mention my own use case with dependabot, but the same thing applies in a less automated fashion even when humans are doing an upgrade...).

We use dependabot to keep our provider version pins updated, and it helpfully displays a changelog whenever it proposes an upgrade. The trouble is that this repo apparently doesn't use a changelog, so dependabot has no source of truth for what is going to change:

It only becomes apparent to the user that there is a breaking change between these two versions when they run a plan and the plan breaks:

It would be more user-friendly if the breaking change (and really, any change) was called out in a changelog. Would it be possible to add one?.

I'll add that I'm not opposed to looking around at other OSS projects and see how they handle it, and then see what I can paste into GitHub actions to get it working and make a PR here to contribute it, if that would be welcomed.

Thanks.

bug: import does not work

The snowsql import does not import the resource statements correctly.

Terraform Version

n/a

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec v1.2.0

Terraform Configuration Files

resource "snowsql_exec" "role" {
  name = "my_role"

  create {
    statements = "CREATE ROLE IF NOT EXISTS my_role"
  }

  read {
    statements = "SHOW ROLES LIKE 'my_role'"
  }

  delete {
    statements = "DROP ROLE IF EXISTS my_role"
  }
}

Expected Behavior

What should have happened?

the create, read, and delete statements should be in the resource state.

Actual Behavior

{
  "version": 4,
  "terraform_version": "1.0.5",
  "serial": 152,
  "lineage": "xxxxxxxxxxxxxxx",
  "outputs": {},
  "resources": [
    {
      "mode": "managed",
      "type": "snowsql_exec",
      "name": "role",
      "provider": "provider[\"registry.terraform.io/aidanmelen/snowsql\"]",
      "instances": [
        {
          "schema_version": 0,
          "attributes": {
            "create": [],
            "delete": [],
            "id": "my_role",
            "name": null,
            "read": [],
            "read_results": "",
            "update": []
          },
          "sensitive_attributes": [],
          "private": "xxxxxxx=="
        }
      ]
    }
  ]
}

Skip revokes

Hi there, @aidanmelen

Question:

I want to know if there is a way to skip the revokes, and just create the grants as necessary. My environment is rlly big and i'm trying to use lifecycles to skip the delete.statements. But its not working..

I dont want to have revokes for a moment in the environment during the deploys. Usually takes 4/5 min to recreate the grants when we add a new role or something. For our team this is a issue. I just want to add new grants and not takes this downtime during this time.

Tf-Code

resource "snowsql_exec" "sch_read_mat_view" {
  name = "${local.name_grants}_READ_ALL_SCHEMAS"

  create {
    statements = <<-EOT
    ${local.read_grant_all_sch_mat_view}
    EOT
  }

  delete {
    statements = <<-EOT
    ${local.delete_grant_all_sch_mat_view}
    EOT
  }

  lifecycle {
    ignore_changes = [delete[0].statements]
  }
}

feat: Add read lifecycle

Terraform Version

1.x.x

Affected Resource(s)

Please list the resources as a list, for example:

snowsql_exec

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Terraform Configuration Files

resource "snowsql_exec" "dcl" {
  name = local.name

  create {
    statements = "CREATE USER IF NOT EXISTS ${local.name} PASSWORD='${local.password}';"
  }

  read {
    statements = "SHOW USERS LIKE '${local.name}';"
  }

  update {
    statements = "ALTER USER IF EXISTS ${local.name} SET DISPLAY_NAME = my_example_usertf;"
  }

  delete {
    statements = "DROP USER IF EXISTS ${local.name};"
  }
}

output "read_result_json" {
  value = snowsql_exec.dcl.read_result_json
}

Expected Behavior

After create or update lifecycle changes, the snowsql read_result_json attribute will contain a list of JSON formatted read query results.

subsequent tf plan and apply dont work

Hi there,

Terraform Version

Terraform v1.5.0

Affected Resource(s)

"snowsql_exec"

I am using this to insert configuration into specific snowflake tables which works for the first time but subsequent terraform plan and apply commands fail complaining about "Cannot perform SELECT. This session does not have a current database. Call 'USE DATABASE', or use a qualified name."

in my commands i have provided what database to use and what schema to use using the commands USE DATABASE etc. however it doesnt recognize these values and fails.

provider "snowsql" {
username =
account =
password =
warehouse =
role =
}

resource "snowsql_exec" "insert_config" {
create {
statements = <<-EOT
USE DATABASE ${snowflake_database.database.name};
USE SCHEMA ${snowflake_schema.schema.name};
INSERT INTO XYZ.ABC_TBL (COL) VALUES ('COL1');
EOT
}
read {
statements = <<-EOSTMT
USE DATABASE ${snowflake_database.database.name};
USE SCHEMA ${snowflake_schema.schema.name};
SELECT COUNT(*) FROM XYZ.ABC_TBL;
EOSTMT
}
delete {
statements = <<-EOD
USE DATABASE ${snowflake_database.database.name};
USE SCHEMA ${snowflake_schema.schema.name};
DELETE FROM XYZ.ABC_TBL WHERE 1=2;
EOD
}
depends_on = [
snowflake_table.table_config
]
}