PostgreSQL Row Level Security (RLS): Basics and Examples

Row Level Security (RLS): Basics and Examples

What is Postgres Row Level Security?

Row level security (RLS for short) is a PostgreSQL security feature provided by the open source PostgreSQL database. It allows database administrators to define policies to control how specific rows of data display and operate for one or more user roles.

RLS is, in essence, an additional filter you can apply to a PostgreSQL database table. When a user tries to perform an action on a table, this filter is applied before the query criteria or other filtering, and the data is narrowed or rejected according to your security policy.

You can create row level security policies for specific commands like SELECT, INSERT, UPDATE, and DELETE, specify it for ALL commands.

In this article, you will learn:

Why is Row Level Security Important?

Row Level Security is a staple in the world of data access control, and for good reason. When properly implemented, RLS allows multiple users to access the same database, but only access and edit a subset of its rows based on the user’s role and authorization context.

Controlling access at the database level has many advantages, such as the ability to scale access outside of applications. But it also has its drawbacks – it can be hard to manage as your databases grow in size, and can provide a single point of failure in the data warehouse administrator’s account. Implementing RLS in Postgres can be especially challenging, which makes it all the more important to be fully informed on the best practices.

Row Level Security Policies

Here are a few aspects you should know when implementing row-level security policies:

Use ALTER TABLE … ENABLE ROW LEVEL SECURITY—to enable row-level security on the table. If this option is not enabled, your policy cannot be applied to the table.
Use the CREATE POLICY command—to define new row-level security policies for each table.
Each policy grants permission to specific database operations, such as DELETE, UPDATE, or SELECT.
The policy is selective, only applying to rows that match a predefined SQL expression.
USING statements are used to check existing table rows for the policy expression.
WITH CHECK statements are used to check new rows
Policy names can be repeated across tables—you can create a policy with the same name and reuse it across multiple tables.

Here are some parameters you should know:

Name—this is the name of the policy.
table_name—lets you create a name for the table the policy is applied to.
PERMISSIVE—lets you create a permissive policy. This is the default option. Permissive policies can be applied together to a single query.
RESTRICTIVE—lets you create a restrictive policy. Restrictive policies can be applied together to a single query. You can use this parameter to restrict access to tables.
command—the default option is DELETE. ALL. You can also use, SELECT, ALL, INSERT, UPDATE.
role_name—the default is PUBLIC, which means the policy applies to all database users.
using_expression—a SQL expression that returns boolean. Each row is checked against this expression—if it returns false, it is silently suppressed and cannot be viewed or modified by the user. No error is returned.
check_expression—a SQL expression returning boolean, which is used when INSERT or UPDATE operations are performed on the table. Rows are allowed if the policy expression is true, and if it returns false, an error is returned.

Postgres Row Level Security Examples

Here are examples showing how to define RLS policies for your database.

Allowing managers to edit their own rows

Here is an example showing how to create a policy that ensures only members of the “manager” role can access only the rows for a specific account. The code in this and the following example was shared in the PostgreSQL documentation.

The USING clause implicitly adds a WITH CHECK clause, ensuring that members of the manager role cannot perform SELECT, DELETE, or UPDATE operations on rows that belong to other managers, and cannot INSERT new rows belonging to another manager.

Alternatively, instead of providing a specific user role, you can use PUBLIC, which has the same effect, but applies the restrictions to all users of the database.

Applying different access permissions for new rows

The following example lets you apply another policy for new rows added by the user. This can be done by combining two policies—one enables all rows to be viewed by all roles, and the other only allows each user to modify her own rows.

Note that this policy has a different effect on different comments:

When users perform a SELECT, the two policies are combined using an OR operator, allowing users to select all rows
For other commands, only the second policy is applied, allowing each user to perform actions on their own rows.

Disabling row level security

You can turn off RLS, without deleting the policy. When needed, the policy can be re-enabled. To do this, use this line:

ALTER TABLE {table-name} ENABLE ROW LEVEL SECURITY;

Postgres Row-Level Security With Satori

Satori enables you to apply row-level security and dynamic masking over Postgres databases at scale in a simple and intuitive way, without code changes. In addition, you can set access controls and security policies in a single place, regardless of your database capabilities.