Hasura GraphQL on Snowflake using RBAC: A secure and scalable data access solution

When it comes to managing access control for your databases, role-based access control (RBAC) is a popular choice. In this post, we'll take a look at what RBAC is, its advantages, and how Hasura's RBAC system can be used to secure your Snowflake data warehouse.

RBAC is a security model used to control access to resources based on the roles of the users. In this model, roles are defined based on the responsibilities and permissions of the users. Access control policies are then defined based on these roles, rather than for individual users.

In an RBAC system, you might define a "manager" role that has access to certain resources and an "employee" role that has access to a different set of resources. Users are then assigned to these roles, and their access to resources is determined by the permissions associated with the role.

RBAC provides several advantages over other access control models:

• Scalability: RBAC is highly scalable and can be used to manage access control for large and complex systems.
• Simplicity: RBAC is easy to understand and implement, as it is based on the roles and responsibilities of users.
• Granularity: RBAC provides fine-grained access control, allowing access to be controlled at a very granular level.
• Flexibility: RBAC allows access control policies to be easily modified as the needs of the organization change.

Hasura's RBAC system provides several advantages over Snowflake's native RBAC system:

• Integrations: Hasura's RBAC system can be easily integrated with other Hasura features, such as row-level security (RLS), to provide even more granular access control. Hasura's RBAC system can also be integrated with other third-party authentication and authorization systems, such as Auth0, to provide even more flexibility and control.
• Ease of use: Hasura's RBAC system is easy to use, with a simple and intuitive user interface.
• Automation: Hasura's RBAC system can be automated, allowing access control policies to be easily modified as the needs of the organization change.
• Performance: Hasura's RBAC system is designed to be performant, with minimal overhead and fast response times. Check out this blog post.

Let's assume we have a Snowflake data warehouse containing one table – "employees." We want to build a GraphQL API using Hasura that allows users to query the data in these tables based on their roles.

We will set up Hasura to connect to the Snowflake data warehouse using JDBC. We will configure Hasura to use the RBAC roles defined in Snowflake to control access to the data in the GraphQL API.

Then, we will define the roles and permissions using Hasura's RBAC system. We will create one more role along with the default "admin," namely "user." The "admin" role will have full access to all data in the "employees" table, while the "user" role will have access to only his profile in the "employees" table.

The above architecture diagram describes how this row permission works.

If the "admin" user runs this query, they will receive a list of all employees, including their IDs and names.

If the "user" user runs this query, they will only receive their own profile id.

Here’s the architecture of the different components:

Here's how it works:

1.  Connect database from Snowflake: You can connect the database from Snowflake that you would like to use in your APIs and your application.

2.  Define permissions in Hasura: You can define permissions in Hasura for each role that controls access to the data.

3.  Use Hasura to control access to APIs: You can use Hasura's RBAC feature to control access to the APIs based on the roles of the users.

4.  Use Hasura to generate GraphQL APIs: Hasura will auto-generate GraphQL APIs based on your Snowflake database schema and the permissions defined in Hasura.

Now let’s move on to an example to demonstrate how you can use RBAC using Hasura and Snowflake. In this tutorial, we will set up Hasura Cloud to use Snowflake as a data source using JDBC and RBAC.

Prerequisites:

• A Hasura Cloud account
• A Snowflake account with JDBC driver installed and configured
• Basic knowledge of Hasura and GraphQL

Step 1: Create a new Hasura Cloud project
Log in to your Hasura Cloud account and create a new project. Once your project is created, you'll be taken to the project dashboard.

Step 2: Add a new data source
Click on the "Data" tab on the left-hand side of the dashboard, and then click on "Add" to add a new data source. Select "Snowflake" as the data source type and provide the required connection details, including the JDBC connection string, username, and password.

Step 3: Track the Snowflake tables

Click on the "Data" tab on the left-hand side of the dashboard, and then click on Database you just added as a new data source. Go to the “Untracked” tab, select the tables and click “Track Selected.” You should now see the tables on the “Tracked” tab.

Step 4: Set up RBAC
Once your data source is connected, you can set up RBAC to control access to your data. Click on a table you want to set up the permissions for and then click the "Permissions" tab on the left-hand side of the dashboard, and then click on "Add role" to add a new role. Give your role a name, such as "user," and then define the permissions for that role.

There are primarily two types of permissions you can set up for each of Insert, Select, Update and Delete – they are row and column permissions.

You might allow the user role to read only certain rows from the table. Here’s an example of row permission:

{“EMPLOYEE_ID” : {“_eq”:”X-Hasura-User-Id”}}

This only allows the user to select rows from the table where the “EMPLOYEE_ID” equals the Hasura user id.

You can also define permissions at the column level, allowing a role to read data from certain columns but not others. You might also want the user to be able to read the data but not modify or delete it.

Here’s an example of setting the column permissions to allow the user to access only the “EMPLOYEE _ID” column.

Step 5: Test your GraphQL API
Now, you can test your GraphQL API using the Hasura console or by sending requests to your API using a GraphQL client. For example, you might send a query to retrieve data from a specific table:

query MyQuery { EMPLOYEES { EMPLOYEE_ID NAME } }

If your role has permission to read data from the "EMPLOYEES" table, you should receive a response containing the requested data.

You can notice that since “X-Hasura-Role” isn’t selected, it uses the default “admin” role, which can access all rows and columns in the table.

Now, if we select the “user” role in the headers, it can only access specific rows and columns as specified in the permissions before. Otherwise, it throws an error.

In this specific example, the “user” role can only see and select rows with “EMPLOYEE_ID” equal to 2 and can only see the EMPLOYEE_ID column.

In conclusion, Hasura Cloud provides an easy-to-use and powerful platform for creating GraphQL APIs on top of Snowflake using JDBC, with added security through RBAC. By leveraging Hasura's RBAC system, developers can ensure that the right users have access to the right data, while also being able to enforce fine-grained access control down to the row and column level.

The ability to set RBAC rules and policies in Hasura Cloud provides a powerful way to secure and control access to your data, making it ideal for building APIs for sensitive or complex data sources like Snowflake. With Hasura's intuitive UI and seamless integration with Snowflake, developers can easily build and deploy powerful APIs with minimal configuration and maximum security.

Overall, the combination of Hasura Cloud, Snowflake, and JDBC provides an effective way to build scalable, secure, and performant GraphQL APIs on top of a powerful data warehouse. With Hasura's RBAC systems, developers can ensure that their APIs are secure and compliant while also providing rich, flexible access to data.

Sign up now for Hasura Cloud to try this feature!