Row-Level Security

Introduction

This is an Access Control feature used to secure data. It determines whether a user has the right to see or modify a specific row of data.

RLS allows you to control SELECT, INSERT, UPDATE, or DELETE privileges on specific rows of data based on the identity of the user executing the command.
How it works
- First, you need to enable RLS for each table you want to use
- Then, you define Policies to permit how users can perform data operations. If no Policy exists, by default no one (except the Superuser/Owner) can see any row of data in the table anymore
- Next, when a query is submitted, the system does not run that command immediately but performs a step called Query Rewrite
- It will automatically add filter conditions (like a WHERE clause) based on the created Policies to hide rows that the user does not have permission to view.
Example: In a SaaS system, you enable RLS to ensure that customers of one Company only see data rows belonging to that Company, remaining completely unaware of the existence of data from other Companies (even though all data is stored in the same Table)

Characteristics

Performance impact
- Since RLS automatically adds filter conditions to the SQL query, its performance depends on whether those conditions are optimized
- You should ensure that columns used in the Policy have an Index created so Postgres can query faster.
Common applications
- RLS is extremely useful in Multi-tenant applications (SaaS software, where multiple companies share the same Database but must absolutely never see each other's data)
- Instead of writing manual filtering code at the Application (Backend) layer which is highly prone to omissions, you use RLS to block it right at the Database layer to ensure absolute security.

Detail

First, create the tables and Index as follows

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    price NUMERIC(12, 2) NOT NULL,
    stock INT NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    customer_name VARCHAR(255) NOT NULL,
    total_amount NUMERIC(12, 2) NOT NULL,
    status VARCHAR(50) NOT NULL, 
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_tenant ON products(tenant_id);
CREATE INDEX idx_orders_tenant_status ON orders(tenant_id, status);

Enable Row level security for tables and also for the Owner user

ALTER TABLE tenants ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

ALTER TABLE tenants FORCE ROW LEVEL SECURITY;
ALTER TABLE products FORCE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

After that, create Users and grant permissions to use the Schema and Tables as follows

CREATE USER app_user WITH PASSWORD 'password';
CREATE USER "36321616-093d-491e-b9b3-49256f59585c" WITH PASSWORD 'password';

GRANT USAGE ON SCHEMA public TO "36321616-093d-491e-b9b3-49256f59585c";
GRANT USAGE ON SCHEMA public TO app_user;

GRANT ALL ON TABLE tenants TO "36321616-093d-491e-b9b3-49256f59585c";
GRANT ALL ON TABLE tenants TO app_user;
GRANT ALL ON TABLE products TO app_user;
GRANT ALL ON TABLE orders TO app_user;

Next, create the Policy as follows

CREATE POLICY tenant_read_policy ON tenants
    FOR ALL
    TO public
    USING (id::text = current_user);

CREATE POLICY product_write_policy ON products
    FOR ALL 
    TO app_user
    USING (
        tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
    )
    WITH CHECK (
        tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
    );

CREATE POLICY order_select_policy ON orders
    FOR SELECT
    TO app_user
    USING (
        tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
        AND status <> 'cancelled'
    );

CREATE POLICY order_insert_policy ON orders
    FOR INSERT
    TO app_user
    WITH CHECK (
        tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
    );

CREATE POLICY order_update_policy ON orders
    FOR UPDATE
    TO app_user
    USING (
        tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
        AND status <> 'cancelled'
    )
    WITH CHECK (
        tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
    );

Policy 1: This Policy only allows operations on the Tenant whose id matches the current user
- ON tenants: creates the Policy for table tenants
- FOR ALL: applies to SELECT, INSERT, UPDATE, DELETE
- TO public: applies to all current Users and Users created in the future
- USING (id::text = current_user): this condition automatically appends WHERE id = current_user into the corresponding statement, since the id column type is UUID, I use id::text to cast it to TEXT to compare it with current_user
Policy 2: Policy restricts that a Tenant can only add products with a tenant_id matching their own ID and can only edit or delete products owned by that Tenant
- TO app_user: only applies to the app_user created above
- tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid
- app.current_tenant_id is the value passed into each Transaction, which will be retrieved using the current_setting function
  - If using current_user as required by Policy 1, each Tenant would need a different User
  - This approach is not suitable for a large system where multiple Pods connect via a Connection Pool that uses only 1 User
  - Therefore, using current_setting to let the user pass their own tenant ID for processing is a more reasonable and effective solution
- NULLIF checks if app.current_tenant_id has a value to use, otherwise it returns ''
- ::uuid casts the result to the UUID type
- WITH CHECK is used to inspect data about to be used in an INSERT or data after an UPDATE, ensuring it must be assigned with your correct tenant_id (not accidentally assigned to another tenant)
Policy 3: Creates a Policy for SELECT to only allow viewing your own orders (except cancelled orders)
Policy 4: Creates a Policy for INSERT to only allow creating new orders for your own tenant
Policy 5: Creates a Policy for UPDATE
- Only allows editing your own uncancelled orders and the data after updating must still belong to your tenant.
- No need to create a Policy for DELETE because by default deletion is not allowed in that table

SELECT

First, seed data for tables as follows

Then, switch to this role to test the tenant Policy

SET ROLE "36321616-093d-491e-b9b3-49256f59585c";

For the following examples, use this role to check

SET ROLE app_user;

BEGIN;
SET LOCAL app.current_tenant_id = '3ecdc59a-5e7f-40a1-9bad-b55916c9b4c6';
SELECT * FROM products
SELECT * FROM orders

Retrieve the product records belonging to the current tenant
Retrieve the order records belonging to the current tenant where status <> 'cancelled'
Use SET LOCAL so that app.current_tenant_id only exists within that specific Transaction, avoiding other requests from using the same tenant information
This is a commonly used approach when multiple Pods connect to the Database through a Connection Pool, because the Connection Pool uses only 1 Role making it very difficult to apply current_user, so you should pass an additional tenant_id for proper validation

If you pass a tenant_id that contains no data, you will not find the corresponding product information

BEGIN;
SET LOCAL app.current_tenant_id = '3ecdc59a-5e7f-40a1-9bad-b55916c9b4c1';
SELECT * FROM products

INSERT

BEGIN;
SET LOCAL app.current_tenant_id = '3ecdc59a-5e7f-40a1-9bad-b55916c9b4c6';

-- Statement 1
INSERT INTO products (tenant_id, name, price, stock) 
VALUES ('3ecdc59a-5e7f-40a1-9bad-b55916c9b4c6', 'Invalid tenant', 110.00, 100);

-- Statement 2
INSERT INTO products (tenant_id, name, price, stock) 
VALUES ('3ecdc59a-5e7f-40a1-9bad-b55916c9b4c1', 'Invalid tenant', 110.00, 100);

Statement 1: INSERT succeeds because it uses the same tenant_id as the active one
Statement 2: Policy blocks it because it attempts to INSERT with a different tenant_id than the active one

UPDATE & DELETE

BEGIN;
SET LOCAL app.current_tenant_id = '3ecdc59a-5e7f-40a1-9bad-b55916c9b4c6';

-- Statement 1
UPDATE orders 
SET status = 'shipped' 
WHERE id = 'e838d9ea-d548-4fd5-9fe3-aab0ed2c9037';

-- Statement 2
UPDATE orders 
SET total_amount = 100.00 
WHERE id = 'b466cf05-11cc-4d34-a7a4-e1e436b6bbf5';

-- Statement 3
UPDATE orders 
SET tenant_id = '3ecdc59a-5e7f-40a1-9bad-b55916c9b4c2'
WHERE id = 'e838d9ea-d548-4fd5-9fe3-aab0ed2c9037';

-- Statement 4
DELETE FROM orders 
WHERE id = 'e838d9ea-d548-4fd5-9fe3-aab0ed2c9037';

Statement 1: Changes status to shipped, this statement is valid so it executes successfully
Statement 2: Update target is an order with a status of cancelled, which is blocked by the Policy
Statement 3: Updates the tenant_id for the order, which is blocked by the Policy, resulting in no order found to update
Statement 4: Deleting the order is also blocked by the Policy, so no row will be found to delete