Snowflake Playground

Terraform module for creating a 'Playground' environment within a Snowflake account. For users, a Playground environment in Snowflake is a place where they know that they have write permissions, and are free to create and play without worry regarding permissions. For Administrators, it is an environment that they know will self-clean and don't need to manually manage.

See architecture.md for details on the structure of the Playground.

Why should I create a Playground environment?

If you experience any of the following in your Snowflake account, then a Playground environment may be useful:

• Users creating objects for development, but then forgetting to delete them.
  • You're then left with a schema with tens, hundreds, thousands of objects that you don't know what they are for. Is it safe to delete them, or are they something important? Maybe the person who created the object has left so you can't even ask them any more.
• Users deploying Production systems to development environments - the continual clean-up of the Playground environment will discourage the deployment of production systems in the Playground.
• Users (especially new users) not knowing where to create objects, because they aren't sure where they have permissions.

Solving these issues manually can be incredibly time consuming and wasteful. A Playground area is a one-stop-shop to help resolve these issues, and give users a clear and safe space to experiment.

Disclaimer

Use of this Terraform Module is done at your own risk. The authors have made their best efforts to ensure that it works as intended. You should test the playground before implementation to ensure that it works as you desire. The authors are not responsible for any loss of data that may occur as a result of your use or deployment of this module.

Prerequisites

• Snowflake Enterprise Edition Account (or above)
  • The module requires object tagging, which is only available in Enterprise edition accounts (and above).

Getting Started

The Snowflake Playground Terraform Module requires the Snowflake Terraform Provider.

Once you have configured your Snowflake Provider, you can deploy this module using:

module "playground" {
    source  = "jagoodhand/playground/snowflake"
}

By default this will use your configured Snowflake provider to deploy a Playground into your Snowflake account with the configuration:

• Playground Schema: PLAY.GROUND
• Administration Schema: PLAY.ADMINISTRATION
• Expiry Date Tag: PLAY.ADMINISTRATION.EXPIRY_DATE
• Playground Warehouse: PLAYGROUND_ADMIN_WAREHOUSE
  • Size: X-SMALL
• TimeTravel for the Playground schema: 1 day
• Standard max object age: 31
• Max allowed expiry-date tag: 90 days in future
• Automation schedule (disabled by default): "USING CRON 0 3 * * * UTC"

For safety, the playground automation is disabled by default. Set the following inputs to enable the automation:

module "playground" {
    source  = "jagoodhand/playground/snowflake"

    dry_run = false
    tasks_enabled = true
}

For a basic deployment example, see this main.tf, and its README.md.

Playground Automation Logic

The Playground Automation (once enabled) ensures that the Playground doesn't become a dumping ground for all data, and discourages the building of production systems within the Playground.

ℹ️ Note: The automation will not stop someone from deploying a production system to the Playground if they were willing to continually update their tags.

Rule 1: Any untagged object will be dropped after the number of days specified by the max_object_age_without_tag variable.

Rule 2: Any object tagged with the EXPIRY_DATE tag, where the EXPIRY_DATE of the tag has passed, will be dropped.

Rule 3: Any EXPIRY_DATE tag, with a value greater than the number of days in the future specified by the max_expiry_days variable, will have their EXPIRY_DATE tag altered to be max_expiry_days in the future.

Examples

The examples below describe the expected behaviour of the automation in each scenario.

For these examples, we will assume that:

• The current date is 2023-01-01
• max_object_age_without_tag = 30
• max_expiry_days = 60

Table Name	Created Date	Expiry Date Value	Expected Behaviour
TABLE_1	2022-11-01	null	Drop TABLE_1
TABLE_2	2022-11-01	2023-01-31	Do nothing
TABLE_3	2022-11-01	2023-06-01	Alter Expiry Date tag for TABLE_3 to be 2023-02-02
TABLE_4	2022-12-20	null	Do nothing

Security / Permissions

