PostgreSQL - RLS (Row Level Security)

When creating a user in pgAdmin, there's an option 'Bypass RLS'. What is RLS?

 

RLS stands for Row-Level Security. It is a database security feature in PostgreSQL that allows you to control access to specific rows in a table based on the characteristics of the user executing a query.

While standard SQL privileges (like SELECT, INSERT, or UPDATE) apply to the entire table, RLS allows for much more granular control.

---

How RLS Works

Normally, if a user has SELECT permission on a table, they can see every row in that table. With RLS enabled, PostgreSQL applies a "security policy" to the table. This policy acts as an invisible filter that is automatically appended to every query.

For example, in a multi-tenant application where multiple customers store data in the same invoices table, RLS ensures that:

• Customer A only sees rows where customer_id = 'A'.

• Customer B only sees rows where customer_id = 'B'.

Even if Customer A runs SELECT * FROM invoices;, they will never see Customer B's data because the database filters it out at the engine level before returning the results.

---

The 'Bypass RLS' Option

When you see the Bypass RLS toggle in pgAdmin, you are deciding whether that specific user (role) should be exempt from these security filters.

• When it is OFF (Default): The user is subject to any RLS policies defined on tables. If no policy allows them to see a row, they won't see it.

• When it is ON: The user can see every row in every table, regardless of RLS policies.

When to use 'Bypass RLS'

You typically only grant this to administrative roles or specific maintenance accounts.

• Superusers and table owners bypass RLS by default.

• ETL Tools or Backup Jobs: You might give a migration user BYPASSRLS so it can back up the entire dataset without being blocked by row filters.

• General Users: You should almost never enable this for application users or standard employees, as it defeats the purpose of granular data security.

---

Basic SQL Example

If you wanted to set this via a SQL command instead of the pgAdmin UI, it would look like this:

-- Create a user that ignores RLS policies
CREATE ROLE manager_admin WITH LOGIN BYPASSRLS PASSWORD 'secure_password';

-- Or update an existing user
ALTER ROLE frank BYPASSRLS;

 

How to set RLS to achieve Customer A only sees rows where customer_id = 'A' and Customer B only sees rows where customer_id = 'B' ?

 

To achieve this in PostgreSQL, you need to perform three main steps: enable Row-Level Security on the table, create the security policies, and ensure your application users are not superusers (since superusers bypass RLS by default).

Here is how you can set this up using SQL:

---

1. Enable Row-Level Security

By default, tables do not enforce RLS even if policies exist. You must explicitly turn it on for the table you want to protect.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

2. Create the Security Policy

You define a policy that PostgreSQL will automatically wrap around every query. The USING clause acts as a hidden WHERE filter.

In a common application setup, you can use the current_user function to match the database login name to a column in your table:

CREATE POLICY customer_isolation_policy ON invoices
    FOR ALL 
    TO PUBLIC
    USING (customer_id = current_user);

• FOR ALL: Applies to SELECT, INSERT, UPDATE, and DELETE.

• TO PUBLIC: Applies to every role (except owners/superusers).

• USING: This is the logic. If a user named customer_a logs in, PostgreSQL silently adds AND customer_id = 'customer_a' to their queries.

---

3. Testing the Isolation

To see this in action, you can create two test users and try to query the same table.

-- Create two test users
CREATE ROLE customer_a WITH LOGIN PASSWORD 'password123';
CREATE ROLE customer_b WITH LOGIN PASSWORD 'password123';

-- Grant them access to the table
GRANT SELECT, INSERT ON invoices TO customer_a, customer_b;

-- Now, if you log in as 'customer_a':
SELECT * FROM invoices; 
-- Result: Only rows where customer_id = 'customer_a' are returned.

---

Key Implementation Details

Feature	Description
Table Owners	The user who created the table bypasses RLS by default. To test policies, use a separate restricted user.
Performance	RLS has a negligible impact on performance because PostgreSQL treats the policy just like an indexed WHERE clause.
Permissive vs Restrictive	You can have multiple policies. By default, they are "Permissive" (OR logic), but you can make them "Restrictive" (AND logic).

A Note on Session Variables

In many modern web apps (like those built with FastAPI), the app connects to the database using one single "master" user. In that case, you wouldn't use current_user. Instead, you would use a session variable:

1. Set the variable in your app code: SET app.current_customer_id = 'A';

2. Update your policy: USING (customer_id = current_setting('app.current_customer_id'));