Repository: chanzuckerberg/terraform-provider-snowflake
Pull request #545 (2021-05-17 14:28:57)

Add data source for SYSTEM$GET_PRIVATELINK_CONFIG() function
<!-- Feel free to delete comments as you fill this in -->

<!-- summary of changes -->

## Test Plan
<!-- detail ways in which this PR has been tested or needs to be tested -->
* [x] acceptance tests
<!-- add more below if you think they are relevant -->
* [x] - This executes SELECT SYSTEM$GET_PRIVATELINK_CONFIG(), which applies for accounts with Business Critical plan; although values are returned from Snowflake either way.

Tests ran

```
$ TF_ACC=1 go test -v ./... -run TestAccSystemGetPrivateLinkConfig_aws
?   	github.com/chanzuckerberg/terraform-provider-snowflake	[no test files]
=== RUN   TestAccSystemGetPrivateLinkConfig_aws
=== PAUSE TestAccSystemGetPrivateLinkConfig_aws
=== CONT  TestAccSystemGetPrivateLinkConfig_aws
--- PASS: TestAccSystemGetPrivateLinkConfig_aws (5.92s)
PASS
ok  	github.com/chanzuckerberg/terraform-provider-snowflake/pkg/datasources	6.353s
?   	github.com/chanzuckerberg/terraform-provider-snowflake/pkg/db	[no test files]
testing: warning: no tests to run
PASS
ok  	github.com/chanzuckerberg/terraform-provider-snowflake/pkg/provider	(cached) [no tests to run]
testing: warning: no tests to run
PASS
ok  	github.com/chanzuckerberg/terraform-provider-snowflake/pkg/resources	(cached) [no tests to run]
testing: warning: no tests to run
PASS
ok  	github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake	(cached) [no tests to run]
?   	github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers	[no test files]
testing: warning: no tests to run
PASS
ok  	github.com/chanzuckerberg/terraform-provider-snowflake/pkg/validation	(cached) [no tests to run]
?   	github.com/chanzuckerberg/terraform-provider-snowflake/pkg/version	[no test files]
```

The JSON structure from the function `SELECT SYSTEM$GET_PRIVATELINK_CONFIG()` on all the accounts I have ran against are AWS, so have not been able to verify if there are any different behaviours on Azure, e.g. the config value `privatelink-ocsp-url` may actually come back as documented rather than `privatelink_ocsp-url` so have added belt-n-braces around that.

## References
<!-- issues documentation links, etc  -->

* https://docs.snowflake.com/en/sql-reference/functions/system_get_privatelink_config.html

alldoami: /ok-to-test sha=563760c
github-actions[bot]: Integration tests success for [563760c](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/850821132)
alldoami: @robbruce could you update your branch? After that we can merge! Thank you for creating this!
robbruce: @alldoami - merged with main
alldoami: /ok-to-test sha=2f41868
github-actions[bot]: Integration tests success for [2f41868](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/851353621)