This Playground primarily relies on Snowflake Managed Access Schemas. Using Managed Access Schemas means that the users who created the object, are not able to control the grants on that object. In the Playground, only the owning role (defined in the Snowflake Provider configuration), and those roles that inherit it are able to control the grants on objects.

ℹ️ Note: Even though the roles with ownership on the Playground objects can manual grant permissions on objects in the Playground, they should not. If users are requesting manual grants, then they are likely abusing the purpose of the Playground environment.

Principles:

• Objects created in the Playground are only usable by the role that created them (or another role that inherits the creating role).
• Additional grants in the Playground must not be created. If users need to share data between themselves or roles, the Playground is not the place to do it.
• An administration role should be the owner of the Playground

It is recommended to use SYSADMIN or ACCOUNTADMIN as the owners of the Playground. Which of these is most appropriate is likely to depend on how you operate your account. There are pros and cons to using either:

• Using SYSADMIN follows the principle of least privilege (any role below SYSADMIN is likely to not have the necessary permissions to properly manage the Playground, but this may vary depending on the configuration of your Snowflake account)
• Using ACCOUNTADMIN ensures that only top-level administrators can manage the Playground and the objects within it.

The operation of these principles is illustrated in Diagram 1.

Diagram 1 - Illustration of object access control within the Playground.

ROLE_1 cannot 'see', or operate on TABLE_2, and ROLE_2 cannot 'see' or operate on TABLE_1.

ROLE_1 / ROLE_2 can only know about the existence of TABLE_2 / TABLE_1 through the SNOWFLAKE.ACCOUNT_USAGE information, or by attempting to create a table of the same name at which time an error will occur.

❗ WARNING: Snowflake Secondary Roles can enable users to move data in unexpected ways. This is described below. You should be aware of this before deploying the Playground to your Snowflake account. Alternatively, you can request that Snowflake disables Secondary Roles in your account through a Support Ticket.

Snowflake Secondary Role Implications

Snowflake have introduced the concept of Secondary Roles.

Diagram 2 - Illustration of how Secondary Roles effect table access.

Prior to Secondary Roles, even if USER_1 had access to ROLE_1 and ROLE_2, they would not be able to read data from ROLE_2, and write it so that ROLE_1 then had access to it i.e. it wouldn’t be possible to do:

USE ROLE1;
CREATE TABLE_1 AS SELECT * FROM TABLE_2;

With Secondary Roles, this is now possible. USER_2 would now have access to data that originally was only available to users of ROLE_2. This isn’t an issue unique to the Snowflake Playground. It can occur anywhere in your Snowflake account, but it is something to be aware of. A user could use this as a method of exposing sensitive data to other users who did not have permission to initially read it. You should therefore assume that any user can make data that they can read available to any other user whom they share a role with write permissions with.

If you wish to make this data transfer more difficult, it is recommended that you raise a Support Ticket with Snowflake to disable Secondary Roles on your Snowflake Account.

Limitations

• Tagging information utilised by the module may be up to 3 hours out of date. This is because it is only currently possible to bulk-source tag references in Snowflake from the SNOWFLAKE.ACCOUNT_USAGE schema. Thus it is recommended that the automation is only run once daily, and at least 3 hours after midnight to account for the delay.
• Two users cannot create an object of the same name within the Playground.

Object Types

The logic within the Playground Automation will operate on the following Snowflake object types:

• Tables
• External Tables
• Views
• Materialized Views
• Pipes
• Procedures
• Stages
• Streams
• Tasks

The logic will not operate on the following Snowflake object types, because they cannot be tagged:

• Tags
• File Formats
• Functions
• Masking Policies
• Row Access Policies
• Sequences

Some of the logic in this module treats tables, external tables views, and materialized views as the same because these objects cannot share the same name.

Contributing

Report issues/questions/feature requests on in the issues section.

Full contributing guidelines are covered here.

License

Apache 2 Licensed. See LICENSE for full details.

Authors

Module is maintained by James Goodhand.

Requirements

Name	Version
terraform	>= 1.1.7
snowflake	>=0.54.0

