A Guide to Snowflake Permissions As Code With Permifrost

Manage Snowflake Permissions as Code with Permifrost for Superior Security and Maintainability.

Welcome to Tech Station, SDG Group’s hub for uncovering the latest innovations in data and analytics! In this article, we explore a powerful combination: Permifrost and Snowflake. We will demonstrate how to use Permifrost, an open-source tool, to manage Snowflake permissions declaratively through YAML files. This "Permissions as Code" approach not only automates governance but also eliminates unwanted grants, strengthens security, and makes the entire data environment easier to maintain and audit.

Looking for something else? Check all of our content here.

In the ever-evolving world of data engineering, maintaining data governance, security, and efficiency is paramount.

As data teams grow and the volume of data increases, it becomes increasingly challenging to manage permissions and maintain a clean, secure data environment.

Often, companies migrating to platforms like Snowflake face issues such as:

• Inconsistent Permissions: Different teams with varying levels of access often lead to overly permissive grants.

• Manual Management: Manually assigning permissions is slow and prone to human error.

• Audit Difficulties: Tracking and auditing permissions across the environment becomes a complex task, increasing security risks.

This article explores a powerful solution to these challenges by combining Permifrost and Snowflake to enhance maintainability and security.

The Tools of the Solution

• Permifrost: An open-source Python package, maintained by the GitLab Data Team, for managing permissions in a Snowflake data warehouse. It simplifies and accelerates privilege setup using a YAML specification file. Permifrost compares this file to the current state of the Snowflake environment and generates (or executes) the necessary queries to align permissions with the declaration.
  
  
• Snowflake: A cloud-based data warehouse solution widely adopted for its scalability, performance, and ease of use.
  
  
• dbt (Recommended): To complete the ecosystem, we suggest using dbt as a data transformation tool, which integrates seamlessly into this workflow.

The Solution: "Permissions as Code"

Integrating Permifrost with Snowflake transforms permission management into a version-controlled, automated process.

1. Define Permissions as Code: With Permifrost, permissions are defined in a declarative YAML file.
   
   
2. Version Control with Git: All permission definitions are stored in a Git repository. Every change is tracked, reviewed, and approved before being applied, ensuring a robust audit trail.
   
   
3. Automate Permission Management: A CI/CD pipeline is set up to automatically apply permissions using Permifrost. Whenever a change is merged into the main branch, Permifrost runs and updates the permissions in Snowflake.
   
   
4. Remove Unwanted Grants: Permifrost’s declarative approach ensures that any permissions not defined in the YAML file are removed from Snowflake. This is crucial for preventing "privilege creep."
   
   
5. Integrate with dbt: If using dbt, you can ensure that only the necessary permissions are granted for each transformation job, adhering to the principle of least privilege.

This article will provide a practical guide focusing on implementing points 1 and 4 of the described solution.

Step 1: Configure Your Snowflake Environment

First, let's create the necessary objects in Snowflake.

Create Database and Schema:

Create Roles and Users:

Define the roles and users that will require specific permissions.

Step 2: Set Up and Configure Permifrost

Initialize Permifrost Repository: Create a new Git repository to manage your Snowflake permissions.

Define Permissions in YAML:

Create a permifrost.yml file in the repository.

The error you will get if you try to run Permifrost with another role

Apply Permissions: We suggest running the command with the --dry option first to check the configuration and preview the queries.

The output of the first dry run will look similar to the following, showing all the GRANT commands to be executed:

Verifying the Removal of Unwanted Grants

To appreciate the change, you can run the following commands in Snowflake before and after applying Permifrost:

Now, to demonstrate how Permifrost handles removing permissions, let's modify the permifrost.yml file to revoke write privileges from the engineer_role:

Running the dry run again will now show the REVOKE commands needed to align the environment, demonstrating that Permifrost has identified and is preparing to remove the excess privileges.