Permissioning & Relations with Redshift

Archive
, ,
1

Anyone have any resources on best practices for managing permissions in redshift with DBT. In particular I’m thinking through the right way to structure VIEWs if I don’t want analysts to have access to the underlying table. Do we just materialize them anyway? Do we use ephemeral relations?

2

Generally, all users are in groups, and groups are GRANTed permissions using post-hooks in DBT. You can easily write a macro to cycle through those grants (see below for an example).

Specific to your question, allowing someone to SELECT from a view does not allow them to SELECT the underlying table(s) directly. They will need USAGE permissions on the tables, but not SELECT.

{% macro grant_usage_on_all_schemas_to(user_list) %}
  {# CALL THIS MACRO WITH THE FOLLOWING SYNTAX:
    on-run-end:
        - '{{ grant_usage_on_all_schemas_to(["group readers", "group transformers"]) }}'
  #}
  {%- call statement('get_all_schemas', fetch_result=True, auto_begin=False) -%}

    select distinct
      nspname as schema_name
    from pg_namespace
    where
      schema_name not like 'pg_%'
      and schema_name not like 'dbt_%'

  {%- endcall -%}

  {% set result = load_result('get_all_schemas') %}
  {% set schema_list = result['data'] | map(attribute=0) | list %}

  {% for u in user_list %}
    {% for s in schema_list %}
      grant usage on schema {{ s }} to {{ u }} ;
    {% endfor %}
  {% endfor %}

{% endmacro %}

{% macro grant_select_on_all_tables_in(schema_list, user_list) %}
  {# CALL THIS MACRO WITH THE FOLLOWING SYNTAX:
    on-run-end:
        - '{{ grant_select_on_all_tables_in(["analytics"],["group readers","group transformers"]) }}'
  #}
  {% for u in user_list %}
    {% for s in schema_list %}
      grant select on all tables in schema {{ s }} to {{ u }} ;
    {% endfor %}
  {% endfor %}

{% endmacro %}

{% macro grant_execute_on_all_functions_in(schema_list, user_list) %}
  {# CALL THIS MACRO WITH THE FOLLOWING SYNTAX:
    on-run-end:
        - '{{ grant_execute_on_all_functions_in(["analytics"],["group readers","group transformers"]) }}'
  #}
  {% for u in user_list %}
    {% for s in schema_list %}
      grant execute on all functions in schema {{ s }} to {{ u }} ;
    {% endfor %}
  {% endfor %}

{% endmacro %}
4 Likes
3

The “usage without select” is exactly the trick I was looking for. Thank you!

Also thanks for this macro – super helpful!