version: "1.0"

databases:
  # System Databases
  - snowflake:
      shared: yes
  - snowflake_sample_data:
      shared: yes
  - permifrost:
      shared: no

  # Production Databases (at least one raw and one analytics)
  - prod_raw:
      shared: no
  - prod_analytics:
      shared: no

  # Development Databases (one per developing user)
  - dev_<first_initial><last_name>_raw:  # e.g. dev_jdoe_raw
      shared: no
  - dev_<first_initial><last_name>_analytics:  # e.g. dev_jdoe_analytics
      shared: no

  # In case the client uses dbt Cloud
  - dev_cloud:
      shared: no

warehouses:
  # The name "warehouse" is misleading. These are compute units. Think of them as
  # virtual machines or servers that are spun up and down at will.
  # Billing can best be analyzed split by warehouse, hence a certain granularity is useful.
  - load:
      size: x-small
      meta:
        warehouse_size: x-small  # Add size as meta tag as well
        warehouse_type: standard
        auto_suspend: 60
        auto_resume: true
        initially_suspended: true
        statement_timeout_in_seconds: 1200
  - transform:
      size: x-small
      meta:
        warehouse_size: x-small
        warehouse_type: standard
        auto_suspend: 60
        auto_resume: true
        initially_suspended: true
        statement_timeout_in_seconds: 1200
  - report:
      size: x-small
      meta:
        warehouse_size: x-small
        warehouse_type: standard
        auto_suspend: 60
        auto_resume: true
        initially_suspended: true
        statement_timeout_in_seconds: 1200
  - develop:
      size: x-small
      meta:
        warehouse_size: x-small
        warehouse_type: standard
        auto_suspend: 60
        auto_resume: true
        initially_suspended: true
        statement_timeout_in_seconds: 1200
  - admin:
      size: x-small
      meta:
        warehouse_size: x-small
        warehouse_type: standard
        auto_suspend: 60
        auto_resume: true
        initially_suspended: true
        statement_timeout_in_seconds: 1200

roles:
  # System Roles Def
  - public: {}
  - orgadmin:
      warehouses:
        - admin
  - accountadmin:
      member_of:
        - securityadmin
        - sysadmin
      warehouses:
        - admin
  - securityadmin:
      member_of:
        - useradmin
      warehouses:
        - admin
  - sysadmin:
      warehouses:
        - admin
  - useradmin:
      warehouses:
        - admin

  - ops_role_owner:
      # Should be member of all ops roles directly or indirectly
      # Ops roles are all roles that are not system roles (above) or user roles
      # (each user is only assigned one role - their user role - and any other roles are added to that user role)
      member_of:  ### TBU ###
        - loader
        - transformer
        - reporter
        - developer
        - analyst
  - userrole_role_owner:
      # Should be member of all human roles
      member_of:   ### TBU ###
        # Except for user roles who are member of high-level system roles (otherwise it fails with "would create a cycle")
        - userrole_<first_initial><last_name> # e.g. userrole_jdoe

  # Operations Roles
  # When adding a role: add the role to member_of of ops_role_owner
  - loader:
      # Used by production data loading processes
      warehouses:
        - load
      owns:
        databases:
          - prod_raw
        schemas:
          - prod_raw.*
        tables:
          - prod_raw.*.*

  - transformer:
      # Used by dbt for production runs
      warehouses:
        - transform
      owns:
        databases:
          # Database for production dbt runs
          - prod_analytics
        schemas:
          - prod_analytics.*
        tables:
          - prod_analytics.*.*
      privileges:
        databases:
          read:
            - prod_raw
            - snowflake  # required for dbt_snowflake_monitoring package
        schemas:
          read:
            - prod_raw.*
            - snowflake.*
        tables:
          read:
            - prod_raw.*.*
            - snowflake.*.*

  - reporter:
      # Used by BI Tool to read data
      warehouses:
        - report
      privileges:
        databases:
          read:
            - prod_analytics
        schemas:
          read:
            - prod_analytics.reporting
        tables:
          read:
            - prod_analytics.reporting.*

  - developer:
      # Base role with privileges for all developers - user roles should be members
      warehouses:
        - develop
      privileges:
        databases:
          read:
            - prod_raw
            - prod_analytics
        schemas:
          read:
            - prod_raw.*
            - prod_analytics.*
        tables:
          read:
            - prod_raw.*.*
            - prod_analytics.*.*

  - analyst:
      # Base role with privileges for all analysts - user roles should be members
      warehouses:
        - report
      privileges:
        databases:
          read:
            - prod_analytics
        schemas:
          read:
            # Only allow read access to reporting Schema
            - prod_analytics.reporting
        tables:
          read:
            - prod_analytics.reporting.*

  ## User roles
  # When adding a role for a new user: add the role to member_of of userrole_role_owner
  # Do not add accountadmin or orgadmin roles here, these need to be added
  # directly to the user. Add at least one user.

  - userrole_<first_initial><last_name>:  # e.g. userrole_jdoe
      member_of:
        - developer
        - loader
        - analyst
        - transformer
      owns:
        databases:
          - dev_<first_initial><last_name>_raw  # e.g. dev_jdoe_raw
          - dev_<first_initial><last_name>_analytics  # e.g. dev_jdoe_analytics
        schemas:
          - dev_<first_initial><last_name>_raw.*
          - dev_<first_initial><last_name>_analytics.*
        tables:
          - dev_<first_initial><last_name>_raw.*.*
          - dev_<first_initial><last_name>_analytics.*.*

