# Snowflake Management Configuration Guide

Welcome to the Snowflake Management Configuration Guide. This document will help you understand how to set up and adjust your `permifrost.yml` file for managing roles, warehouses, databases, and users in Snowflake using Permifrost.

## What is Permifrost?

[Permifrost](https://pypi.org/project/permifrost/) is a tool designed to manage Snowflake permissions in a declarative way. Permifrost simplifies and accelerates the set-up of user privileges in a Snowflake environment using a specification file in YAML format. We use a fork of the original project maintained by Gemma Analytics, which includes additional features and improvements.

## Features & Prerequisites

Snowflake access management policies are handled by Permifrost, and the required `DROP`, `CREATE`, and `ALTER` statements are performed by the custom Python package [`tundri`](https://github.com/Gemma-Analytics/tundri).

_Note: Before editing - you need to understand how Snowflake access management works. You can find an overview [here](https://docs.snowflake.com/en/user-guide/security-access-control-overview)_

## Permifrost YAML Format Example

```yaml
databases:
  - db_name:
      shared: boolean
roles:
  - role_name:
      warehouses:
        - ...
      member_of:
        - ...
      privileges:
        databases:
          read:
            - ...
          write:
            - ...
        schemas:
        tables:
      owns:
        databases:
        schemas:
        tables:
users:
  - user_name:
      can_login: boolean
      member_of:
        - ...
warehouses:
  - warehouse_name:
      size: x-small
```

The main sections in this file are: `databases`, `roles`, `users`, and `warehouses`.

## Specification Details

In the `permifrost.yml` specification file, the following information can be defined:

- Privileges on database, schema, and table levels
- Privileges on other objects (e.g. virtual warehouses)
- User roles
- Ownership of objects (databases, schemas, and tables)

## Use Case: Role Permissions

The `permifrost.yml` specification file specifies permissions for databases, tables, and schemas using simple "read" and "write" permissions. Thus, it abstracts away the particular "grant" mechanics for the different database objects. For example, database/schema/table permissions for a specific role are specified as:

```yaml
roles:
  - role_name:
      warehouses:
        - ...
      member_of:
        - ...
      privileges:
        databases:
          read:
            - db_1
            - db_2
            - ...
          write:
            - ...
        schemas:
          read:
            - db_1.*
            - db_2.schema_name
            - db_3.schema_partial_*
            - ...
          write:
            - ...
        tables:
          read:
            - db_1.*.*
            - db_2.schema_name.*
            - db_2.schema_partial_*.*
            - db_3.schema_name.table_name
            - ...
          write:
            - ...
    owns:
      databases:
        - ...
      schemas:
        - ...
      tables:
        - ...
```

### Key Features
- **Wildcard Characters:** Permifrost supports the use of wildcard characters for easier configuration.
- **Future Grants:** Roles are set up with future grants, which is particularly useful for environments where schemas are frequently dropped and recreated.

In the YAML specification above, the asterisk "*" is a wildcard character that can be used to grant access to:

- all current and future tables/schemas in a database (e.g., "db_1.*.*") or all current and future tables in a specific schema (e.g., "db_2.schema_name.*");
- all current and future tables in schemas matching a pattern (e.g., "db_2.schema_partial_*.*").

The advantage of this YAML specification over a SQL script is that it improves readability and is easier to maintain.

## Pull Requests and Dry Runs

When the `permifrost.yml` is edited, for example to add a new user to Snowflake - a dry run is automatically triggered in Github Actions. This simply outputs the necessary SQL commands without directly executing them on the Snowflake database.

After merging the Pull Request, Permifrost will check the Snowflake configuration against this specification file. It then executes the necessary SQL commands to rectify any detected deviations. Afterward, it generates and optionally executes the necessary queries to adapt the Snowflake environment to what's specified in the YAML file.

### Adding Databases:
- Add a new entry under `databases`.
- Specify the `shared` property (`yes` or `no`).

### Adding Warehouses:
- Add a new entry under `warehouses`.
- Define the `size` and `meta` properties as needed.

### Defining Roles:
- Add a new entry under `roles`.
- Specify the `member_of`, `warehouses`, `owns`, and `privileges` properties as needed.

### Managing Users:
- Add a new entry under `users`.
- Define the `can_login`, `member_of`, and `meta` properties, including `default_role`, `default_warehouse`, and `password`.

### Important Considerations
- **Explicit Definitions:** Everything needs to be specified in the `permifrost.yml`. If an object is not specified, it will be dropped.

By following this guide, you can effectively manage your Snowflake permissions using Permifrost. Ensure that all configurations are explicitly defined to maintain a consistent and secure Snowflake environment.