Row-Level Security

Row-Level Security (RLS) allows for the management of access to individual data rows at the table level, enabling detailed access isolation. This feature lets administrators set specific rules that determine which table rows users can view or modify.

Scenarios

Includes commands: ALL, SELECT, INSERT, UPDATE, DELETE.
Policies can be assigned to multiple roles.
Enforced on both commands and roles.
Commands like TRUNCATE and REFERENCES do not apply to row-level permissions due to their unique operational methods.

Syntax

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_name: Unique within its table, but can be shared across different tables. Multiple policies on a table are treated as an OR relationship.
USING: Verifies existing records for SELECT, INSERT, UPDATE, DELETE, or ALL actions.
WITH CHECK: Checks records for addition or modification, applicable to SELECT, INSERT, UPDATE, DELETE, or ALL operations.

Manage policies through the CREATE POLICY, ALTER POLICY, and DROP POLICY commands.

-- Query the current policies
SELECT * FROM pg_policies WHERE tablename='table_name';

Managing Row-Level Security

Enable RLS on the target table

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

Disable RLS

ALTER TABLE table_name DISABLE ROW LEVEL SECURITY;

Examples

-- Order data is grouped by tenant
CREATE TABLE order_details (order_id text, tenant_name text, order_detail text);
 
ALTER TABLE order_details ENABLE ROW LEVEL SECURITY;
 
-- Control access to only your own tenant's data
CREATE POLICY order_multi_tenant ON order_details
USING (tenant_name = current_user);
 
-- Create multiple strategies, applied in combination. The relationship between multiple strategies is OR:
CREATE POLICY order_update ON order_details
FOR UPDATE
USING (tenant_name = current_user)
WITH CHECK (order_id IS NOT NULL);

tacnode=> \d order_details
             Table "public.order_details"
    Column    | Type | Collation | Nullable | Default
--------------+------+-----------+----------+---------
 order_id     | text |           |          |
 tenant_name  | text |           |          |
 order_detail | text |           |          |
Policies:
    POLICY "order_multi_tenant"
      USING ((tenant_name = current_user))
    POLICY "order_update" FOR UPDATE
      USING ((tenant_name = current_user))
      WITH CHECK ((order_id IS NOT NULL))

To learn more about RLS, visit Row Security Policies.

Row-Level Security

Scenarios

Syntax

Managing Row-Level Security

Examples

On this page