Providers

Name	Version
snowflake	>=0.54.0

Modules

No modules.

Resources

Name	Type
snowflake_database.play	resource
snowflake_database_grant.play_usage	resource
snowflake_function.normalize_proc_names	resource
snowflake_object_parameter.log_table_data_retention	resource
snowflake_procedure.tidy_playground	resource
snowflake_procedure.update_objects	resource
snowflake_schema.administration	resource
snowflake_schema.ground	resource
snowflake_schema_grant.play_administration_grant_usage	resource
snowflake_schema_grant.play_ground_grant_external_table	resource
snowflake_schema_grant.play_ground_grant_file_format	resource
snowflake_schema_grant.play_ground_grant_function	resource
snowflake_schema_grant.play_ground_grant_masking_policy	resource
snowflake_schema_grant.play_ground_grant_materialized_view	resource
snowflake_schema_grant.play_ground_grant_pipe	resource
snowflake_schema_grant.play_ground_grant_procedure	resource
snowflake_schema_grant.play_ground_grant_row_policy	resource
snowflake_schema_grant.play_ground_grant_sequence	resource
snowflake_schema_grant.play_ground_grant_stage	resource
snowflake_schema_grant.play_ground_grant_stream	resource
snowflake_schema_grant.play_ground_grant_table	resource
snowflake_schema_grant.play_ground_grant_task	resource
snowflake_schema_grant.play_ground_grant_temporary_table	resource
snowflake_schema_grant.play_ground_grant_usage	resource
snowflake_schema_grant.play_ground_grant_view	resource
snowflake_table.log_table	resource
snowflake_table.streams	resource
snowflake_table.tasks	resource
snowflake_tag.expiry_date_tag	resource
snowflake_tag_grant.expiry_date_apply_grant	resource
snowflake_task.tidy	resource
snowflake_task.update_stream_objects	resource
snowflake_task.update_task_objects	resource
snowflake_view.log_summary	resource
snowflake_view.log_view	resource
snowflake_view.object_ages	resource
snowflake_view.object_tags	resource
snowflake_view_grant.select_object_ages_grant	resource
snowflake_view_grant.select_object_tags_grant	resource
snowflake_warehouse.playground_admin_warehouse	resource

Inputs

Name	Description	Type	Default	Required
data_retention_time	Snowflake Data Retention value for the Playground Schema.	number	1	no
dry_run	Whether the playground tidying procedure should alter tags / drop objects, or just log its planned actions.	bool	true	no
expiry_date_tag	Details regarding the location and name of the EXPIRY_DATE tag. If create = true then a new tag will be created, otherwise will assume a current tag exists with this name. If using an existing tag, you MUST grant 'APPLY' on this tag to the 'PUBLIC' role. This will not be done by the terraform module.	object({ database = string schema = string name = string create = bool })	{ "create": true, "database": "PLAY", "name": "EXPIRY_DATE", "schema": "ADMINISTRATION" }	no
max_expiry_days	Max number of days that an expiry date tag can be set in the future. If a tag is set beyond this value, then it will be reduced to the date defined by this number of days in the future.	number	90	no
max_object_age_without_tag	Max number of days to allow an object to exist in the playground without an expiry date before it is dropped.	number	31	no
playground	Database, schema and administration schema names to use for the Playground. Also provides control over whether the Playground schema should be transient.	object({ database = string schema = string is_transient = bool administration_schema = string })	{ "administration_schema": "ADMINISTRATION", "database": "PLAY", "is_transient": true, "schema": "GROUND" }	no
playground_warehouse	Warehouse to use for executing the playground automation functions.	object({ name = string size = string })	{ "name": "playground_admin_warehouse", "size": "xsmall" }	no
task_cron_schedule	CRON schedule on which the playground tidying tasks should be executed.	string	"USING CRON 0 3 * * * UTC"	no
tasks_enabled	Whether the playground tidying tasks are enabled or not.	bool	false	no

Outputs

No outputs.