Roles & RLS In Depth

PostgreSQL's access-control model rests on a single idea that trips up people coming from other databases: there is no separate concept of a "user" and a "group."

Both are the same underlying object, a role, and everything else in this section, from GRANT to row-level security, is built on top of that one unification.

Summary

Core Idea: A role is PostgreSQL's single principal type; whether it behaves like a "user" or a "group" depends only on its attributes and how other roles are granted to it.
Why It Matters: Object-level privileges (GRANT) and row-level security (RLS) answer two different questions, "can this role touch this table" and "which rows in that table can it see," and conflating them causes real security gaps.
Key Concepts: role, membership, inheritance, privilege, row-level security policy, FORCE ROW LEVEL SECURITY.
When to Use: Designing a least-privilege role hierarchy, building multi-tenant row isolation, or auditing why a query returns fewer rows than expected.
Limitations / Trade-offs: RLS adds a real per-row planning and execution cost, and table owners silently bypass every policy unless you explicitly force it.
Related Topics: multi-tenant patterns, connection pooling and session variables, migration-time privilege grants, security auditing.

Foundations

Older PostgreSQL documentation still refers to CREATE USER, but that command is only a convenience wrapper: it creates a role with the LOGIN attribute set, and nothing more.

A role without LOGIN behaves like what other systems call a "group," existing purely to hold privileges that other roles can inherit.

That single-object design means GRANT app_readers TO app_api and GRANT SELECT ON orders TO app_readers are the exact same kind of statement, just granting different things to a role.

A helpful analogy is a badge system in a building: a role is a badge, membership in another role is clipping a second badge onto your lanyard, and INHERIT decides whether the badge reader checks both badges automatically or only checks the one you explicitly present.

GRANT, REVOKE, and role membership together decide which objects a role is allowed to touch at all - a table, a schema, a function.

Row-level security is a separate, later layer that decides which rows inside an object a role is allowed to see, once it has already cleared the object-level check.

Mechanics & Interactions

Privilege checks and RLS checks happen at different points and answer different questions, which is the single most important distinction in this whole model.

An object-level privilege check happens once, when the planner confirms your role has SELECT, INSERT, UPDATE, or DELETE rights on the table at all.

A row-level security policy, once enabled, gets folded into the query plan as an implicit filter, and its predicate is evaluated per row rather than once per statement.

That means RLS is not a permission in the GRANT sense; it is closer to an automatically-injected WHERE clause the role cannot see or remove.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

USING controls which existing rows are visible for SELECT, UPDATE, and DELETE; WITH CHECK separately controls which rows a new or modified row is allowed to become, and omitting WITH CHECK on an UPDATE-relevant policy silently reuses the USING clause instead.

The table owner and any role with the BYPASSRLS attribute skip every policy by default, which is a deliberate design choice so migration and admin roles are not accidentally locked out of their own tables.

FORCE ROW LEVEL SECURITY exists specifically to close that gap when the owner role itself should still be subject to its own policies, which matters most when application code runs as the owning role.

Advanced Considerations & Applications

RLS policies commonly key off a session variable set once per connection, such as current_setting('app.tenant_id'), which quietly reintroduces the client/server session-state distinction covered elsewhere in this cookbook.

That dependency is exactly why RLS and connection pooling need deliberate coordination: a pooled connection reused across tenants without resetting that session variable can leak one tenant's rows into another tenant's request.

Performance is the other place RLS earns real scrutiny, because an unindexed column referenced in a policy's USING clause turns every query against that table into an implicit per-row filter with no index to prune against.

Indexing the columns your policies filter on, typically tenant_id, is not optional at scale; it is the difference between RLS costing nothing extra and RLS silently doubling query latency.

Default privileges (ALTER DEFAULT PRIVILEGES) solve a related but distinct problem: without them, every table a migration creates needs its GRANT statements repeated by hand, and it is easy to ship a new table with no grants at all, which fails safe for security but breaks the application at runtime.