Patch:
diff --git a/docs/data-sources/system_get_privatelink_config.md b/docs/data-sources/system_get_privatelink_config.md
new file mode 100644
index 00000000..0fd806ff
--- /dev/null
+++ b/docs/data-sources/system_get_privatelink_config.md
@@ -0,0 +1,84 @@
+---
+# generated by https://github.com/hashicorp/terraform-plugin-docs
+page_title: "snowflake_system_get_privatelink_config Data Source - terraform-provider-snowflake"
+subcategory: ""
+description: |-
+  
+---
+
+# snowflake_system_get_privatelink_config (Data Source)
+
+
+
+## Example Usage
+
+```terraform
+data "system_get_privatelink_config" "snowflake_private_link" {}
+
+resource "aws_security_group" "snowflake_private_link" {
+  vpc_id = var.vpc_id
+
+  ingress {
+    from_port   = 80
+    to_port     = 80
+    cidr_blocks = var.vpc_cidr
+    protocol    = "tcp"
+  }
+
+  ingress {
+    from_port   = 443
+    to_port     = 443
+    cidr_blocks = var.vpc_cidr
+    protocol    = "tcp"
+  }
+}
+
+resource "aws_vpc_endpoint" "snowflake_private_link" {
+  vpc_id              = var.vpc_id
+  service_name        = data.system_get_privatelink_config.aws_vpce_id
+  vpc_endpoint_type   = "Interface"
+  security_group_ids  = [aws_security_group.snowflake_private_link.id]
+  private_dns_enabled = false
+}
+
+resource "aws_route53_zone" "snowflake_private_link" {
+  name = "privatelink.snowflakecomputing.com"
+
+  vpc {
+    vpc_id = var.vpc_id
+  }
+}
+
+resource "aws_route53_record" "snowflake_private_link_url" {
+  zone_id = aws_route53_zone.snowflake_private_link.zone_id
+  name    = data.system_get_privatelink_config.snowflake_private_link.account_url
+  type    = "CNAME"
+  ttl     = "300"
+  records = [aws_vpc_endpoint.snowflake_private_link.dns_entry[0]["dns_name"]]
+}
+
+resource "aws_route53_record" "snowflake_private_link_oscp_url" {
+  zone_id = aws_route53_zone.snowflake_private_link_url.zone_id
+  name    = data.system_get_privatelink_config.snowflake_private_link.oscp_url
+  type    = "CNAME"
+  ttl     = "300"
+  records = [aws_vpc_endpoint.snowflake_private_link.dns_entry[0]["dns_name"]]
+}
+```
+
+<!-- schema generated by tfplugindocs -->
+## Schema
+
+### Optional
+
+- **id** (String) The ID of this resource.
+
+### Read-Only
+
+- **account_name** (String) The name of your Snowflake account.
+- **account_url** (String) The URL used to connect to Snowflake through AWS PrivateLink or Azure Private Link.
+- **aws_vpce_id** (String) The AWS VPCE ID for your account.
+- **azure_pls_id** (String) The Azure Private Link Service ID for your account.
+- **oscp_url** (String) The OCSP URL corresponding to your Snowflake account that uses AWS PrivateLink or Azure Private Link.
+
+
diff --git a/examples/data-sources/snowflake_system_get_privatelink_config/data-source.tf b/examples/data-sources/snowflake_system_get_privatelink_config/data-source.tf
new file mode 100644
index 00000000..f7d7ff1c
--- /dev/null
+++ b/examples/data-sources/snowflake_system_get_privatelink_config/data-source.tf
@@ -0,0 +1,51 @@
+data "system_get_privatelink_config" "snowflake_private_link" {}
+
+resource "aws_security_group" "snowflake_private_link" {
+  vpc_id = var.vpc_id
+
+  ingress {
+    from_port   = 80
+    to_port     = 80
+    cidr_blocks = var.vpc_cidr
+    protocol    = "tcp"
+  }
+
+  ingress {
+    from_port   = 443
+    to_port     = 443
+    cidr_blocks = var.vpc_cidr
+    protocol    = "tcp"
+  }
+}
+
+resource "aws_vpc_endpoint" "snowflake_private_link" {
+  vpc_id              = var.vpc_id
+  service_name        = data.system_get_privatelink_config.aws_vpce_id
+  vpc_endpoint_type   = "Interface"
+  security_group_ids  = [aws_security_group.snowflake_private_link.id]
+  private_dns_enabled = false
+}
+
+resource "aws_route53_zone" "snowflake_private_link" {
+  name = "privatelink.snowflakecomputing.com"
+
+  vpc {
+    vpc_id = var.vpc_id
+  }
+}
+
+resource "aws_route53_record" "snowflake_private_link_url" {
+  zone_id = aws_route53_zone.snowflake_private_link.zone_id
+  name    = data.system_get_privatelink_config.snowflake_private_link.account_url
+  type    = "CNAME"
+  ttl     = "300"
+  records = [aws_vpc_endpoint.snowflake_private_link.dns_entry[0]["dns_name"]]
+}
+
+resource "aws_route53_record" "snowflake_private_link_oscp_url" {
+  zone_id = aws_route53_zone.snowflake_private_link_url.zone_id
+  name    = data.system_get_privatelink_config.snowflake_private_link.oscp_url
+  type    = "CNAME"
+  ttl     = "300"
+  records = [aws_vpc_endpoint.snowflake_private_link.dns_entry[0]["dns_name"]]
+}
diff --git a/pkg/datasources/system_get_privatelink_config.go b/pkg/datasources/system_get_privatelink_config.go
new file mode 100644
index 00000000..2566976c
--- /dev/null
+++ b/pkg/datasources/system_get_privatelink_config.go
@@ -0,0 +1,86 @@
+package datasources
+
+import (
+	"database/sql"
+	"log"
+
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema"
+)
+
+var systemGetPrivateLinkConfigSchema = map[string]*schema.Schema{
+	"account_name": {
+		Type:        schema.TypeString,
+		Computed:    true,
+		Description: "The name of your Snowflake account.",
+	},
+
+	"account_url": {
+		Type:        schema.TypeString,
+		Computed:    true,
+		Description: "The URL used to connect to Snowflake through AWS PrivateLink or Azure Private Link.",
+	},
+
+	"oscp_url": {
+		Type:        schema.TypeString,
+		Computed:    true,
+		Description: "The OCSP URL corresponding to your Snowflake account that uses AWS PrivateLink or Azure Private Link.",
+	},
+
+	"aws_vpce_id": {
+		Type:        schema.TypeString,
+		Computed:    true,
+		Description: "The AWS VPCE ID for your account.",
+	},
+
+	"azure_pls_id": {
+		Type:        schema.TypeString,
+		Computed:    true,
+		Description: "The Azure Private Link Service ID for your account.",
+	},
+}
+
+func SystemGetPrivateLinkConfig() *schema.Resource {
+	return &schema.Resource{
+		Read:   ReadSystemGetPrivateLinkConfig,
+		Schema: systemGetPrivateLinkConfigSchema,
+	}
+}
+
+// ReadSystemGetPrivateLinkConfig implements schema.ReadFunc
+func ReadSystemGetPrivateLinkConfig(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+
+	sel := snowflake.SystemGetPrivateLinkConfigQuery()
+	row := snowflake.QueryRow(db, sel)
+	rawConfig, err := snowflake.ScanPrivateLinkConfig(row)
+
+	if err == sql.ErrNoRows {
+		// If not found, mark resource to be removed from statefile during apply or refresh
+		log.Print("[DEBUG] system_get_privatelink_config not found")
+		d.SetId("")
+		return nil
+	}
+
+	config, err := rawConfig.GetStructuredConfig()
+	if err != nil {
+		log.Printf("[DEBUG] system_get_privatelink_config failed to decode")
+		d.SetId("")
+		return nil
+	}
+
+	d.SetId(config.AccountName)
+	d.Set("account_name", config.AccountName)
+	d.Set("account_url", config.AccountURL)
+	d.Set("oscp_url", config.OSCPURL)
+
+	if config.AwsVpceID != "" {
+		d.Set("aws_vpce_id", config.AwsVpceID)
+	}
+
+	if config.AzurePrivateLinkServiceID != "" {
+		d.Set("azure_pls_id", config.AzurePrivateLinkServiceID)
+	}
+
+	return nil
+}
diff --git a/pkg/provider/provider.go b/pkg/provider/provider.go
index 931edd22..5c9a080a 100644
--- a/pkg/provider/provider.go
+++ b/pkg/provider/provider.go
@@ -122,11 +122,9 @@ func Provider() *schema.Provider {
 				DefaultFunc: schema.EnvDefaultFunc("SNOWFLAKE_REGION", "us-west-2"),
 			},
 		},
