Row-Level Security

Row-Level Security (RLS) provides fine-grained access control at the individual row level within database tables. This powerful security feature enables administrators to create policies that determine which rows users can view, insert, update, or delete based on user identity, roles, or custom business logic.

Understanding Row-Level Security

What RLS Provides

Granular Data Access:

Control access to specific rows within tables
Implement tenant isolation in multi-tenant applications
Enforce data segregation based on user attributes
Create custom access rules based on business logic

Policy-Based Security:

Define rules using SQL expressions
Support multiple policies per table for complex scenarios
Combine policies with existing database permissions
Automatic enforcement without application changes

Common Use Cases

Multi-Tenant Applications:

SaaS platforms with customer data isolation
Enterprise applications with department segregation
Healthcare systems with patient data protection
Financial systems with account-based access control

Regulatory Compliance:

GDPR data access restrictions
HIPAA patient information protection
Financial data segregation requirements
Geographic data residency compliance

Organizational Security:

Department-based data access
Regional sales data isolation
Project-based information security
Hierarchical data access control

RLS Policy Components

Policy Syntax and Structure

CREATE POLICY policy_name ON table_name
    [ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
    [ TO { role_name | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
    [ USING ( using_expression ) ]
    [ WITH CHECK ( check_expression ) ]

Policy Elements Explained

Policy Name (policy_name)

Unique identifier within the table scope
Can be reused across different tables
Descriptive names improve maintainability
Multiple policies on same table create OR relationships

Command Scope (FOR clause)

ALL: Applies to all operations (default)
SELECT: Controls which rows can be read
INSERT: Controls which rows can be inserted
UPDATE: Controls which rows can be modified
DELETE: Controls which rows can be removed

Target Roles (TO clause)

role_name: Specific database role or user
PUBLIC: All database users
CURRENT_USER: The currently connected user
SESSION_USER: The user who initiated the session

Access Conditions

USING: Expression that must be true for row visibility
WITH CHECK: Expression that must be true for row modifications
Both use standard SQL expressions and functions
Can reference current user, roles, and custom functions

Policy Evaluation Logic

USING Expression:

Evaluated for SELECT, UPDATE, DELETE operations
Determines which existing rows are visible/accessible
Row is hidden if expression returns false or null
Applied before any WHERE clause in user queries

WITH CHECK Expression:

Evaluated for INSERT and UPDATE operations
Determines which new/modified rows are permitted
Operation fails if expression returns false or null
Ensures data integrity according to security policies

Implementing Row-Level Security

Basic RLS Setup

Step 1: Enable RLS on Target Table

-- Enable row-level security for the table
ALTER TABLE your_table ENABLE ROW LEVEL SECURITY;

Step 2: Create Security Policies

-- Create a basic tenant isolation policy
CREATE POLICY tenant_isolation ON your_table
    USING (tenant_id = current_setting('app.current_tenant_id'));

Step 3: Test Policy Implementation

-- Set tenant context
SET app.current_tenant_id = 'tenant_123';
 
-- Queries now automatically filtered by tenant
SELECT * FROM your_table;  -- Only shows tenant_123 rows

Multi-Tenant Application Example

Table Structure:

-- Create table with tenant isolation column
CREATE TABLE order_details (
    order_id TEXT PRIMARY KEY,
    tenant_name TEXT NOT NULL,
    order_detail TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);
 
-- Enable RLS
ALTER TABLE order_details ENABLE ROW LEVEL SECURITY;

Basic Tenant Policy:

-- Users can only see their own tenant's data
CREATE POLICY tenant_data_isolation ON order_details
    USING (tenant_name = current_user);

Advanced Multi-Policy Setup:

-- Read policy: users see only their tenant's data
CREATE POLICY tenant_read_access ON order_details
    FOR SELECT
    USING (tenant_name = current_user);
 
-- Write policy: users can only modify complete orders
CREATE POLICY order_write_policy ON order_details
    FOR UPDATE
    USING (tenant_name = current_user)
    WITH CHECK (order_id IS NOT NULL AND order_detail IS NOT NULL);
 
-- Insert policy: enforce tenant assignment
CREATE POLICY tenant_insert_policy ON order_details
    FOR INSERT
    WITH CHECK (tenant_name = current_user);

Role-Based Access Control

Department-Based Access:

-- Create roles for different departments
CREATE ROLE sales_team;
CREATE ROLE finance_team;
CREATE ROLE admin_team;
 
-- Create table with department data
CREATE TABLE company_data (
    record_id SERIAL PRIMARY KEY,
    department TEXT NOT NULL,
    sensitive_data TEXT,
    public_data TEXT
);
 
ALTER TABLE company_data ENABLE ROW LEVEL SECURITY;
 
-- Department isolation policy
CREATE POLICY department_access ON company_data
    TO sales_team
    USING (department = 'sales');
 
CREATE POLICY finance_access ON company_data
    TO finance_team
    USING (department = 'finance');
 
-- Admin sees all data
CREATE POLICY admin_full_access ON company_data
    TO admin_team
    USING (true);

Dynamic Security Policies

User Attribute-Based Access:

-- Create user attributes table
CREATE TABLE user_attributes (
    username TEXT PRIMARY KEY,
    region TEXT,
    clearance_level INTEGER,
    department TEXT
);
 
-- Create data table with access requirements
CREATE TABLE classified_data (
    doc_id SERIAL PRIMARY KEY,
    content TEXT,
    required_clearance INTEGER,
    region_restriction TEXT,
    created_by TEXT
);
 
ALTER TABLE classified_data ENABLE ROW LEVEL SECURITY;
 
-- Complex access policy based on multiple criteria
CREATE POLICY clearance_based_access ON classified_data
    USING (
        -- User must have sufficient clearance
        (SELECT clearance_level FROM user_attributes 
         WHERE username = current_user) >= required_clearance
        AND 
        -- User must be in correct region (if restricted)
        (region_restriction IS NULL OR 
         (SELECT region FROM user_attributes 
          WHERE username = current_user) = region_restriction)
        AND
        -- User can always see their own documents
        (created_by = current_user)
    );

Advanced RLS Patterns

Time-Based Access Control

-- Table with time-sensitive data
CREATE TABLE time_sensitive_records (
    record_id SERIAL PRIMARY KEY,
    data TEXT,
    valid_from TIMESTAMP,
    valid_until TIMESTAMP,
    access_role TEXT
);
 
ALTER TABLE time_sensitive_records ENABLE ROW LEVEL SECURITY;
 
-- Time-based access policy
CREATE POLICY time_based_access ON time_sensitive_records
    USING (
        NOW() BETWEEN valid_from AND valid_until
        AND 
        pg_has_role(access_role, 'USAGE')
    );

Hierarchical Data Access

-- Organization hierarchy table
CREATE TABLE org_hierarchy (
    user_id TEXT,
    manager_id TEXT,
    department TEXT,
    level INTEGER
);
 
-- Employee data with hierarchical access
CREATE TABLE employee_data (
    employee_id TEXT PRIMARY KEY,
    manager_id TEXT,
    salary DECIMAL,
    performance_data TEXT
);
 
ALTER TABLE employee_data ENABLE ROW LEVEL SECURITY;
 
-- Hierarchical access policy
CREATE POLICY hierarchical_access ON employee_data
    USING (
        -- Users can see their own data
        employee_id = current_user
        OR
        -- Managers can see their direct reports
        manager_id = current_user
        OR
        -- Senior managers can see their entire organization
        EXISTS (
            SELECT 1 FROM org_hierarchy o1
            JOIN org_hierarchy o2 ON o1.department = o2.department
            WHERE o1.user_id = current_user 
            AND o1.level >= 3
            AND o2.user_id = employee_data.employee_id
        )
    );

Managing RLS Policies

Policy Administration

Viewing Existing Policies:

-- List all policies for a specific table
SELECT * FROM pg_policies WHERE tablename = 'your_table_name';
 
-- View policy details with SQL expressions
SELECT 
    schemaname,
    tablename,
    policyname,
    permissive,
    roles,
    cmd,
    qual,
    with_check
FROM pg_policies 
WHERE tablename = 'order_details';

Modifying Policies:

-- Update existing policy
ALTER POLICY tenant_isolation ON order_details
    USING (tenant_name = current_user OR current_user = 'admin');
 
-- Rename policy
ALTER POLICY old_policy_name ON table_name RENAME TO new_policy_name;

Removing Policies:

-- Drop specific policy
DROP POLICY IF EXISTS policy_name ON table_name;
 
-- Disable RLS entirely (removes all policies)
ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;

Policy Testing and Validation

Testing Policy Behavior:

-- Test as different users
SET ROLE tenant_user_1;
SELECT COUNT(*) FROM order_details;  -- Should show only tenant_1 data
 
SET ROLE tenant_user_2;  
SELECT COUNT(*) FROM order_details;  -- Should show only tenant_2 data
 
-- Reset to superuser to see all data
RESET ROLE;
SELECT COUNT(*) FROM order_details;  -- Shows all data

Performance Testing:

-- Analyze query performance with RLS
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM order_details WHERE order_id = 'specific_order';
 
-- Compare with RLS disabled
ALTER TABLE order_details DISABLE ROW LEVEL SECURITY;
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM order_details WHERE order_id = 'specific_order';

Performance Considerations

Optimizing RLS Performance

Index Strategy:

Create indexes on columns used in RLS policies
Consider composite indexes for complex policy expressions
Monitor query performance after RLS implementation
Use query plans to identify performance bottlenecks

-- Create index for tenant-based policies
CREATE INDEX idx_order_details_tenant ON order_details(tenant_name);
 
-- Composite index for complex policies
CREATE INDEX idx_classified_data_access 
ON classified_data(required_clearance, region_restriction, created_by);

Policy Expression Optimization:

Use simple, fast-executing expressions when possible
Avoid complex subqueries in policy expressions
Consider using functions for reusable policy logic
Test policy performance with representative data volumes

Monitoring and Maintenance

Performance Monitoring:

-- Monitor slow queries with RLS
SELECT 
    query,
    mean_time,
    calls,
    total_time
FROM pg_stat_statements 
WHERE query LIKE '%your_table%'
ORDER BY mean_time DESC;

Regular Maintenance:

Review and update policies as business requirements change
Monitor policy effectiveness and user access patterns
Validate policy logic with sample data sets
Document policy purposes and business rules

Security Best Practices

Policy Design Guidelines

Security-First Approach:

Default to restrictive policies, then expand as needed
Test policies thoroughly before production deployment
Use explicit role assignments rather than PUBLIC when possible
Regularly audit policy effectiveness and coverage

Business Logic Integration:

Align RLS policies with application business rules
Coordinate with application-level security measures
Document policy relationships and dependencies
Test edge cases and boundary conditions

Operational Security

Access Control:

Limit who can create and modify RLS policies
Implement approval workflows for policy changes
Monitor policy modifications and access pattern changes
Maintain separation between policy administration and data access

Compliance and Auditing:

Document all RLS policies and their business purposes
Maintain audit trails of policy changes and access patterns
Regular compliance reviews of data access controls
Test disaster recovery procedures for RLS-protected data

Row-Level Security provides powerful, flexible data protection capabilities that integrate seamlessly with your application architecture while maintaining high performance and operational simplicity.

Row-Level Security

On this page