Managing User Roles in Airflow: The Airflow-EDW-Grants Plugin for Amazon Redshift

Introduction

When managing multiple Redshift databases, especially if you have a separate Airflow instance for each one (like we do for a client), handling users, roles, and access can quickly become a complex task. Traditionally, this would require running SQL queries to create roles, users, and manage permissions on your Redshift databases. For those not familiar with SQL, this can be a daunting challenge.

In this post, we introduce the Airflow-EDW-Grants plugin—a solution that simplifies the process. With this plugin, you can easily create users, roles, and manage their relationships directly from Airflow, without needing to write a single line of SQL or interact with Redshift metadata tables.

This plugin is designed for users who need to manage Redshift roles and users but don't necessarily work with Redshift every day. Whether you're a Project Manager or someone without deep SQL knowledge, this tool provides an easy interface to control access to your Redshift databases. Once set up, non-technical users can manage roles and users efficiently, all within Airflow.

The plugin is available for installation via PyPI and its source code can be found on GitHub.

How to Start Using the Airflow-EDW-Grants Plugin

To get started with our plugin, you can install it directly from PyPI or view the source code on GitHub. Simply add airflow-edw-grants to your requirements.txt file, then restart your Airflow web server.

Next, configure the redshift_connection_grants_name Airflow variable with the name of your Redshift connection, or create a Redshift connection in Airflow named edw_con if it isn’t already present.

Once set up, navigate to EDW -> Permissions in the Airflow UI to access the Roles and Users tables. Here, you can efficiently manage user roles and permissions within your Redshift environment.

Note: Only users with the admin role can access this plugin, ensuring that sensitive permissions and role management features remain securely restricted to authorized personnel.

What Can You Do with the Airflow-EDW-Grants Plugin?

The Airflow-EDW-Grants plugin provides an intuitive interface within the Airflow UI to manage users and roles directly in your Redshift environment. This plugin offers two main sections—Roles and Users—that help simplify and streamline permission management.

Roles Management

In the Roles table (displayed on the right side), you can view, add, delete, and edit roles:

• Create a New Role: To create a new role, click the “+” icon, enter a unique role name, and attach any existing roles that the new role should inherit permissions from. Once you submit, the new role appears in the Roles table.
  

This approach allows for hierarchical role structuring by granting roles the permissions of other roles, making it easier to manage complex permission schemes.

Users Management

In the Users table (located on the left side), you can add new users, modify their roles, and manage permissions, including granting users the ability to create database objects:

• Add a New User: Click the “+” icon to create a new user. When setting the password, ensure it meets the requirement of including at least one uppercase letter.
  

• Modify User Roles: You can assign or remove roles from users and toggle their permissions to create database objects.

• Delete a User: If a user no longer needs access, you can remove them from the system. However, if a user has database editing privileges, you must revoke those privileges before deletion.

Note: If a user has permissions to edit the database, you’ll need to adjust those settings before they can be removed. 

Atomicity

In this plugin, creating a new user and assigning roles follows an all-or-nothing principle: either all related database operations succeed, or none do. This ensures that if the process encounters an error—such as failing to assign a role or grant a specific permission—the entire transaction is rolled back, leaving the database unchanged.

In Amazon Redshift (inherited from PostgreSQL), Data Definition Language (DDL) statements (such as CREATE, ALTER, and DROP) are handled as transactional operations, meaning they can be rolled back if an error occurs or if a transaction is explicitly canceled. This allows a single BEGIN...END transaction block to encompass DDL, Data Query Language (DQL), and Data Manipulation Language (DML) operations. Thus, you can execute a series of commands in one block, ensuring atomicity across different SQL operation types.

Fault Tolerant

To enhance user experience, the @failure_tolerant_front_end decorator is applied to each HTTP method. This decorator captures errors and gracefully handles them by redirecting users back to the main plugin page, rather than presenting a broken Airflow page. This approach maintains a smooth and user-friendly interface, even when backend issues occur.

Security

By default, this plugin is hidden in the menu for users without the necessary role-based permissions, making it accessible only to administrators. However, since HTTP methods could still be accessed directly, we added an @admin_only decorator to each method, which enforces that only users with the admin role can execute plugin actions. This provides an extra layer of security for managing sensitive database roles and permissions.

To prevent SQL injection, all Data Manipulation Language (DML) queries use SQLAlchemy's ORM queries. For Data Definition Language (DDL) queries, parameterized queries are used wherever possible, and any additional variables are validated rigorously to ensure they meet safe standards for database operations.

Conclusion

The Airflow-EDW-Grants plugin simplifies user and role management within Amazon Redshift for teams using Apache Airflow. By providing an intuitive interface, this plugin allows users to create and manage roles and permissions without needing extensive database administration experience. Its focus on security and transactional integrity ensures that changes are handled safely. We invite you to try out the Airflow-EDW-Grants plugin to streamline your workflow in managing database roles and permissions. For more details, visit the plugin on PyPI and check out the source code on GitHub.