# Further users might only need to be member_of developer and analyst but own the same
# kinds of databases, schemas and tables

# In case dbt Cloud is used
  - userrole_dev_cloud:
      member_of:
        - developer
      owns:
        databases:
          - dev_cloud
        schemas:
          - dev_cloud.*
        tables:
          - dev_cloud.*.*

users:
  # System users
  - snowflake:  # Add this user to avoid Permifrost dropping it
      can_login: yes
      meta:
        default_role: sysadmin
        password: 1passwordvault
        must_change_password: true

  - permifrost:
      can_login: yes
      member_of:
        - securityadmin
        - sysadmin
        # Required to be able to create and destroy objects
        - ops_role_owner
        - userrole_role_owner
      meta:
        default_role: securityadmin
        type: service
        rsa_public_key: |
          -----BEGIN PUBLIC KEY-----
          MIIBIjANBgkqh...   # replace here with the full contents of the public key
          -----END PUBLIC KEY-----

  # Production processes users
  # Below are examples, delete unused users for you project
  # Generate a new key-pair for each user
  - airbyte:
      can_login: yes
      member_of:
        - loader
      meta:
        default_role: loader
        type: service
        rsa_public_key: |
          -----BEGIN PUBLIC KEY-----
          MIIBIjANBgkqh...   # replace here with the full contents of the public key
          -----END PUBLIC KEY-----

  - airflow:
      can_login: yes
      member_of:
        - loader
      meta:
        default_role: loader
        type: service
        rsa_public_key: |
          -----BEGIN PUBLIC KEY-----
          MIIBIjANBgkqh...   # replace here with the full contents of the public key
          -----END PUBLIC KEY-----

  - metabase:
      can_login: yes
      member_of:
        - reporter
      meta:
        default_role: reporter
        type: service
        rsa_public_key: |
          -----BEGIN PUBLIC KEY-----
          MIIBIjANBgkqh...   # replace here with the full contents of the public key
          -----END PUBLIC KEY-----

  # Dbt cloud prod user
  - dbt_cloud:  # for production runs
      can_login: yes
      member_of:
        - transformer
      meta:
        default_role: transformer
        type: service
        rsa_public_key: |
          -----BEGIN PUBLIC KEY-----
          MIIBIjANBgkqh...   # replace here with the full contents of the public key
          -----END PUBLIC KEY-----

  # Dbt cloud dev user
  - dbt_cloud_dev:  # for dev runs
      can_login: yes
      member_of:
        - userrole_dev_cloud
        - transformer
        - developer
      meta:
        default_role: dev_cloud
        type: service
        rsa_public_key: |
          -----BEGIN PUBLIC KEY-----
          MIIBIjANBgkqh...   # replace here with the full contents of the public key
          -----END PUBLIC KEY-----

  # Human users
  - admin:  # overall admin user for the client
      can_login: yes
      member_of:
        - sysadmin
        - securityadmin
        - accountadmin
        - useradmin
        - orgadmin
      meta:
        default_role: accountadmin
        password: 1passwordvault
        must_change_password: true
        type: person
# Add at least one human user below
  - <first_initial><last_name>:  # e.g. jdoe
      can_login: yes
      member_of:
        # All roles are assigned to the userrole...
        - userrole_<first_initial><last_name>  # e.g. userrole_jdoe
        # ... except accountadmin and orgadmin roles, which are assigned directly
        # Note: do not assign these roles to all human users. Only for those that need direct access to billing and user management
        # - accountadmin
        # - orgadmin
      meta:
        default_role: userrole_<first_initial><last_name>  # e.g. userrole_jdoe
        default_warehouse: develop
        password: 1passwordvault
        must_change_password: true
        type: person

# Further users might only need to be member_of userrole_<first_initial><last_name>