postgres row level security policies setup

TL;DR

Enable RLS with ALTER TABLE ... ENABLE ROW LEVEL SECURITY, then write policies per command (SELECT, INSERT, UPDATE, DELETE) using session state like current_setting. Table owners bypass RLS by default, so use FORCE ROW LEVEL SECURITY if owners must be restricted too. The number one mistake is enabling RLS with no policies, which blocks everything; the number two is forgetting the owner bypass. Test every role's view before going live.

The query

postgres row level security policies setup

Use this when

• you need multi-tenant isolation where each tenant sees only its rows
• different database roles must see different subsets of a table
• you enabled RLS and now every query returns zero rows

Not for

• column-level permissions, which are GRANT-based, not RLS
• filtering rows in application code, which RLS is meant to replace

Steps

1. Enable RLS on the table, then immediately create at least one policy. RLS with zero policies denies everything.

Expected output: The table has RLS enabled and at least one policy per command you use.

1. Write policies around a tenant identifier, typically from a session setting your app sets per connection.

Expected output: A policy like tenantid = currentsetting('app.tenant')::int exists.

1. Decide on the owner bypass: keep the default if the app owner needs full access, or FORCE ROW LEVEL SECURITY if not.

Expected output: You have a documented decision on owner behavior.

1. Test as each role: connect as the app user, the read-only user, and the owner, and confirm each sees exactly its rows.

Expected output: Every role's row set matches the intended policy.

1. Add a regression test that inserts a row as one tenant and confirms another tenant cannot see it.

Expected output: The test fails if a policy is ever dropped or weakened.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_TFV7p5PCnIXX5VNnDYIpQg