Posthook grant schema usage

[nstein-gpjoule]

Hi,

I'm new to dbt, and just saw that DBT is not applying grants to the schema the models resides in.

I now had the idea to put a post_hook config in every model.

{{ config(
    materialized='view',
    grants = {
    '+select': ['ROLE_A', 'ROLE_B'] }
    post_hook = '{% for role in something %}GRANT USAGE ON {{ target.schema }} TO {{ role }}{% endfor %}'
) }}

Would this be possible? Is there a way to collect all roles for the current target? Since grants is a dictionary how can I concatenate all roles (and make them unique)?

[nstein-gpjoule]

I accomplished this using a global post-hook and these 3 Macros:

models:
  ...
  +post-hook: "{{ generate_schema_grants(this) }}"

seeds:
  ...
  +post-hook: "{{ generate_schema_grants(this) }}"

Get the Model config:

{% macro get_model_config(model_ref) %}
{% if execute %}
    {%- set model_name = model_ref.identifier -%}
    {% for node in graph.nodes.values() %}
        {%- set curr_model_name = node.unique_id.split('.')[-1] -%}
        {%- if model_name == curr_model_name -%}
            {%- set model_config = node.config -%}
            {{ return(model_config) }}
        {%- endif -%}
    {% endfor %}
{% endif %}
{% endmacro %}

Extract the roles:

{% macro get_model_grant_roles(model_ref) %}
{% if execute %}
    {%- set model_config = get_model_config(model_ref) -%}
    {%- set roles = [] -%}
    {% for model_grant, model_roles in model_config.grants.items() %}
        {%- set roles = roles.extend(model_roles) -%}
    {% endfor %}
    {{ return(roles | unique | list) }}
{% endif %}
{% endmacro %}

Create the sql string:

{% macro generate_schema_grants(model_ref) %}
{% if execute %}
    {%- set roles = get_model_grant_roles(model_ref) -%}
    {% if roles | length %}
      {%- set sql_strings = [] -%}
      {% for curr_role in roles %}
          {{ sql_strings.append("GRANT USAGE ON SCHEMA " + model_ref.database + '.' + model_ref.schema + " TO " + curr_role + ';') }}
          {{ log("Granting usage on Schema: " ~ model_ref.database + '.' + model_ref.schema ~ " to Role: " ~ curr_role, info = true) }}
      {% endfor %}
      {{ return(' '.join(sql_strings)) }}
    {% endif %}
{% endif %}
{% endmacro %}