Implementing Row-Level Security (RLS) in PostgreSQL

Introduction

Row-Level Security (RLS) restricts which rows are visible to a given database user, ideal for multi-tenant applications.

Prerequisites

• PostgreSQL >=12
• psql or pgAdmin access

Step 1: Enable RLS on Table

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Step 2: Create Policy

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

• USING: filter rows for SELECT, UPDATE, DELETE.
• tenant_id matches session variable.

Step 3: Set Tenant Context

SET app.current_tenant TO '42';

Do this per session after user authentication.

Step 4: Insert Privilege Policy

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

Controls which rows can be inserted.

Step 5: Test RLS

SET app.current_tenant TO '1';
SELECT * FROM orders; -- sees only tenant 1 orders

Summary

PostgreSQL RLS enforces row-level permissions in the database, enhancing security for multi-tenant systems with minimal application changes.