Row-Level Access Control: Row-Level Security (RLS)

If role and table-level permissions are like a "security guard at the front door," then Row-Level Security is like a personal guard checking if a user can enter each individual room in the building.

RLS helps make sure a user can only work with the rows in a table they're allowed to. For example:

• In an online store, a manager should only see their own orders.
• In a CRM platform, an employee only sees clients from their own team.
• In a banking system, a client should only have access to their own accounts.

How RLS Works

RLS is based on the concept of access policies. Policies define which rows in a table are visible to a role or user, and which are available for changes (INSERT, UPDATE, DELETE).

By default, RLS is disabled for all tables, and you have to turn it on manually.

Enabling RLS for a Table

Let's create an orders table to store online store orders:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    product_name TEXT NOT NULL,
    price NUMERIC NOT NULL
);

Let's add some test data:

INSERT INTO orders (user_id, product_name, price)
VALUES
    (1, 'Smartphone', 500),
    (2, 'Laptop', 1000),
    (1, 'Headphones', 100),
    (3, 'Keyboard', 50);

Now let's enable RLS for this table:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

What did we just do? We activated the RLS mechanism, but no policies are set yet. Until you create policies, RLS doesn't actually affect access to the table.

Creating an Access Policy

To add rules for managing access, use the command:

CREATE POLICY policy_name
ON table
[FOR { SELECT | INSERT | UPDATE | DELETE }]
TO role
USING (access_condition)
WITH CHECK (check_condition);

• FOR: defines which operations the policy applies to (SELECT, INSERT, UPDATE, DELETE). If you skip this, the policy applies to all operations.
• TO: specifies which roles the policy is active for. If not set, the policy applies to any role.
• USING: sets the condition under which rows will be visible to the user.
• WITH CHECK: defines the check condition for INSERT and UPDATE operations.

Example: Access Only to Your Own Orders

Let's create a policy that lets users see only their own orders. Let's say the current user's ID matches user_id in the table:

CREATE POLICY user_can_view_own_orders
ON orders
FOR SELECT
USING (user_id = current_user::INT);

What's happening here?

• The policy is called user_can_view_own_orders.
• It applies to the SELECT operation.
• Only rows where user_id matches the current user's ID (current_user) are visible.

So, if you're logged in as a user with user_id = 1, you'll only see your own orders.

Checking How RLS Works

Let's create two users: user1 and user2.

CREATE ROLE user1 LOGIN PASSWORD 'password1';
CREATE ROLE user2 LOGIN PASSWORD 'password2';

Let's grant the roles access to the table:

GRANT SELECT ON orders TO user1, user2;

Now let's switch to user1 and try to run a query:

SELECT * FROM orders;

Result: you'll only see rows where user_id = 1.

Policies for INSERT

Let's say we want to let users add only their own orders (i.e., the row's user_id must match the current user's ID).

Let's create a policy for INSERT:

CREATE POLICY user_can_insert_own_orders
ON orders
FOR INSERT
WITH CHECK (user_id = current_user::INT);

Now, if user1 tries to add an order where user_id isn't 1, the query will fail with an error.

Policies for UPDATE and DELETE

You can create similar policies for updating and deleting data. For example:

To update your own data:

CREATE POLICY user_can_update_own_orders
ON orders
FOR UPDATE
USING (user_id = current_user::INT)
WITH CHECK (user_id = current_user::INT);

To delete your own data:

CREATE POLICY user_can_delete_own_orders
ON orders
FOR DELETE
USING (user_id = current_user::INT);

Applying Multiple Policies

You can create multiple policies for one table. They'll all be applied at the same time. For example, if several rules apply to one role, PostgreSQL will check all of them (logical AND).

Checking and Debugging RLS

To check which policies are set for a table, use the command:

\di+ table_name

If you want to temporarily disable RLS (for example, for an admin), run:

ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

The admin role SUPERUSER isn't limited by RLS by default, so it can see all the data.

Common Mistakes When Setting Up RLS

Mistakes can happen if you forget to:

• Activate RLS with ALTER TABLE ... ENABLE ROW LEVEL SECURITY.
• Set up the right policies for all operations (SELECT, INSERT, UPDATE, DELETE).
• Correctly specify the condition in USING and WITH CHECK. For example, if you don't check user_id, you might accidentally open access to all rows.

Row-Level Security is one of the most powerful security tools in PostgreSQL. It lets you set up access down to the row and automate access management, which is super important for complex apps with high data protection requirements.