Row Level Security with Postgres (via Hasura AuthZ)

Security is one of the most critical topics when managing a database. Thankfully, in 2016 developers got help with this challenging task when Postgres 9.5 was released with Row-level Security (RLS). RLS allows application developers to control access to rows in a database table based on rules evaluated during query execution.

This blog will cover the basics of Postgres RLS and the Hasura way of solving these permission rules.

To enable RLS on a table, we use the ALTER TABLE ... ENABLE ROW LEVEL SECURITY command. Once run, only the table owner or admin can CRUD rows on the table. To give permissions to other users or roles, we create policies.

Policies

Once RLS is enabled, we use policies to create permission rules on the table. A policy is a per-table SQL expression that returns a boolean representing if the permission is satisfied.

The two types of policy expressions are USING, which checks existing rows, and WITH CHECK, which covers new rows. You can combine policies with OR or AND clauses.

An example from the Postgres documentation that allows all users to view all rows in the users table but only modify their own:

CREATE POLICY user_sel_policy ON users
    FOR SELECT
    USING (true);

CREATE POLICY user_mod_policy ON users
    USING (user_name = current_user);

Passing data to the policy

In the above example, the users table user_name column is compared to the current_user value. The current_user is the currently active Postgres role. Roles can be thought of as either a database user or a group of database users, depending on how the role is set up. Your role is set initially by how you connect to Postgres and can be changed later using the SET ROLE command.

However, we usually handle roles at the application layer instead of the database layer. For example, usually, the front end has its own login system. They don’t create a Postgres role on every signup. Instead, we can set session variables in Postgres and read them in our policies via current_setting(). Check out this excellent article by Tomas Vondra for an introduction to this topic. Here is an excerpted example:

SET my.username = 'tomas';

CREATE POLICY chat_policy ON chat
    USING (current_setting('my.username') IN (message_from, message_to))
    WITH CHECK (message_from = current_setting('my.username'));

Postgres row-level security is a deep topic with a lot of nuances. Please make sure to read the official documentation before implementing.

Instead of using Postgres's RLS, Hasura has its own authorization system (AuthZ) for several reasons:

• Hasura federates data from multiple sources such as different databases like BigQuery and external APIs like Remote Schemas. Now you can use the same AuthZ solution everywhere.

• With Postgres RLS, every user query would execute the policies separately, which can affect performance. With Hasura’s implementation, similar queries are batched, allowing for million+ user performance.

Authentication (AuthN)

Before setting up the AuthZ rules, Hasura’s authorization system must get a user’s role and session variables. These can come from a JWT or a webhook. Session variables are prefixed with X-HASURA-. You can also configure what role an unauthenticated user would use.

Authorization (AuthZ)

For Authorization or Access Control, Hasura helps you define granular role-based access control rules for every field in your GraphQL schema (granular enough to control access to any row or column in your database).

Hasura uses the role/user information from the AuthN layer and the request itself to validate the operation against your rules. If the request is allowed, it generates a SQL query, which includes the row/column-level constraints from the access control rules. It then sends it to the database to perform the required operation (fetch the rows needed for queries, insert/edit rows for mutations, etc.).

Earlier, we looked at an example of Postgres RLS restricting users to only seeing their rows. Here’s how that looks in Hasura:

This permission rule reads: “For the role user, table authors, and operation select/query, allow access to those rows where the value in the id column is the same as the value in the X-Hasura-User-ID session variable”.

Like Postgres RLS, the row-level permissions are boolean expressions that help you restrict access to rows depending on the operation. You can create powerful rules based on session variables, the types of columns, static values, and relationships. For more details, please see the Hasura row-level security documentation.

AuthZ in the application layer gives us benefits like using the permissions system for other databases and APIs. You may still need to add permissions to your database if other applications have access.

This article covered Postgres row-level security basics and how Hasura approaches the same problem. These basics should help you get started building secure applications that users love. Try out what you learned on a free Hasura Cloud instance!