How to enable row-level security (RLS) in PostgreSQL
October 23, 2023
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
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:
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.
Apply an RLS policy to table
After creating a policy, apply it to the table using
You can test whether RLS is working by changing the current setting for the role and trying a SELECT query.
Using variables for dynamic RLS policies
You can use session variables to make the policies dynamic.
Then, whenever a user logs in, set the session variable to their 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.
PostgreSQL UPDATE guide
How to list users in PostgreSQL
How to change the default port in PostgreSQL