How to enable row-level security (RLS) in PostgreSQL

What is Row-Level Security (RLS)?

Row-level security (RLS) is a feature in PostgreSQL that allows you to define policies to restrict access to individual rows in a table. This ensures that users can only access or modify data that they're authorized to see.

Enabling RLS on a table

First, let's turn on RLS for a specific table. Here, I'll assume a table named employees.

ALTER TABLE employees ENABLE ROW LEVEL SECURITY;

By default, this will block all access to the table unless a policy is applied. To still allow the table owner access, you can use:

ALTER TABLE employees FORCE ROW LEVEL SECURITY;

Create an RLS policy

A policy dictates what data can be accessed by whom. Let's create a policy to allow users to see only the rows where the department matches their role.

CREATE POLICY employee_policy
    ON employees
    FOR SELECT
    USING (department = current_setting('my_app.user_role'));

Apply an RLS policy to table

After creating a policy, apply it to the table using ALTER TABLE.

ALTER TABLE employees FORCE ROW LEVEL SECURITY;
ALTER TABLE employees ALTER POLICY employee_policy USING (department = current_setting('my_app.user_role'));

Test RLS

You can test whether RLS is working by changing the current setting for the role and trying a SELECT query.

SET my_app.user_role = 'Engineering';
SELECT * FROM employees;

Using variables for dynamic RLS policies

You can use session variables to make the policies dynamic.

ALTER TABLE employees ALTER POLICY employee_policy USING (department = current_setting('my_app.user_role'));

Then, whenever a user logs in, set the session variable to their role.

SET my_app.user_role = '<user-role>';

When to Use RLS

RLS is best for multi-tenant applications or applications with different user roles that require access to subsets of data. It's not recommended for systems with extremely high throughput because it might add performance overhead.

Using Basedash with RLS

If your team is using Basedash as a database management tool, enabling RLS in PostgreSQL will work in tandem with the team-based permission controls in Basedash. This provides an extra layer of security and specificity, ensuring that users can only access the data they're permitted to, both from the application and from the admin panel. You can connect as many database roles to Basedash as you’d like and use them for various views across teams as needed.

Alternatives to RLS

If RLS doesn't fit your needs, consider using other PostgreSQL features like Views or Table Partitioning for achieving similar results.