-		ResourcesMap: getResources(),
-		DataSourcesMap: map[string]*schema.Resource{
-			"snowflake_system_get_aws_sns_iam_policy": datasources.SystemGetAWSSNSIAMPolicy(),
-		},
-		ConfigureFunc: ConfigureProvider,
+		ResourcesMap:   getResources(),
+		DataSourcesMap: getDataSources(),
+		ConfigureFunc:  ConfigureProvider,
 	}
 }
 
@@ -188,6 +186,15 @@ func getResources() map[string]*schema.Resource {
 	)
 }
 
+func getDataSources() map[string]*schema.Resource {
+	dataSources := map[string]*schema.Resource{
+		"snowflake_system_get_aws_sns_iam_policy": datasources.SystemGetAWSSNSIAMPolicy(),
+		"snowflake_system_get_privatelink_config": datasources.SystemGetPrivateLinkConfig(),
+	}
+
+	return dataSources
+}
+
 func ConfigureProvider(s *schema.ResourceData) (interface{}, error) {
 	account := s.Get("account").(string)
 	user := s.Get("username").(string)
diff --git a/pkg/snowflake/system_get_privatelink_config.go b/pkg/snowflake/system_get_privatelink_config.go
new file mode 100644
index 00000000..2fc50de7
--- /dev/null
+++ b/pkg/snowflake/system_get_privatelink_config.go
@@ -0,0 +1,64 @@
+package snowflake
+
+import (
+	"encoding/json"
+
+	"github.com/jmoiron/sqlx"
+)
+
+func SystemGetPrivateLinkConfigQuery() string {
+	return `SELECT SYSTEM$GET_PRIVATELINK_CONFIG() AS "config"`
+}
+
+type RawPrivateLinkConfig struct {
+	Config string `db:"config"`
+}
+
+type privateLinkConfigInternal struct {
+	AccountName               string `json:"privatelink-account-name"`
+	AwsVpceID                 string `json:"privatelink-vpce-id,omitempty"`
+	AzurePrivateLinkServiceID string `json:"privatelink-pls-id,omitempty"`
+	AccountURL                string `json:"privatelink-account-url"`
+	OSCPURL                   string `json:"privatelink-ocsp-url,omitempty"`
+	TypodOSCPURL              string `json:"privatelink_ocsp-url,omitempty"` // because snowflake returns this for AWS, but don't have an Azure account to verify against
+}
+
+type PrivateLinkConfig struct {
+	AccountName               string
+	AwsVpceID                 string
+	AzurePrivateLinkServiceID string
+	AccountURL                string
+	OSCPURL                   string
+}
+
+func ScanPrivateLinkConfig(row *sqlx.Row) (*RawPrivateLinkConfig, error) {
+	config := &RawPrivateLinkConfig{}
+	err := row.StructScan(config)
+	return config, err
+}
+
+func (r *RawPrivateLinkConfig) GetStructuredConfig() (*PrivateLinkConfig, error) {
+	config := &privateLinkConfigInternal{}
+	err := json.Unmarshal([]byte(r.Config), config)
+	if err != nil {
+		return nil, err
+	}
+
+	return config.getPrivateLinkConfig()
+}
+
+func (i *privateLinkConfigInternal) getPrivateLinkConfig() (*PrivateLinkConfig, error) {
+	config := &PrivateLinkConfig{
+		i.AccountName,
+		i.AwsVpceID,
+		i.AzurePrivateLinkServiceID,
+		i.AccountURL,
+		i.OSCPURL,
+	}
+
+	if i.TypodOSCPURL != "" {
+		config.OSCPURL = i.TypodOSCPURL
+	}
+
+	return config, nil
+}


Test patch:
diff --git a/pkg/datasources/system_get_privatelink_config_acceptance_test.go b/pkg/datasources/system_get_privatelink_config_acceptance_test.go
new file mode 100644
index 00000000..eb1ec9f1
--- /dev/null
+++ b/pkg/datasources/system_get_privatelink_config_acceptance_test.go
@@ -0,0 +1,31 @@
+package datasources_test
+
+import (
+	"testing"
+
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/resource"
+)
+
+func TestAccSystemGetPrivateLinkConfig_aws(t *testing.T) {
+	resource.ParallelTest(t, resource.TestCase{
+		Providers: providers(),
+		Steps: []resource.TestStep{
+			{
+				Config: privateLinkConfig(),
+				Check: resource.ComposeTestCheckFunc(
+					resource.TestCheckResourceAttrSet("data.snowflake_system_get_privatelink_config.p", "account_name"),
+					resource.TestCheckResourceAttrSet("data.snowflake_system_get_privatelink_config.p", "account_url"),
+					resource.TestCheckResourceAttrSet("data.snowflake_system_get_privatelink_config.p", "oscp_url"),
+					resource.TestCheckResourceAttrSet("data.snowflake_system_get_privatelink_config.p", "aws_vpce_id"),
+				),
+			},
+		},
+	})
+}
+
+func privateLinkConfig() string {
+	s := `
+	data snowflake_system_get_privatelink_config p {}
+	`
+	return s
+}
diff --git a/pkg/snowflake/system_get_privatelink_config_test.go b/pkg/snowflake/system_get_privatelink_config_test.go
new file mode 100644
index 00000000..2a0e8c4f
--- /dev/null
+++ b/pkg/snowflake/system_get_privatelink_config_test.go
@@ -0,0 +1,65 @@
+package snowflake
+
+import (
+	"testing"
+
+	"github.com/stretchr/testify/require"
+)
+
+func TestSystemGetPrivateLinkConfigQuery(t *testing.T) {
+	r := require.New(t)
+	sb := SystemGetPrivateLinkConfigQuery()
+
+	r.Equal(sb, `SELECT SYSTEM$GET_PRIVATELINK_CONFIG() AS "config"`)
+}
+
+func TestSystemGetPrivateLinkGetStructuredConfigAws(t *testing.T) {
+	r := require.New(t)
+
+	raw := &RawPrivateLinkConfig{
+		Config: `{"privatelink-account-name":"ab1234.eu-west-1.privatelink","privatelink-vpce-id":"com.amazonaws.vpce.eu-west-1.vpce-svc-123456789abcdef12","privatelink-account-url":"ab1234.eu-west-1.privatelink.snowflakecomputing.com","privatelink_ocsp-url":"ocsp.ab1234.eu-west-1.privatelink.snowflakecomputing.com"}`,
+	}
+
+	c, e := raw.GetStructuredConfig()
+	r.Nil(e)
+
+	r.Equal("ab1234.eu-west-1.privatelink", c.AccountName)
+	r.Equal("com.amazonaws.vpce.eu-west-1.vpce-svc-123456789abcdef12", c.AwsVpceID)
+	r.Equal("", c.AzurePrivateLinkServiceID)
+	r.Equal("ab1234.eu-west-1.privatelink.snowflakecomputing.com", c.AccountURL)
+	r.Equal("ocsp.ab1234.eu-west-1.privatelink.snowflakecomputing.com", c.OSCPURL)
+}
+
+func TestSystemGetPrivateLinkGetStructuredConfigAwsAsPerDocumentation(t *testing.T) {
+	r := require.New(t)
+
+	raw := &RawPrivateLinkConfig{
+		Config: `{"privatelink-account-name":"ab1234.eu-west-1.privatelink","privatelink-vpce-id":"com.amazonaws.vpce.eu-west-1.vpce-svc-123456789abcdef12","privatelink-account-url":"ab1234.eu-west-1.privatelink.snowflakecomputing.com","privatelink-ocsp-url":"ocsp.ab1234.eu-west-1.privatelink.snowflakecomputing.com"}`,
+	}
+
+	c, e := raw.GetStructuredConfig()
+	r.Nil(e)
+
+	r.Equal("ab1234.eu-west-1.privatelink", c.AccountName)
+	r.Equal("com.amazonaws.vpce.eu-west-1.vpce-svc-123456789abcdef12", c.AwsVpceID)
+	r.Equal("", c.AzurePrivateLinkServiceID)
+	r.Equal("ab1234.eu-west-1.privatelink.snowflakecomputing.com", c.AccountURL)
+	r.Equal("ocsp.ab1234.eu-west-1.privatelink.snowflakecomputing.com", c.OSCPURL)
+}
+
+func TestSystemGetPrivateLinkGetStructuredConfigAzure(t *testing.T) {
+	r := require.New(t)
+
+	raw := &RawPrivateLinkConfig{
+		Config: `{"privatelink-account-name":"ab1234.east-us-2.azure.privatelink","privatelink-pls-id":"sf-pvlinksvc-azeastus2.east-us-2.azure.something","privatelink-account-url":"ab1234.east-us-2.azure.privatelink.snowflakecomputing.com","privatelink_ocsp-url":"ocsp.ab1234.east-us-2.azure.privatelink.snowflakecomputing.com"}`,
+	}
+
+	c, e := raw.GetStructuredConfig()
+	r.Nil(e)
+
+	r.Equal("ab1234.east-us-2.azure.privatelink", c.AccountName)
+	r.Equal("", c.AwsVpceID)
+	r.Equal("sf-pvlinksvc-azeastus2.east-us-2.azure.something", c.AzurePrivateLinkServiceID)
+	r.Equal("ab1234.east-us-2.azure.privatelink.snowflakecomputing.com", c.AccountURL)
+	r.Equal("ocsp.ab1234.east-us-2.azure.privatelink.snowflakecomputing.com", c.OSCPURL)
+}