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.
- Define Permissions as Code: With Permifrost, permissions are defined in a declarative YAML file.
- 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.
- 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.
- 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."
- 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.
A Practical Guide: Setting Up Permifrost with Snowflake
Let's see how to do it. Here are the prerequisites:
-
Access to a Snowflake account with the SECURITYADMIN role.
-
Permifrost installed (pip install permifrost).
-
Git installed and a GitHub (or other Git repository) account.
Step 1: Configure Your Snowflake Environment
First, let's create the necessary objects in Snowflake.
|
Create a Database and a Scheme:
-- example:
USE ROLE securityadmin;
CREATE DATABASE refactoring;
USE DATABASE refactoring;
CREATE SCHEMA sbellesi;
Create Roles and Users:
Define the roles and users that will require permissions.
--example:
-- Create Role
CREATE ROLE analyst_role;
CREATE ROLE engineer_role;
-- Create Users
CREATE USER analyst_user PASSWORD='StrongPassword' DEFAULT_ROLE = analyst_role;
CREATE USER engineer_user PASSWORD='StrongPassword' DEFAULT_ROLE = engineer_role;
-- Assign Role to Users
GRANT ROLE analyst_role TO USER analyst_user;
GRANT ROLE engineer_role TO USER engineer_user;
|
Step 2: Set Up and Configure Permifrost
|
Initialize Permifrost Repository: Create a new Git repository to manage your Snowflake permissions.
mkdir permifrost_repo
cd permifrost_repo
git init
Define Permissions in YAML:
Create a permifrost.yml file in the repository.
# example
# Databases
databases:
- refactoring:
shared: false # put false if your db isn't a shared object!
# Roles
roles:
- analyst_role:
warehouses:
- compute_wh
privileges:
databases:
read:
- refactoring
schemas:
read:
- refactoring.*
tables:
read:
- refactoring.sbellesi.*
- engineer_role:
warehouses:
- compute_wh
privileges:
databases:
read:
- refactoring
write:
- refactoring
schemas:
read:
- refactoring.*
write:
- refactoring.*
tables:
read:
- refactoring.sbellesi.*
write:
- refactoring.sbellesi.*
# Users
# can_login is required the rest of the parameters are optional
# None of the values are validated in Snowflake, hence default_warehouse, default_namespace and default_role
# can contain invalid values
users:
- analyst_user:
can_login: true
member_of:
- analyst_role
has_password: true
- engineer_user:
can_login: true
member_of:
- engineer_role
has_password: true
# Warehouses
# Warehouse sizes are informative and not altered by Permifrost to align with the spec file
warehouses:
- compute_wh:
size: x-small
meta:
some_key: some_value:
can_login: true
member_of:
- engineer_role
has_password: true
# Warehouses
# Warehouse sizes are informative and not altered by Permifrost to align with the spec file
warehouses:
- compute_wh:
size: x-small
meta:
some_key: some_value
|
Connecting Permifrost to Snowflake: We will use the connection via Username and Password by setting environment variables.
PERMISSION_BOT_USER='SNOWFLAKE_USER_NAME'
PERMISSION_BOT_ACCOUNT='SNOWFLAKE_ACCOUNT'
PERMISSION_BOT_WAREHOUSE='SNOWFLAKE_WAREHOUSE'
PERMISSION_BOT_PASSWORD='SNOWFLAKE_PASSWORD'
PERMISSION_BOT_DATABASE='SNOWFLAKE_DATABASE'
PERMISSION_BOT_ROLE='SECURITYADMIN' |
Note: Permifrost expects to run as SECURITYADMIN. If you attempt to run it with a different role, you will encounter an error indicating insufficient privileges.
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.
--general command
permifrost [-v] run <spec_file> [--role] [--dry] [--diff] [--user] [--ignore-memberships]
1) permifrost run --dry permifrost.yml
2) permifrost run permifrost.yml
The output of the first dry run will look similar to the following, showing all the GRANT commands to be executed:
Confirming spec loads successfully
Loading spec file
Checking spec file for errors
Checking permissions on current snowflake connection
Current user is: sarabellesi.
Current role is: securityadmin.
Checking that all entities in the spec file are defined in Snowflake
Fetching granted privileges from Snowflake
[####################################] 100%
Snowflake specs successfully loaded
Generating permission Queries:
Processing role analyst_role
Processing role engineer_role
Processing user analyst_user
Processing user engineer_user
SQL Commands generated for given spec file:
[PENDING] GRANT usage ON warehouse compute_wh TO ROLE analyst_role;
[PENDING] GRANT operate ON warehouse compute_wh TO ROLE analyst_role;
[PENDING] GRANT monitor ON warehouse compute_wh TO ROLE analyst_role;
[PENDING] GRANT usage ON database refactoring TO ROLE analyst_role;
[PENDING] GRANT usage ON FUTURE schemas IN database refactoring TO ROLE analyst_role;
[PENDING] GRANT usage ON schema refactoring.public TO ROLE analyst_role;
[PENDING] GRANT usage ON schema refactoring.sbellesi TO ROLE analyst_role;
[PENDING] GRANT select ON FUTURE tables IN schema refactoring.sbellesi TO ROLE analyst_role;
[PENDING] GRANT select ON ALL tables IN schema refactoring.sbellesi TO ROLE analyst_role;
[PENDING] GRANT select ON FUTURE views IN schema refactoring.sbellesi TO ROLE analyst_role;
[PENDING] GRANT select ON ALL views IN schema refactoring.sbellesi TO ROLE analyst_role;
[PENDING] GRANT usage ON warehouse compute_wh TO ROLE engineer_role;
[PENDING] GRANT operate ON warehouse compute_wh TO ROLE engineer_role;
[PENDING] GRANT monitor ON warehouse compute_wh TO ROLE engineer_role;
[PENDING] GRANT usage ON database refactoring TO ROLE engineer_role;
[PENDING] GRANT usage, monitor, create schema ON database refactoring TO ROLE engineer_role;
[PENDING] GRANT usage ON FUTURE schemas IN database refactoring TO ROLE engineer_role;
[PENDING] GRANT usage ON schema refactoring.public TO ROLE engineer_role;
[PENDING] GRANT usage ON schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT usage, monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON FUTURE schemas IN database refactoring TO ROLE engineer_role;
[PENDING] GRANT usage, monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON schema refactoring.public TO ROLE engineer_role;
[PENDING] GRANT usage, monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT select ON FUTURE tables IN schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT select ON ALL tables IN schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT select ON FUTURE views IN schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT select ON ALL views IN schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT select, insert, update, delete, truncate, references ON FUTURE tables IN schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT select, insert, update, delete, truncate, references ON ALL tables IN schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT select ON FUTURE views IN schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] GRANT select ON ALL views IN schema refactoring.sbellesi TO ROLE engineer_role;
[PENDING] ALTER USER analyst_user SET DISABLED = FALSE;
[PENDING] GRANT ROLE analyst_role TO user analyst_user;
[PENDING] ALTER USER engineer_user SET DISABLED = FALSE;
[PENDING] GRANT ROLE engineer_role TO user engineer_user;
|
Verifying the Removal of Unwanted Grants
To appreciate the change, you can run the following commands in Snowflake before and after applying Permifrost:
|
show grants to role engineer_role;
show grants to role analytics_role;
Now, to demonstrate how Permifrost handles removing permissions, let's modify the permifrost.yml file to revoke write privileges from the engineer_role:
# permifrost.yml (versione modificata)
# Databases
databases:
- refactoring:
shared: false
# Roles
roles:
- analyst_role:
warehouses:
- compute_wh
privileges:
databases:
read:
- refactoring
schemas:
read:
- refactoring.*
tables:
read:
- refactoring.sbellesi.*
- engineer_role:
warehouses:
- compute_wh
privileges:
databases:
read:
- refactoring
schemas:
read:
- refactoring.*
tables:
read:
- refactoring.sbellesi.*
# Users
users:
- analyst_user:
can_login: true
member_of:
- analyst_role
has_password: true
- engineer_user:
can_login: true
member_of:
- engineer_role
has_password: true
# Warehouses
warehouses:
- compute_wh:
size: x-small
meta:
some_key: some_value
|
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.
Confirming spec loads successfully
Loading spec file
Checking spec file for errors
...
SQL Commands generated for given spec file:
[PENDING] REVOKE monitor, create schema ON database refactoring FROM ROLE engineer_role;
[PENDING] REVOKE monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON FUTURE schemas IN database refactoring FROM ROLE engineer_role;
[PENDING] REVOKE monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON schema refactoring.public FROM ROLE engineer_role;
[PENDING] REVOKE monitor, create table, create view, create stage, create file format, create sequence, create function, create pipe ON schema refactoring.sbellesi FROM ROLE engineer_role;
[PENDING] REVOKE insert, update, delete, truncate, references ON FUTURE tables IN schema refactoring.sbellesi FROM ROLE engineer_role;
... |
Conclusion: Automated Security and Maintainability
By following this tutorial, you have successfully integrated Permifrost and Snowflake to manage permissions as code, ensuring a secure and maintainable data environment.
This setup simplifies permission management and enhances security by removing unwanted grants and ensuring consistent access controls.
For data teams facing similar challenges, adopting this approach can significantly improve their data governance and security posture, paving the way for more efficient and secure data operations.
Want to simplify permission management and strengthen the security of your Snowflake environment? Contact us for a personalized consultation and discover how the "Permissions as Code" approach with Permifrost can automate your data governance, ensuring consistent, auditable, and error-proof access control.
.png?width=2000&name=SDG%20-%20Logo%20White%20(1).png)
