Repository: chanzuckerberg/terraform-provider-snowflake
Pull request #540 (2021-05-12 16:50:38)

feat: Resource to manage a user's public keys
<!-- Feel free to delete comments as you fill this in -->

<!-- summary of changes -->
A resource to manage a user's public keys. Note that this resource is intended to be used when users are managed through an external source (eg SCIM) and it'll conflict with the `snowflake_user` resource.

## Test Plan
Unit tests

## References 

github-actions[bot]: Integration tests failure for [753ffcd02bcb5867a719374ccec3454c420d8795](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836219459)
github-actions[bot]: Integration tests failure for [15d6480cd8369d31600f5ebd87a25cee0ec0964c](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836230206)
github-actions[bot]: Integration tests success for [9de687bce342358641d12115fea9a49e29f5d1bb](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836244652)
github-actions[bot]: Integration tests failure for [00bbb3376046bfbb5a7143b8c2dec2ddffc697b1](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836355018)
github-actions[bot]: Integration tests failure for [7506e880d57746a4ddd711ca82a76ac75592c705](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836357921)
github-actions[bot]: Integration tests failure for [6193d6d77d40ae20ddaa5eec2bc3438d09667498](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836403203)
github-actions[bot]: Integration tests failure for [9b397910dc834081bb4986257fafddb2db2d119e](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836461863)
github-actions[bot]: Integration tests failure for [19f30ee63602e3f238ed511ebc6ed076594e3622](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836560820)
github-actions[bot]: Integration tests failure for [426e10ec0752f76500a95402f691193e6ad7825b](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836582017)
github-actions[bot]: Integration tests success for [f867d2f77815de965c64224011d33dec0780cc1e](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836635978)
github-actions[bot]: Integration tests success for [366dd0fe391fb4e37c953c0afe41ef3f5da33b03](https://github.com/chanzuckerberg/terraform-provider-snowflake/actions/runs/836634912)

Patch:
diff --git a/docs/resources/user_public_keys.md b/docs/resources/user_public_keys.md
new file mode 100644
index 00000000..decaa24c
--- /dev/null
+++ b/docs/resources/user_public_keys.md
@@ -0,0 +1,28 @@
+---
+# generated by https://github.com/hashicorp/terraform-plugin-docs
+page_title: "snowflake_user_public_keys Resource - terraform-provider-snowflake"
+subcategory: ""
+description: |-
+  
+---
+
+# snowflake_user_public_keys (Resource)
+
+
+
+
+
+<!-- schema generated by tfplugindocs -->
+## Schema
+
+### Required
+
+- **name** (String) Name of the user.
+
+### Optional
+
+- **id** (String) The ID of this resource.
+- **rsa_public_key** (String) Specifies the user’s RSA public key; used for key-pair authentication. Must be on 1 line without header and trailer.
+- **rsa_public_key_2** (String) Specifies the user’s second RSA public key; used to rotate the public and Public keys for key-pair authentication based on an expiration schedule set by your organization. Must be on 1 line without header and trailer.
+
+
diff --git a/go.sum b/go.sum
index 5acd58f0..d55ff7c6 100644
--- a/go.sum
+++ b/go.sum
@@ -669,7 +669,6 @@ github.com/spf13/viper v1.3.2/go.mod h1:ZiWeW+zYFKm7srdB9IoDzzZXaJaI5eL9QjNiN/DM
 github.com/spf13/viper v1.4.0/go.mod h1:PTJ7Z/lr49W6bUbkmS1V3by4uWynFiR9p7+dSq/yZzE=
 github.com/stretchr/objx v0.1.0/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
 github.com/stretchr/objx v0.1.1/go.mod h1:HFkY916IF+rwdDfMAkV7OtwuqBVzrE8GR6GFx+wExME=
-github.com/stretchr/objx v0.2.0 h1:Hbg2NidpLE8veEBkEZTL3CvlkUIVzuU9jDplZO54c48=
 github.com/stretchr/objx v0.2.0/go.mod h1:qt09Ya8vawLte6SNmTgCsAVtYtaKzEcn8ATUoHMkEqE=
 github.com/stretchr/testify v0.0.0-20151208002404-e3a8ff8ce365/go.mod h1:a8OnRcib4nhh0OaRAV+Yts87kKdq0PP7pXfy6kDkUVs=
 github.com/stretchr/testify v1.2.0/go.mod h1:a8OnRcib4nhh0OaRAV+Yts87kKdq0PP7pXfy6kDkUVs=
diff --git a/pkg/provider/provider.go b/pkg/provider/provider.go
index 0c2441b0..931edd22 100644
--- a/pkg/provider/provider.go
+++ b/pkg/provider/provider.go
@@ -154,6 +154,7 @@ func GetGrantResources() resources.TerraformGrantResources {
 }
 
 func getResources() map[string]*schema.Resource {
+	// NOTE(): do not add grant resources here
 	others := map[string]*schema.Resource{
 		"snowflake_api_integration":           resources.APIIntegration(),
 		"snowflake_database":                  resources.Database(),
@@ -165,8 +166,8 @@ func getResources() map[string]*schema.Resource {
 		"snowflake_network_policy":            resources.NetworkPolicy(),
 		"snowflake_pipe":                      resources.Pipe(),
 		"snowflake_resource_monitor":          resources.ResourceMonitor(),
-		"snowflake_role_grants":               resources.RoleGrants(),
 		"snowflake_role":                      resources.Role(),
+		"snowflake_role_grants":               resources.RoleGrants(),
 		"snowflake_schema":                    resources.Schema(),
 		"snowflake_share":                     resources.Share(),
 		"snowflake_stage":                     resources.Stage(),
@@ -176,6 +177,7 @@ func getResources() map[string]*schema.Resource {
 		"snowflake_external_table":            resources.ExternalTable(),
 		"snowflake_task":                      resources.Task(),
 		"snowflake_user":                      resources.User(),
+		"snowflake_user_public_keys":          resources.UserPublicKeys(),
 		"snowflake_view":                      resources.View(),
 		"snowflake_warehouse":                 resources.Warehouse(),
 	}
diff --git a/pkg/resources/user_public_keys.go b/pkg/resources/user_public_keys.go
new file mode 100644
index 00000000..d05dbc01
--- /dev/null
+++ b/pkg/resources/user_public_keys.go
@@ -0,0 +1,176 @@
+package resources
+
+import (
+	"database/sql"
+	"fmt"
+	"log"
+	"strings"
+
+	"github.com/chanzuckerberg/go-misc/sets"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/snowflake"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema"
+)
+
+var userPublicKeyProperties = []string{
+	"rsa_public_key",
+	"rsa_public_key_2",
+}
+
+// sanitize input to supress diffs, etc
+func publicKeyStateFunc(v interface{}) string {
+	value := v.(string)
+	value = strings.TrimSuffix(value, "\n")
+	return value
+}
+
+var userPublicKeysSchema = map[string]*schema.Schema{
+	"name": {
+		Type:        schema.TypeString,
+		Required:    true,
+		Description: "Name of the user.",
+		ForceNew:    true,
+	},
+
+	"rsa_public_key": {
+		Type:        schema.TypeString,
+		Optional:    true,
+		Description: "Specifies the user’s RSA public key; used for key-pair authentication. Must be on 1 line without header and trailer.",
+		StateFunc:   publicKeyStateFunc,
+	},
+	"rsa_public_key_2": {
+		Type:        schema.TypeString,
+		Optional:    true,
+		Description: "Specifies the user’s second RSA public key; used to rotate the public and Public keys for key-pair authentication based on an expiration schedule set by your organization. Must be on 1 line without header and trailer.",
+		StateFunc:   publicKeyStateFunc,
+	},
+}
+
+func UserPublicKeys() *schema.Resource {
+	return &schema.Resource{
+		Create: CreateUserPublicKeys,
+		Read:   ReadUserPublicKeys,
+		Update: UpdateUserPublicKeys,
+		Delete: DeleteUserPublicKeys,
+
+		Schema: userPublicKeysSchema,
+		Importer: &schema.ResourceImporter{
+			StateContext: schema.ImportStatePassthroughContext,
+		},
+	}
+}
+
+func checkUserExists(db *sql.DB, name string) (bool, error) {
+	// First check if user exists
+	stmt := snowflake.User(name).Show()
+	row := snowflake.QueryRow(db, stmt)
+	_, err := snowflake.ScanUser(row)
+	if err == sql.ErrNoRows {
+		log.Printf("[DEBUG] user (%s) not found", name)
+		return false, nil
+	}
+	if err != nil {
+		return false, err
+	}
+
+	return true, nil
+}
+
+func ReadUserPublicKeys(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	id := d.Id()
+
+	exists, err := checkUserExists(db, id)
+	if err != nil {
+		return err
+	}
+	// If not found, mark resource to be removed from statefile during apply or refresh
+	if !exists {
+		d.SetId("")
+		return nil
+	}
+	// we can't really read the public keys back from Snowflake so assume they haven't changed
+	return nil
+}
+
+func CreateUserPublicKeys(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	name := d.Get("name").(string)
+
+	for _, prop := range userPublicKeyProperties {
+		publicKey, publicKeyOK := d.GetOk(prop)
+		if !publicKeyOK {
+			continue
+		}
+		err := updateUserPublicKeys(db, name, prop, publicKey.(string))
+		if err != nil {
+			return err
+		}
+	}
+
+	d.SetId(name)
+	return ReadUserPublicKeys(d, meta)
+}
+
+func UpdateUserPublicKeys(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	name := d.Id()
+
+	propsToSet := map[string]string{}
+	propsToUnset := sets.NewStringSet()
+
+	for _, prop := range userPublicKeyProperties {
+		// if key hasn't changed, continue
+		if !d.HasChange(prop) {
+			continue
+		}
+		// if it has changed then we should do something about it
+		publicKey, publicKeyOK := d.GetOk(prop)
+		if publicKeyOK { // if set, then we should update the value
+			propsToSet[prop] = publicKey.(string)
+		} else { // if now unset, we should unset the key from the user
+			propsToUnset.Add(publicKey.(string))
+		}
+	}
+
+	// set the keys we decided should be set
+	for prop, value := range propsToSet {
+		err := updateUserPublicKeys(db, name, prop, value)
+		if err != nil {
+			return err
+		}
+	}
+
+	// unset the keys we decided should be unset
+	for _, prop := range propsToUnset.List() {
+		err := unsetUserPublicKeys(db, name, prop)
+		if err != nil {
+			return err
+		}
+	}
+	// re-sync
+	return ReadUserPublicKeys(d, meta)
+}
+
+func DeleteUserPublicKeys(d *schema.ResourceData, meta interface{}) error {
+	db := meta.(*sql.DB)
+	name := d.Id()
+
+	for _, prop := range userPublicKeyProperties {
+		err := unsetUserPublicKeys(db, name, prop)
+		if err != nil {
+			return err
+		}
+	}
+
+	d.SetId("")
+	return nil
+}
+
+func updateUserPublicKeys(db *sql.DB, name string, prop string, value string) error {
+	stmt := fmt.Sprintf(`ALTER USER "%s" SET %s = '%s'`, name, prop, value)
+	return snowflake.Exec(db, stmt)
+}
+func unsetUserPublicKeys(db *sql.DB, name string, prop string) error {
+	stmt := fmt.Sprintf(`ALTER USER "%s" UNSET %s`, name, prop)
+	return snowflake.Exec(db, stmt)
+}
diff --git a/pkg/snowflake/user.go b/pkg/snowflake/user.go
index ecd46057..c213cabd 100644
--- a/pkg/snowflake/user.go
+++ b/pkg/snowflake/user.go
@@ -33,3 +33,8 @@ func ScanUser(row *sqlx.Row) (*user, error) {
 	err := row.StructScan(r)
 	return r, err
 }
+
+type DescribeUserProp struct {
+	Property string         `db:"property"`
+	Value    sql.NullString `db:"value"`
+}


Test patch:
diff --git a/pkg/resources/user_public_keys_acceptance_test.go b/pkg/resources/user_public_keys_acceptance_test.go
new file mode 100644
index 00000000..865561c3
--- /dev/null
+++ b/pkg/resources/user_public_keys_acceptance_test.go
@@ -0,0 +1,87 @@
+package resources_test
+
+import (
+	"bytes"
+	"strings"
+	"testing"
+	"text/template"
+
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/acctest"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/resource"
+	"github.com/stretchr/testify/require"
+)
+
+func TestAcc_UserPublicKeys(t *testing.T) {
+	r := require.New(t)
+	prefix := strings.ToUpper(acctest.RandStringFromCharSet(10, acctest.CharSetAlpha))
+	sshkey1, err := testhelpers.Fixture("userkey1")
+	r.NoError(err)
+	sshkey2, err := testhelpers.Fixture("userkey2")
+	r.NoError(err)
+
+	resource.ParallelTest(t, resource.TestCase{
+		Providers: providers(),
+		Steps: []resource.TestStep{
+			{
+				Config: uPublicKeysConfig(r, PublicKeyData{
+					Prefix:     prefix,
+					PublicKey1: sshkey1,
+					PublicKey2: sshkey2,
+				}),
+				Check: resource.ComposeTestCheckFunc(
+					resource.TestCheckResourceAttr("snowflake_user.w", "name", prefix),
+
+					resource.TestCheckResourceAttr("snowflake_user_public_keys.foobar", "rsa_public_key", sshkey1),
+					resource.TestCheckResourceAttr("snowflake_user_public_keys.foobar", "rsa_public_key_2", sshkey2),
+				),
+			},
+			// IMPORT
+			{
+				ResourceName:            "snowflake_user.w",
+				ImportState:             true,
+				ImportStateVerify:       true,
+				ImportStateVerifyIgnore: []string{"password", "rsa_public_key", "rsa_public_key_2", "must_change_password"},
+			},
+		},
+	})
+}
+
+type PublicKeyData struct {
+	Prefix     string
+	PublicKey1 string
+	PublicKey2 string
+}
+
+func uPublicKeysConfig(r *require.Assertions, data PublicKeyData) string {
+	t := `
+resource "snowflake_user" "w" {
+	name = "{{.Prefix}}"
+	comment = "test comment"
+	login_name = "{{.Prefix}}_login"
+	display_name = "Display Name"
+	first_name = "Marcin"
+	last_name = "Zukowski"
+	email = "fake@email.com"
+	disabled = false
+	default_warehouse="foo"
+	default_role="foo"
+	default_namespace="foo"
+}
+
+resource "snowflake_user_public_keys" "foobar" {
+	name = snowflake_user.w.name
+	rsa_public_key = <<KEY
+{{ .PublicKey1 }}
+	KEY
+
+	rsa_public_key_2 = <<KEY
+{{ .PublicKey2 }}
+	KEY
+}
+`
+	conf := bytes.NewBuffer(nil)
+	err := template.Must(template.New("user").Parse(t)).Execute(conf, data)
+	r.NoError(err)
+	return conf.String()
+}
diff --git a/pkg/resources/user_public_keys_test.go b/pkg/resources/user_public_keys_test.go
new file mode 100644
index 00000000..b2c75399
--- /dev/null
+++ b/pkg/resources/user_public_keys_test.go
@@ -0,0 +1,76 @@
+package resources_test
+
+import (
+	"database/sql"
+	"database/sql/driver"
+	"testing"
+
+	sqlmock "github.com/DATA-DOG/go-sqlmock"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/provider"
+	"github.com/chanzuckerberg/terraform-provider-snowflake/pkg/resources"
+	. "github.com/chanzuckerberg/terraform-provider-snowflake/pkg/testhelpers"
+	"github.com/hashicorp/terraform-plugin-sdk/v2/helper/schema"
+	"github.com/stretchr/testify/require"
+)
+
+func TestUserPublicKeys(t *testing.T) {
+	r := require.New(t)
+	err := resources.UserPublicKeys().InternalValidate(provider.Provider().Schema, true)
+	r.NoError(err)
+}
+
+func rowsFromMap(in map[string]string) *sqlmock.Rows {
+	cols := []string{}
+	vals := []driver.Value{}
+	for col, val := range in {
+		cols = append(cols, col)
+		vals = append(vals, val)
+	}
+	rows := sqlmock.NewRows(cols)
+	rows.AddRow(vals...)
+	return rows
+}
+
+func TestUserPublicKeysCreate(t *testing.T) {
+	r := require.New(t)
+
+	in := map[string]interface{}{
+		"name":             "good_name",
+		"rsa_public_key":   "asdf",
+		"rsa_public_key_2": "asdf2",
+	}
+	d := schema.TestResourceDataRaw(t, resources.UserPublicKeys().Schema, in)
+	r.NotNil(d)
+
+	rows := map[string]string{
+		"name": "good_name",
+	}
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`ALTER USER "good_name" SET rsa_public_key = 'asdf'`).WillReturnResult(sqlmock.NewResult(1, 1))
+		mock.ExpectExec(`ALTER USER "good_name" SET rsa_public_key_2 = 'asdf2'`).WillReturnResult(sqlmock.NewResult(1, 1))
+		mock.ExpectQuery(`SHOW USERS LIKE 'good_name'`).WillReturnRows(rowsFromMap(rows))
+		err := resources.CreateUserPublicKeys(d, db)
+		r.NoError(err)
+
+		r.Equal(in["name"], d.Id())
+	})
+}
+
+func TestUsePublicKeysrDelete(t *testing.T) {
+	r := require.New(t)
+	in := map[string]interface{}{
+		"name":             "good_name",
+		"rsa_public_key":   "asdf",
+		"rsa_public_key_2": "asdf2",
+	}
+	d := schema.TestResourceDataRaw(t, resources.UserPublicKeys().Schema, in)
+	d.SetId(in["name"].(string))
+
+	WithMockDb(t, func(db *sql.DB, mock sqlmock.Sqlmock) {
+		mock.ExpectExec(`ALTER USER "good_name" UNSET rsa_public_key`).WillReturnResult(sqlmock.NewResult(1, 1))
+		mock.ExpectExec(`ALTER USER "good_name" UNSET rsa_public_key_2`).WillReturnResult(sqlmock.NewResult(1, 1))
+		err := resources.DeleteUserPublicKeys(d, db)
+		r.NoError(err)
+	})
+}