terraform-snowflake-rbac-infra

The infrastructure stack deploys snowflake databases, warehouses, roles, and grants based on the configuration in the ./config directory. See Configuration below.

Prerequisites

AWS Authentication Requirements

Terraform needs credentials to connect to the remote backend. Multiple configurations are available, and Terraform provides complete documentation on how to set up the credentials. It's best practice to use temporary credentials to connect GitHub with AWS.

Once the above is complete, you must set up an environment in GitHub Settings (development, production) and add a secret to it, AWS_ROLE_ARN, with the role ARN created during the instructions above.

Snowflake Authentication provider requirements

In Terraform, each provider requires credentials to manage resources on our behalf. Below, you will find the variables we use to connect to Snowflake.

• account - (required) Both the name and the region (ex: corp.us-east-1). It can also come from the SNOWFLAKE_ACCOUNT environment variable.
• user - (required) It can come from the SNOWFLAKE_USER environment variable.
• private_key - (required) A private key for using keypair authentication. It can be a source from the SNOWFLAKE_PRIVATE_KEY environment variable.
• role - (optional) Snowflake role to use for operations. If left unset, the user's default role will be used. It can come from the SNOWFLAKE_ROLE environment variable.
• authenticator - (required) When using private_key you must specify authenticator = "JWT" otherwise Terraform will return Error: 260002: password is empty.

The developer will configure the account, username, role, and authenticator in the terraform .tfvars file.

Snowflake User key Creation

If you don't already have a dedicated user in your Snowflake account for running Terraform, see the offical documentation for up-to-date instructions.

In your development environment, run the following command to generate a key pair:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out terraform.p8 -nocrypt
openssl rsa -in terraform.p8 -pubout -out terraform.pub

The next step is to associate the public key with your snowflake user. In the Snowflake console, execute the create user command with the USERADMIN role, exclude the public key delimiters in the SQL statement. Execute the grant role commands with the SECURITYADMIN role.

create user TERRAFORM rsa_public_key='MIIBIjANBgkqh...' TYPE = SERVICE;
grant role SYSADMIN to user TERRAFORM;
grant role SECURITYADMIN to user TERRAFORM;
grant role ACCOUNTADMIN to user TERRAFORM;

You can execute a DESCRIBE USER command to verify the user's public key.

desc user TERRAFORM;

The private key must be created as a GitHub environment secret named SNOWFLAKE_PRIVATE_KEY in each environment.

Configuration

File	Description
config/roles.yml	The roles file is used to grant access roles to the environment functional roles (functional_roles) as well as to grant the environment functional roles to the top-level account roles. (account_roles)
config/permissions.yml	The permissions file is used to specify the grants that are to be assigned to the corresponding object access roles, it is a lookup for the object-level grants
config/databases.yml	The databases file is used to specify the databases to be created and the object access roles that should be created under each database
config/warehouses.yml	The warehouses file is used to specify the warehouses to be created, as well as the environment functional role permissions to be granted to the warehouse
config/users.yml	The users file is used to specify the users to be created, as well as to set the default warehouse, default role and grant the default role to the user

Adding roles

Functional roles

1. Navigate to the config/roles.yml file
2. If creating an environment level role add a new key:value sequences under functional_roles
   1. The key should be the name of the role (terraform will prepend the environment and project)
   2. The value should be a list of object access roles defined in config/databases.yml
3. If creating an account level role add a new key:value sequences under account_roles
   1. The key should be the name of the role you would like to create
   2. The value should be the function roles that you would like to grant to the account level role (Do not grant object level roles)

Object roles

1. Navigate to the config/databases.yml file
2. We create either a read (r) or a read/write (rw) role under each named database. We have a key:value sequences under each database name, the key is roles the value is an sequences of role names (r, rw)
3. For each of the roles we defined in step 2 we must create a lookup to define the permissions we want to set to the r and rw roles, we define the permissions in config/permissions.yml
   1. Navigate to config/permissions.yml
   2. Each object database, warehouse, etc has its own key:value sequences to define the object we want to grant permissions on key and the permissions we want to grant value this is repeated for each role we defined in step 2

Adding Databases

1. Navigate to the config/databases.yml file
2. Add a new block with the name of the new database (terraform will prepend the environment and project) and the object roles you would like to create under the new database name (r, rw)

  database_name: 
    roles:
      - r
      - rw

Adding Warehouses

