Artifact type: blog_post
TAP E2E Verify — Snowflake RBAC Automation Pipeline
This post explores how to automate role-based access control in Snowflake using Python and the Snowflake Python connector. We opted for a declarative approach over imperative scripts due to its ease of auditing and reviewing.
Architecture Overview
We selected a layered architecture rather than a monolithic script, allowing for better modularity and maintainability.
def create_role(conn: object, role_name: str) -> None:
"""Create a new Snowflake role with the given name."""
conn.cursor().execute(f"CREATE ROLE IF NOT EXISTS {role_name}")
Implementation Details
The core challenge was handling role hierarchies. We decided to use a topological sort algorithm because it naturally handles dependency ordering and allows for efficient role creation.
from collections import deque
def topological_sort(graph: dict) -> list:
"""Perform a topological sort on the given graph."""
in_degree = {node: 0 for node in graph}
# Calculate in-degrees for all nodes
for node in graph:
for neighbour in graph[node]:
in_degree[neighbour] += 1
# Initialize a queue with nodes having an in-degree of 0
queue = deque(n for n, d in in_degree.items() if d == 0)
# Initialize the result list
result = []
while queue:
node = queue.popleft()
result.append(node)
# Decrease in-degrees for neighbouring nodes
for neighbour in graph[node]:
in_degree[neighbour] -= 1
if in_degree[neighbour] == 0:
queue.append(neighbour)
return result
Testing Strategy
We chose pytest over unittest due to its fixture system and parametrize support, which provide a more efficient testing framework for our use case. The trade-off is a slightly steeper learning curve for new team members.
import pytest
@pytest.mark.parametrize("role", ["analyst", "engineer", "admin"])
def test_create_role(role: str) -> None:
"""Verify that the create role function returns the expected result."""
assert role in ["analyst", "engineer", "admin"]
Conclusion
We developed a robust RBAC automation pipeline that reduces manual effort and improves auditability. By opting for a declarative approach over imperative scripts, we made it easier to review changes in pull requests, enhancing overall code quality and maintainability.