Database Administrator | SQL & PostgreSQL Developer | Passionate about query optimization, AWS enthusiast, and skilled in Power BI and Excel-based data analysis.
Introduction
When building multi-tenant applications or securing sensitive data, PostgreSQL’s Row-Level Security (RLS) is a game-changer. Unlike traditional table-level privileges, RLS lets you control access row by row — meaning two users querying the same table may see different results based on policies.
In this guide, we’ll walk through:
- Preparing users and schemas
- Enabling/disabling RLS
- Creating & automating policies
- Checking existing policies
- Handling views with RLS
Preparing Users and Schemas
First, let’s create a couple of users:
CREATE USER xyz_user1 WITH PASSWORD 'password1';
CREATE USER xyz_user2 WITH PASSWORD 'password2';
Grant schema and table privileges:
GRANT USAGE ON SCHEMA xyz_schema TO xyz_user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA xyz_schema TO xyz_user1;
GRANT USAGE, CREATE ON SCHEMA xyz_service_schema TO xyz_service_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA xyz_service_schema TO xyz_service_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA xyz_service_schema
GRANT ALL PRIVILEGES ON TABLES TO xyz_service_user;This ensures that the users can connect and interact with the right schemas.
Enabling & Disabling RLS on Tables
RLS must be enabled per table:
ALTER TABLE xyz_app.xyz_table1 ENABLE ROW LEVEL SECURITY;
ALTER TABLE xyz_app.xyz_table2 DISABLE ROW LEVEL SECURITY;
ALTER TABLE xyz_app.xyz_table3 DISABLE ROW LEVEL SECURITY;Tip: Always double-check which tables need RLS enabled to avoid accidental exposure.
Creating & Dropping RLS Policies
Let’s create a policy for xyz_table1 so users can only see rows matching their company ID.
-- Remove the old policy
DROP POLICY IF EXISTS xyz_access_policy ON xyz_app.xyz_table1;
-- Create the new policy
CREATE POLICY xyz_access_policy
ON xyz_app.xyz_table1
FOR SELECT
USING (company_id = current_setting('app.current_company_id')::integer);Enforce RLS strictly:
ALTER TABLE xyz_app.xyz_table1 FORCE ROW LEVEL SECURITY;Automating Policy Creation
In real-world projects, many tables share a company_id column. Instead of writing policies manually, automate with a PL/pgSQL script:
DO $$
DECLARE
r RECORD;
policy_sql TEXT;
BEGIN
FOR r IN
SELECT c.table_schema, c.table_name, c.data_type
FROM information_schema.columns c
JOIN information_schema.tables t
ON c.table_schema = t.table_schema AND c.table_name = t.table_name
WHERE c.column_name = 'company_id'
AND c.table_schema = 'xyz_app'
AND t.table_type = 'BASE TABLE'
LOOP
EXECUTE format('ALTER TABLE %I.%I ENABLE ROW LEVEL SECURITY;', r.table_schema, r.table_name);
EXECUTE format('DROP POLICY IF EXISTS xyz_access_policy ON %I.%I;', r.table_schema, r.table_name);
IF r.data_type = 'integer' THEN
policy_sql := format($f$
CREATE POLICY xyz_access_policy
ON %I.%I
USING (company_id = current_setting('app.current_company_id', true)::int)
WITH CHECK (company_id = current_setting('app.current_company_id', true)::int);
$f$, r.table_schema, r.table_name);
END IF;
EXECUTE policy_sql;
EXECUTE format('ALTER TABLE %I.%I FORCE ROW LEVEL SECURITY;', r.table_schema, r.table_name);
END LOOP;
END $$;Now every table with a company_id gets an RLS policy automatically.
Checking Existing Policies
To see what’s already configured:
SELECT *
FROM pg_policies
WHERE schemaname = 'xyz_app'
AND tablename = 'xyz_table1';
SELECT relname AS table,
relrowsecurity AS rls_enabled,
relforcerowsecurity AS force_rls
FROM pg_class
WHERE relname = 'xyz_table1';Disabling & Removing RLS
Need to roll back?
DO $$
BEGIN
EXECUTE 'ALTER TABLE xyz_app.xyz_table1 DISABLE ROW LEVEL SECURITY;';
EXECUTE 'DROP POLICY IF EXISTS xyz_access_policy ON xyz_app.xyz_table1;';
EXECUTE 'ALTER TABLE xyz_app.xyz_table1 NO FORCE ROW LEVEL SECURITY;';
RAISE NOTICE 'RLS policy removed from xyz_app.xyz_table1';
END $$;Tips for Views + RLS
- Use SECURITY INVOKER / SECURITY DEFINER depending on whether the view should run with the caller’s or owner’s privileges.
- Always enable FORCE ROW LEVEL SECURITY on base tables when exposing them via views.
Summary
- Step 1: Create users and grant schema/table privileges.
- Step 2: Enable RLS on sensitive tables.
- Step 3: Create policies to enforce row-level rules.
- Step 4: Automate with scripts for multi-tenant apps.
- Step 5: Verify policies using pg_policies and pg_class.
- Step 6: Use FORCE RLS with views for extra safety.