Behavior per materialization

core/dbt/adapters/base/impl.py

@@ -1063,6 +1063,11 @@ class BaseAdapter(metaclass=AdapterMeta):
 
         return Compiler(self.config)
 
+    # used in apply_grants -- True is a safe default
+    @available
+    def do_i_carry_over_grants_when_an_object_is_replaced(self) -> bool:
+        return True
+
     # Methods used in adapter tests
     def update_column_sql(
         self,

core/dbt/context/base.py

@@ -657,6 +657,25 @@ class BaseContext(metaclass=ContextMeta):
             print(msg)
         return ""
 
+    @contextmember
+    @staticmethod
+    def diff_of_two_dicts(dict_a, dict_b):
+        """
+        Given two dictionaries:
+            dict_a: {'key_x': ['value_1', 'value_2'], 'key_y': ['value_3']}
+            dict_b: {'key_x': ['value_1'], 'key_z': ['value_4']}
+        Return the same dictionary representation of dict_a MINUS dict_b
+        """
+        dict_diff = {}
+        for k in dict_a:
+            if k in dict_b:
+                diff = list(set(dict_a[k]) - set(dict_b[k]))
+                if diff:
+                    dict_diff.update({k: diff})
+            else:
+                dict_diff.update({k: dict_a[k]})
+        return dict_diff
+
 
 def generate_base_context(cli_vars: Dict[str, Any]) -> Dict[str, Any]:
     ctx = BaseContext(cli_vars)

core/dbt/include/global_project/macros/adapters/apply_grants.sql

@@ -0,0 +1,62 @@
+{% macro get_show_grant_sql(relation) %}
+{{ return(adapter.dispatch("get_show_grant_sql", "dbt")(relation)) }}
+{% endmacro %}
+
+{% macro default__get_show_grant_sql(relation) %}
+show grants on {{ relation.type }} {{ relation }}
+{% endmacro %}
+
+{% macro get_grant_sql(relation, grant_dict) %}
+{{ return(adapter.dispatch('get_grant_sql', 'dbt')(relation, grant_dict)) }}
+{% endmacro %}
+
+{% macro default__get_grant_sql(relation, grant_dict) %}
+    {% for privilege in grant_dict.keys() %}
+        {% set grantees = grant_dict[privilege] %}
+        {% if grantees %}
+          grant {{ privilege }} on {{ relation.type }} {{ relation }} to {{ grantees | join(', ') }};
+        {% endif %}
+    {% endfor %}
+{% endmacro %}
+
+{% macro get_revoke_sql(relation, grant_dict) %}
+{{ return(adapter.dispatch("get_revoke_sql", "dbt")(relation, grant_dict)) }}
+{% endmacro %}
+
+{% macro default__get_revoke_sql(relation, grant_dict) %}
+    {% for privilege in grant_dict.keys() %}
+        {% set grantees = grant_dict[privilege] %}
+        {% if grantees %}
+          revoke {{ privilege }} on {{ relation.type }} {{ relation }} from {{ grantees | join(', ') }};
+        {% endif %}
+    {% endfor %}
+{% endmacro %}
+
+{% macro apply_grants(relation, grant_config, should_revoke) %}
+{{ return(adapter.dispatch("apply_grants", "dbt")(relation, grant_config, should_revoke)) }}
+{% endmacro %}
+
+{% macro default__apply_grants(relation, grant_config, should_revoke=True) %}
+    {% if grant_config %}
+        {% if should_revoke %}
+            {% set current_grants_table = run_query(get_show_grant_sql(relation)) %}
+            {% set current_grants_dict = adapter.standardize_grants_dict(current_grants_table) %}
+            {% set needs_granting = diff_of_two_dicts(grant_config, current_grants_dict) %}
+            {% set needs_revoking = diff_of_two_dicts(current_grants_dict, grant_config) %}
+            {% if not (needs_granting or needs_revoking) %}
+              {{ log("All grants are in place, no revocation or granting needed") }}
+            {% endif %}
+        {% else %}
+            {% set needs_revoking = {} %}
+            {% set needs_granting = grant_config %}
+        {% endif %}
+        {% if needs_granting or needs_revoking %}
+          {% call statement('grants') %}
+              {#-- TODO edge case: what if both of these return empty queries? --#}
+              {#-- e.g. grant_config is {'select': []} --#}
+              {{ get_revoke_sql(relation, needs_revoking) }}
+              {{ get_grant_sql(relation, needs_granting) }}
+          {% endcall %}
+        {% endif %}
+    {% endif %}
+{% endmacro %}

core/dbt/include/global_project/macros/materializations/models/incremental/incremental.sql

@@ -20,6 +20,8 @@
   -- BEGIN, in a separate transaction
   {%- set preexisting_intermediate_relation = load_cached_relation(intermediate_relation)-%}
   {%- set preexisting_backup_relation = load_cached_relation(backup_relation) -%}
+   -- grab current tables grants config for comparision later on
+  {% set  grant_config = config.get('grants') %}
   {{ drop_relation_if_exists(preexisting_intermediate_relation) }}
   {{ drop_relation_if_exists(preexisting_backup_relation) }}
 
@@ -59,6 +61,11 @@
       {% do to_drop.append(backup_relation) %}
   {% endif %}
 
+  {% set should_revoke = (not full_refresh_mode) or (
+    existing_relation and adapter.do_i_carry_over_grants_when_an_object_is_replaced()
+  ) %}
+  {% do apply_grants(target_relation, grant_config, should_revoke=should_revoke) %}
+
   {% do persist_docs(target_relation, model) %}
 
   {% if existing_relation is none or existing_relation.is_view or should_full_refresh() %}

core/dbt/include/global_project/macros/materializations/models/table/table.sql

@@ -14,6 +14,8 @@
   {%- set backup_relation = make_backup_relation(target_relation, backup_relation_type) -%}
   -- as above, the backup_relation should not already exist
   {%- set preexisting_backup_relation = load_cached_relation(backup_relation) -%}
+  -- grab current tables grants config for comparision later on
+  {% set  grant_config = config.get('grants') %}
 
   -- drop the temp relations if they exist already in the database
   {{ drop_relation_if_exists(preexisting_intermediate_relation) }}
@@ -40,6 +42,8 @@
 
   {{ run_hooks(post_hooks, inside_transaction=True) }}
 
+  {% set should_revoke = existing_relation and adapter.do_i_carry_over_grants_when_an_object_is_replaced() %}
+  {% do apply_grants(target_relation, grant_config, should_revoke=should_revoke) %}
   {% do persist_docs(target_relation, model) %}
 
   -- `COMMIT` happens here

core/dbt/include/global_project/macros/materializations/models/view/view.sql

@@ -25,6 +25,8 @@
   {%- set backup_relation = make_backup_relation(target_relation, backup_relation_type) -%}
   -- as above, the backup_relation should not already exist
   {%- set preexisting_backup_relation = load_cached_relation(backup_relation) -%}
+  -- grab current tables grants config for comparision later on
+  {% set  grant_config = config.get('grants') %}
 
   {{ run_hooks(pre_hooks, inside_transaction=False) }}
 
@@ -47,6 +49,9 @@
   {% endif %}
   {{ adapter.rename_relation(intermediate_relation, target_relation) }}
 
+  {% set should_revoke = existing_relation and adapter.do_i_carry_over_grants_when_an_object_is_replaced() %}
+  {% do apply_grants(target_relation, grant_config, should_revoke=should_revoke) %}
+
   {% do persist_docs(target_relation, model) %}
 
   {{ run_hooks(post_hooks, inside_transaction=True) }}

core/dbt/include/global_project/macros/materializations/seeds/seed.sql

@@ -8,7 +8,10 @@
   {%- set exists_as_table = (old_relation is not none and old_relation.is_table) -%}
   {%- set exists_as_view = (old_relation is not none and old_relation.is_view) -%}
 
+  {%- set  grant_config = config.get('grants') -%}
   {%- set agate_table = load_agate_table() -%}
+  -- grab current tables grants config for comparision later on
+
   {%- do store_result('agate_table', response='OK', agate_table=agate_table) -%}
 
   {{ run_hooks(pre_hooks, inside_transaction=False) }}
@@ -35,6 +38,10 @@
   {% endcall %}
 
   {% set target_relation = this.incorporate(type='table') %}
+  {% set should_revoke = (not full_refresh_mode) or (
+    old_relation is not none and adapter.do_i_carry_over_grants_when_an_object_is_replaced()
+  ) %}
+  {% do apply_grants(target_relation, grant_config, should_revoke=True) %}
   {% do persist_docs(target_relation, model) %}
 
   {% if full_refresh_mode or not exists_as_table %}

core/dbt/include/global_project/macros/materializations/snapshots/snapshot.sql

@@ -5,6 +5,8 @@
 
   {%- set strategy_name = config.get('strategy') -%}
   {%- set unique_key = config.get('unique_key') %}
+  -- grab current tables grants config for comparision later on
+  {%- set  grant_config = config.get('grants') -%}
 
   {% set target_relation_exists, target_relation = get_or_create_relation(
           database=model.database,
@@ -73,6 +75,8 @@
       {{ final_sql }}
   {% endcall %}
 
+  {% set should_revoke = target_relation_exists %}
+  {% do apply_grants(target_relation, grant_config, should_revoke=should_revoke) %}
   {% do persist_docs(target_relation, model) %}
 
   {% if not target_relation_exists %}

plugins/postgres/dbt/adapters/postgres/impl.py

@@ -10,6 +10,7 @@ from dbt.adapters.postgres import PostgresRelation
 from dbt.dataclass_schema import dbtClassMixin, ValidationError
 import dbt.exceptions
 import dbt.utils
+import agate
 
 
 # note that this isn't an adapter macro, so just a single underscore
@@ -85,6 +86,25 @@ class PostgresAdapter(SQLAdapter):
     def parse_index(self, raw_index: Any) -> Optional[PostgresIndexConfig]:
         return PostgresIndexConfig.parse(raw_index)
 
+
+    @available
+    def standardize_grants_dict(self, grants_table: agate.Table) -> dict:
+        grants_dict = {}
+        for row in grants_table:
+            grantee = row['grantee'].lower()
+            privilege = row['privilege_type'].lower()
+            if privilege in grants_dict.keys():
+                grants_dict[privilege].append(grantee)
+            else:
+                grants_dict.update({privilege: [grantee]})
+        return grants_dict
+
+
+    @available
+    def do_i_carry_over_grants_when_an_object_is_replaced(self) -> bool:
+        return False
+
+
     def _link_cached_database_relations(self, schemas: Set[str]):
         """
         :param schemas: The set of schemas that should have links added.

plugins/postgres/dbt/include/postgres/macros/adapters.sql

@@ -202,3 +202,13 @@
     comment on column {{ relation }}.{{ adapter.quote(column_name) if column_dict[column_name]['quote'] else column_name }} is {{ escaped_comment }};
   {% endfor %}
 {% endmacro %}
+
+{% macro postgres__get_show_grant_sql(relation) %}
+  select grantee, privilege_type
+  from information_schema.role_table_grants
+      {{ log(grantee, info=True) }}
+      where grantor = current_role
+        and grantee != current_role
+        and table_schema = '{{ relation.schema }}'
+        and table_name = '{{ relation.identifier }}'
+{% endmacro %}