Through the years, we have learned from implementations at various customers that setting up and maintaining a Snowflake environment can be time consuming and cumbersome. Especially in a fast growing environment with a lot of users, making sure every user has the correct privileges can quickly become very complex. Typically, this results in a lot of extensive SQL scripts, that are very difficult to maintain and are far from surveyable. Therefore, we searched for ways to make the management of Snowflake environments more efficient. Luckily, a small tool exists that make life so much easier!
Permifrost is an open source tool for managing permissions on a Snowflake data warehouse, maintained by the GitLab Data Team. It simplifies and accelerates the set-up of user privileges in a Snowflake environment by means of a specification file in YAML format.
In the specification file the following information can be defined:
- Privileges on database, schema and table level
- Privileges on other objects (e.g. virtual warehouses)
- User roles
- Ownership of objects (databases, schemas and tables)
- Usage of wildcards (e.g. table_*)
After connecting Permifrost with your Snowflake environment, it will process the specification file and compare the content with the current state of the Snowflake environment. Afterwards, it generates and optionally executes the necessary queries to adapt the Snowflake environment to what’s specified in the YAML file. Simple, but effective!
At DataSense, we are very excited about this tool! Therefore, we have searched for ways to use it as efficiently as possible. Because everything is defined in a YAML file, we can actually combine Permifrost with some scripting to generate the specification files. In fact, we came up with a way that simplifies the usage of Permifrost even further, in combination with a growing Snowflake environment. Based on some tables, that correspond with the different specification options in the YAML file, you can define everything that is needed. On top of that, templating is supported.
Let’s suppose source systems are being unclosed iteratively and for each source there is a schema with a name like <SOURCE_NAME>_LANDING. In the TEMPLATES table, we define a source level template with _LANDING as suffix. In the TABLE_NAME field, we indicate whether the template applies for all tables in the schema or for a specific table. In the ROLE_PRIVILEGES table, a role (e.g. DWH_DEV) can be mapped to a template with the desired privileges. This means that, for each source available in the SOURCES table, the privileges will be granted on the schema. In fact, all of the privileges of a new source system are managed by a single insert on the SOURCES table. A Python script processes the information in these tables and generates a YAML file, to be used by Permifrost.
As you can see, the combination of Permifrost and scripting can save a lot of time. Actually, at some of our customers, we have integrated Permifrost in their DataOps release pipelines. After deploying a new release to the Snowflake environment, Permifrost is executed to manage all of the privileges on the newly created objects. As a result, managing your Snowflake environment becomes a background process!
