What is RLS in Postgres DB?

 Excellent — let’s go step by step.

---

🧩 What is Row-Level Security (RLS) in PostgreSQL?

Row-Level Security (RLS) is a fine-grained access control feature in PostgreSQL that lets you restrict which rows of a table a particular user can SELECT, INSERT, UPDATE, or DELETE.

It’s like adding per-row access filters directly inside the database layer — instead of relying only on application-side filtering.

---

🔐 Why RLS?

RLS is especially useful for:

• Multi-tenant systems — where different tenants share the same tables, but should only see their own data.

• User-specific access — ensuring users can view or modify only rows they are entitled to, even if someone bypasses application logic.

It provides defense in depth, since the restriction is enforced inside PostgreSQL itself.

---

🧱 How it works

Step 1. Enable RLS on a table

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Step 2. Define RLS policies

Policies specify who can do what, and which rows they can access.

Example:

CREATE POLICY tenant_isolation_policy
ON orders
FOR SELECT
USING (tenant_id = current_setting('app.current_tenant')::uuid);

Now, when a user runs:

SELECT * FROM orders;

PostgreSQL automatically appends the condition:

WHERE tenant_id = current_setting('app.current_tenant')::uuid

---

⚙️ Key Concepts

Concept	Description
RLS policy	Rule that determines which rows a role can access
USING clause	Condition evaluated on each row for SELECT/UPDATE/DELETE
WITH CHECK clause	Condition that must be true for INSERT/UPDATE (rows being added/changed)
PERMISSIVE / RESTRICTIVE	Policy combination behavior (default = permissive = OR logic)
current_setting()	Used to inject tenant or user context dynamically

---

🧠 Example — Multi-tenant setup

Table

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  tenant_id UUID NOT NULL,
  user_id UUID NOT NULL,
  product TEXT,
  amount NUMERIC
);

Enable RLS

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Create a policy

CREATE POLICY tenant_data_access
ON orders
USING (tenant_id = current_setting('app.current_tenant')::uuid);

Add tenant context per connection

Before executing queries:

SET app.current_tenant = 'tenant-1111-2222-3333-4444';

Now:

SELECT * FROM orders;

will automatically filter:

WHERE tenant_id = 'tenant-1111-2222-3333-4444';

---

🧾 Insert Policy Example

If you want to control what tenants can insert:

CREATE POLICY tenant_insert_policy
ON orders
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

---

🚫 Force RLS for all users

To prevent bypassing RLS by superusers or table owners:

ALTER TABLE orders FORCE ROW LEVEL SECURITY;

---

🧩 Example with User-level Filtering

You can also restrict by user:

CREATE POLICY user_access_policy
ON orders
USING (user_id = current_setting('app.current_user_id')::uuid);

Then set per-session:

SET app.current_user_id = 'user-abc-123';

Now users only see their own orders.

---

⚖️ Combining Policies

You can define multiple policies for different roles or actions:

CREATE POLICY admin_view_all
ON orders
FOR SELECT
TO admin_role
USING (true);

CREATE POLICY user_restricted
ON orders
FOR SELECT
TO app_user_role
USING (user_id = current_setting('app.current_user_id')::uuid);

Policies for the same command type are combined with OR (permissive).
If you want stricter control, define RESTRICTIVE policies (combined with AND).

---

✅ Summary

Feature	Purpose
RLS	Restricts access to individual rows inside PostgreSQL
USING	Controls which rows can be read/modified
WITH CHECK	Controls which rows can be inserted/updated
FORCE RLS	Enforces policy for all users
Context variables	Pass tenant/user IDs dynamically via SET
Policies per role	Allows flexible role-based row-level control

---

Would you like me to show how to integrate JWT (RS256) authentication with PostgreSQL RLS — e.g., automatically setting the tenant and user context from token claims during a DB connection?