Row_level_security

Chapter 47: Row-Level Security

Fine-Grained Access Control

---

47.1 Understanding RLS

Row-Level Security (RLS) restricts which rows users can see or modify.

-- Enable RLS on table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create policy for users to see their own orders
CREATE POLICY own_orders_select ON orders
    FOR SELECT
    USING (user_id = current_user_id());

-- Create policy for users to insert their own orders
CREATE POLICY own_orders_insert ON orders
    FOR INSERT
    WITH CHECK (user_id = current_user_id());

-- Create policy for users to update their own orders
CREATE POLICY own_orders_update ON orders
    FOR UPDATE
    USING (user_id = current_user_id());

-- Create policy for users to delete their own orders
CREATE POLICY own_orders_delete ON orders
    FOR DELETE
    USING (user_id = current_user_id());

-- Test as user
SET session authorization john;
SELECT * FROM orders;  -- Only sees john's orders

---

47.2 Advanced RLS

-- Policy based on role
CREATE POLICY admin_all ON orders
    FOR ALL
    TO admin_role
    USING (true)
    WITH CHECK (true);

-- Policy with multiple conditions
CREATE POLICY regional_sales ON orders
    FOR SELECT
    USING (
        user_id = current_user_id()
        OR region = current_user_region()
    );

-- Function to get user's region
CREATE FUNCTION current_user_region() RETURNS VARCHAR AS $$
BEGIN
    RETURN (SELECT region FROM users WHERE user_id = current_user_id());
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Policy using subquery
CREATE POLICY team_orders ON orders
    FOR SELECT
    USING (
        user_id IN (
            SELECT user_id FROM team_members
            WHERE team_id IN (
                SELECT team_id FROM team_members
                WHERE user_id = current_user_id()
            )
        )
    );

---

47.3 Managing RLS

-- List all policies
SELECT * FROM pg_policies WHERE tablename = 'orders';

-- Check if RLS is enabled
SELECT relname, relrowsecurity
FROM pg_class
WHERE relname = 'orders';

-- Bypass RLS for superusers
-- (Superusers bypass RLS by default)

-- Disable RLS
ALTER TABLE orders DISABLE ROW LEVEL SECURITY;

-- Drop policy
DROP POLICY own_orders_select ON orders;

---

Summary

Command	Purpose
ENABLE ROW LEVEL SECURITY	Turn on RLS
CREATE POLICY	Define access rules
USING	Filter condition
WITH CHECK	Insert/update condition

---

Next: Chapter 48: Data Encryption