1. Navigate to the config/warehouses.yml file
2. Add a new block with the name of the new warehouse (terraform will prepend the environment and project) under the key:value mapping
   1. Under the new key you can add a key:value literal to set the auto_suspend or size on the warehouse
   2. Under the new key you can add a key:value mapping called roles to grant permissions to functional roles defined in config/roles.yml. The key is the role name and the value is a sequences of permissions on the warehouse to grant

  warehouse_name:
    auto_suspend: 60
    roles:
      analyst:
        - usage
        - operate

Adding Users

1. Navigate to the config/users.yml file
2. Under the key:value mapping users you can define the name of the role key (terraform will prepend the environment and project)
3. The value under the mapping created on step 2 can be either the role or the warehouse to assign to the user
   • To grant a role to the user add a key:value literal with role: name_of_role
   • To grant access to use a warehouse to the user add a key:value literal with warehouse: name_of_warehouse
   • To set the user type add a key:value literal with type: type_name, where type_name is one of (person, service, legacy_service).
     • When not specified it is the same as setting it to person i.e. type: person
     • Unfortunately, at the moment user_type property of snowflake_user resource is read-only and cannot be set, therefore setting type to person is exactly the same as omitting the type altogether.

users:
  dbt:
    role: transform
    warehouse: transform
    type: service
  jsmith:
    role: developer
    warehouse: developer
  jdoe:
    role: developer
    warehouse: developer
    type: person

Variables

Name	Description	Type	Default	Required
always_apply	Toggle to always apply on all objects. Used for when there are changes to the grants that need to be retroatively granted to roles	bool	false	no
comment	A comment to apply to all resources	string	"Created by terraform"	no
create_parent_roles	Whether or not you want to create the parent roles (for production deployment only)	bool	false	no
default_tags	Default tags to apply to all Snowflake resources	map(string)	n/a	yes
default_warehouse_auto_suspend	The auto_suspend (seconds) of the Snowflake warehouse that we will be utilizing to run queries in the snowflake_account	number	600	no
default_warehouse_size	The size of the Snowflake warehouse that we will be utilizing to run queries in the snowflake_account	string	"xsmall"	no
environment	The name of the environment we are deploying, for environment separation and naming purposes	string	n/a	yes
governance_database_name	The name to set for governance database	string	"GOVERNANCE"	no
project	The name of the project, for naming and tagging purposes	string	""	no
region	The AWS region that we will deploy into, as well as for naming purposes	string	n/a	yes
snowflake_account	The name of the Snowflake account that we will be deploying into	string	n/a	yes
snowflake_role	The role in Snowflake that we will use to deploy by default	string	n/a	yes
snowflake_user	The name of the Snowflake user that we will be utilizing to deploy into the snowflake_account	string	n/a	yes
tag_admin_role	The name to set for the tag admin	string	"TAG_ADMIN"	no
tags	Tags and their allowed values to create in Snowflake. This will also create a database and schema to house the tags	map(list(string))	{}	no
tags_schema_name	The name to set for tags schema	string	"TAGS"	no

Tagging

Tags are created in var.tags where we define the tag key (string) and the allowed tag values (list), this variable must only be defined once, it is recommended to define this variable in the environments/production.tfvars file to maintain control of approved values within the production environment.

The tags that we apply to Snowflake objects (databases and warehouses) are defined in the var.default_tags. The database and schema where the tags are stored are defined within the tags.tf file and are only created if the var.tags variable length() is greater than 0.

Adding a tag

To add a tag, navigate to the environments/production.tfvars file and add a key:value pair containing the tag name in capital letters and list of approved values to the var.tags variable.

Applying a tag

To apply a tag to databases and warehouses, in the environment of your choice, environments/production.tfvars or environments/development.tfvars, add the tag key:value pair to var.default_tags, ensure that this tag has been added to var.tags and has been deployed to Snowflake before proceeding with the deployment of the tag association.

Deployment

1. Update the backends/backend-{env}.tfvars file to point to the appropriate S3 backend (if required)
2. Update the environments/{env}.tfvars file with any variable changes that may be required
3. Navigate to GitHub Actions and trigger Plan Snowflake Infra
   1. Select Run Workflow
   2. From the drop down menu choose the target environment from Plan to
   3. Select Run Workflow and verify that the plan is showing what we want to deploy is expected
4. Navigate to GitHub Actions and trigger Deploy Snowflake Infra
   1. Select Run Workflow
   2. From the drop down menu choose the target environment from Plan to
   3. (Optional) If you have added a new role which requires grants to all existing objects, you can set Re-run all grants to true
   4. Select Run Workflow and to deploy the changes verified above