Mechanism	Strength	Weakness	Best Fit
`GRANT` / `REVOKE` (object privileges)	Simple, well-understood, checked once per statement	All-or-nothing per object; cannot restrict to a subset of rows	Controlling which tables/schemas a role can touch at all
Row-level security policies	Fine-grained, enforced even against ad-hoc queries	Real per-row cost; invisible unless you know to look for it	Multi-tenant row isolation, tenant-scoped data
Application-layer filtering (`WHERE tenant_id = ?` in app code)	No database-side cost; simplest to reason about locally	Only as strong as every code path remembering the filter	Low-risk internal tools where a missed filter is not catastrophic

Common Misconceptions

"PostgreSQL has separate USER and ROLE concepts." CREATE USER is a thin wrapper around CREATE ROLE ... LOGIN; underneath, users and groups are the same object type.
"RLS policies are a kind of GRANT." GRANT decides whether a role can touch a table at all; RLS decides which rows of an already-allowed table it can see, and the two checks happen at different stages.
"Enabling RLS on a table automatically protects it from the owner." The table owner bypasses RLS by default; only FORCE ROW LEVEL SECURITY subjects the owner to its own policies.
"A policy with only USING also restricts what rows can be written." Without an explicit WITH CHECK, UPDATE and INSERT reuse the USING clause, which is often not the restriction you actually intended for writes.
"RLS is free once enabled." An unindexed policy predicate becomes an implicit per-row filter with no index to prune against, which can meaningfully slow down every query against that table.

FAQs

What's the actual difference between a "user" and a "role" in PostgreSQL?

There is none at the object level - CREATE USER just creates a role with the LOGIN attribute set, so "user" and "group" are both instances of the same ROLE object.

How does role membership relate to inheritance?

Membership (GRANT roleA TO roleB) makes roleB a member of roleA, and INHERIT (the default) means roleB automatically gets roleA's privileges without needing SET ROLE first.

What does GRANT actually control, compared to a row-level security policy?

GRANT controls whether a role can touch an object at all - a table, schema, or function; RLS controls which specific rows inside an already-permitted table the role can see or modify.

Do object privileges and RLS get checked at the same point in query execution?

No - object privileges are checked once per statement during planning, while an RLS policy's predicate is folded into the plan and evaluated per row.

Does the table owner have to follow the RLS policies on their own table?

Not by default - owners and roles with BYPASSRLS skip every policy unless the table has FORCE ROW LEVEL SECURITY set.

What's the difference between USING and WITH CHECK in a policy?

USING filters which existing rows are visible for reads, updates, and deletes; WITH CHECK separately governs which rows a write is allowed to result in, and it defaults to the USING expression if omitted.

Why does RLS commonly depend on a session variable like current_setting('app.tenant_id')?

Because the policy needs some per-connection value to compare against each row, and a session GUC set once at connection time is the standard way to pass that context into the policy expression.

Can connection pooling break RLS-based tenant isolation?

Yes, if a pooled connection is reused across tenants without resetting the session variable the policy depends on, which can leak one tenant's rows into another tenant's query.

Does enabling RLS slow down queries?

It can, especially if the column referenced in the policy is not indexed, since the predicate then runs as an unindexed per-row filter on every query against that table.

Why do default privileges (ALTER DEFAULT PRIVILEGES) matter for migrations?

Without them, every new table a migration creates starts with no grants, so an application role can fail at runtime on a table that was created successfully but never explicitly granted.

When is application-layer filtering a reasonable alternative to RLS?

For low-risk internal tools where a missed WHERE tenant_id = ? is not catastrophic; anywhere a missed filter would be a real security incident, RLS's server-enforced guarantee is worth its cost.

Is RLS enough on its own to make a multi-tenant table safe?

Only combined with correct session-variable handling and indexing - RLS enforces the policy logic correctly, but a leaked or unset session variable, or an unindexed predicate, undermines both its security and its performance.

Roles Basics - creating login and group roles
GRANT/REVOKE Patterns - object-level privilege mechanics
Row-Level Security - policy syntax and USING/WITH CHECK in detail
RLS Performance - indexing policy columns for speed
Bypass RLS & Superuser - when policies do not apply

Stack versions: This page is conceptual and not tied to a specific stack version; role and row-level security mechanics described here are consistent across current PostgreSQL major versions, including PostgreSQL 18.4 (stable 18, maintenance line 17).