The essentials
Quick reference
One focused task per row. Jump to the related section for complete, working examples.
| Use | Syntax | Examples |
|---|---|---|
| Enable row security | ALTER TABLE app.invoice ENABLE ROW LEVEL SECURITY; | View examples |
| Apply RLS to the owner | ALTER TABLE app.invoice FORCE ROW LEVEL SECURITY; | View examples |
| Grant baseline privileges | GRANT
SELECT, INSERT,
UPDATE, DELETE
ON app.invoice TO app_runtime; | View examples |
| Filter visible rows | CREATE POLICY invoice_select
ON app.invoice FOR
SELECT TO app_runtime
USING (tenant_id = app.current_tenant_id()); | View examples |
| Validate inserted rows | CREATE POLICY invoice_insert
ON app.invoice FOR INSERT TO app_runtime
WITH CHECK (tenant_id = app.current_tenant_id()); | View examples |
| Constrain updates | CREATE POLICY inv_update
ON app.invoice FOR
UPDATE TO app_runtime
USING (tenant_id = app.current_tenant_id())
WITH CHECK (tenant_id = app.current_tenant_id()); | View examples |
| Add an allow path | CREATE POLICY support_read
ON app.invoice AS PERMISSIVE FOR
SELECT TO support_agent
USING (assigned_to = current_user); | View examples |
| Add a mandatory condition | CREATE POLICY no_legal_hold_delete
ON app.invoice AS RESTRICTIVE FOR DELETE TO app_runtime
USING (NOT legal_hold); | View examples |
| Remove RLS bypass | ALTER ROLE app_runtime NOBYPASSRLS; | View examples |
| Error instead of filtering | SET LOCAL row_security = off; | View examples |
| Scope tenant context | SET LOCAL app.tenant_id = '42'; | View examples |
| Read optional context | SELECT current_setting('app.tenant_id', true); | View examples |
| Inspect effective definitions | SELECT *
FROM pg_policies
WHERE schemaname = 'app' AND tablename = 'invoice'; | View examples |
| Check RLS for this caller | SELECT row_security_active('app.invoice'::regclass); | View examples |
Row-level security (RLS) is a defense boundary, not a substitute for table privileges, trusted identity propagation, or adversarial testing. Policies silently filter rows for ordinary roles, so a plausible query result can still conceal a broken authorization path. Start from least privilege and default deny, separate visibility from write checks, keep policy expressions simple, and explicitly test table owners, privileged roles, pooled connections, maintenance jobs, and every application role.
Step by step
Detailed examples
Layer RLS beneath ordinary least-privilege grants
The table owner must enable RLS, and policies take effect only after it is enabled. If RLS is enabled but no applicable policy exists, ordinary row access is denied by default. SQL privileges are still checked first: a policy cannot give a role SELECT or UPDATE that GRANT did not provide. FORCE ROW LEVEL SECURITY also subjects the owner during ordinary access, which makes owner-run tests more realistic, but superusers and BYPASSRLS roles remain outside the boundary. TRUNCATE and REFERENCES are not governed by RLS.
ALTER TABLE app.invoice OWNER TO app_owner;
ALTER TABLE app.invoice ENABLE ROW LEVEL SECURITY;
ALTER TABLE app.invoice FORCE ROW LEVEL SECURITY;
REVOKE ALL ON app.invoice FROM PUBLIC;
GRANT SELECT, INSERT, UPDATE, DELETE ON app.invoice TO app_runtime;Separate existing-row visibility from new-row validity
USING filters existing rows considered by SELECT, UPDATE, and DELETE. WITH CHECK validates rows proposed by INSERT or UPDATE and raises an error when the condition is not true. Command-specific policies make this distinction reviewable. UPDATE normally also needs a matching SELECT policy so PostgreSQL can locate and return candidate rows. Avoid relying on an omitted WITH CHECK unless intentionally accepting PostgreSQL's command-specific fallback from USING.
CREATE POLICY invoice_select ON app.invoice
FOR SELECT TO app_runtime
USING (tenant_id = app.current_tenant_id());
CREATE POLICY invoice_insert ON app.invoice
FOR INSERT TO app_runtime
WITH CHECK (tenant_id = app.current_tenant_id());
CREATE POLICY invoice_update ON app.invoice
FOR UPDATE TO app_runtime
USING (tenant_id = app.current_tenant_id())
WITH CHECK (tenant_id = app.current_tenant_id());
CREATE POLICY invoice_delete ON app.invoice
FOR DELETE TO app_runtime
USING (tenant_id = app.current_tenant_id());Reason about the complete policy Boolean expression
For a command and role, applicable permissive policies are combined with OR; applicable restrictive policies are combined with AND. A row must satisfy at least one permissive policy and every restrictive policy. Restrictive policies therefore cannot grant access on their own. Role membership can make more policies applicable, so review inherited memberships and the final combined behavior rather than reading each policy in isolation.
CREATE POLICY tenant_delete ON app.invoice
AS PERMISSIVE FOR DELETE TO app_runtime
USING (tenant_id = app.current_tenant_id());
CREATE POLICY no_legal_hold_delete ON app.invoice
AS RESTRICTIVE FOR DELETE TO app_runtime
USING (NOT legal_hold);Keep runtime, maintenance, and owner identities distinct
Superusers and roles with BYPASSRLS always bypass policies. Table owners normally bypass them unless FORCE ROW LEVEL SECURITY is set. Do not run an application as a superuser, a BYPASSRLS role, or usually as the table owner. Reserve bypass capability for tightly controlled operations and audit its membership. Setting row_security to off is a diagnostic safeguard used by tools such as backups: it causes an error when filtering would occur, rather than bypassing a policy.
SELECT rolname, rolsuper, rolbypassrls
FROM pg_catalog.pg_roles
WHERE rolsuper OR rolbypassrls
ORDER BY rolname;
ALTER ROLE app_runtime NOSUPERUSER NOBYPASSRLS;Bind tenant identity inside every pooled transaction
A custom setting is only an identity transport; any caller allowed to issue arbitrary SQL can normally change it. Derive the tenant from authenticated server-side authorization, reject direct untrusted database access, and expose a carefully reviewed security-definer setter when stronger enforcement is required. With transaction pooling, begin a transaction and use SET LOCAL so the value cannot leak into the next request. Make a missing value fail closed, and schema-qualify functions used by policies.
CREATE FUNCTION app.current_tenant_id()
RETURNS bigint
LANGUAGE sql
STABLE
RETURN NULLIF(pg_catalog.current_setting('app.tenant_id', true), '')::bigint;
BEGIN;
SET LOCAL app.tenant_id = '42'; -- value supplied only after trusted authorization
SELECT invoice_id, total FROM app.invoice ORDER BY invoice_id;
COMMIT;Test denial, bypass, concurrency, and indirect leakage
Exercise every command as each real login role, including a missing tenant, a forged tenant, cross-tenant writes, role membership changes, and owner or maintenance sessions. Inspect pg_policies and row_security_active instead of assuming the session is protected. Unique, primary-key, and foreign-key checks bypass RLS for integrity and can reveal conflicts indirectly. Policies that query mutable tables can also introduce snapshot races; prefer expressions based on the current row, or design and lock authorization lookups deliberately. COPY FROM is not supported on RLS-enabled tables in PostgreSQL 18, so use validated INSERT paths or an isolated staging design.
SELECT policyname, permissive, roles, cmd, qual, with_check
FROM pg_catalog.pg_policies
WHERE schemaname = 'app' AND tablename = 'invoice'
ORDER BY policyname;
BEGIN;
SET LOCAL ROLE app_runtime;
SELECT current_user, row_security_active('app.invoice'::regclass);
-- Run positive and negative fixtures here, then discard the test transaction.
ROLLBACK;Sources and further reading
References
Authoritative documentation used to verify and expand this cheat sheet.
- PostgreSQL Global Development GroupPostgreSQL: Row Security Policiespostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: CREATE POLICYpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: ALTER TABLEpostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: Role Attributespostgresql.org
- PostgreSQL Global Development GroupPostgreSQL: System Information Functions and Operatorspostgresql.org
Help us improve
Found a typo or missing example?
Tell us what would make this cheat sheet clearer, more complete, or more useful.