In PostgreSQL, whose privileges and row-level security policies apply when a support agent queries a view, and what changes when the view sets security_invoker?
answer
- two checks: privileges and policies
- the view owner by default
- table owners skip row security
- functions follow the caller anyway
basics
~20 sBy default a PostgreSQL view checks base-table privileges and applies row-level security as the view's owner. With security_invoker = true it uses the querying user's privileges and policies instead; how functions in the view run is unaffected.
solid answer
~50 sBy default, access to a view's base tables is checked against the **view owner**, so a support agent needs only `SELECT` on the view and `USAGE` on its schema; that is the classic restricted view hiding sensitive columns. Row-level security also follows the owner: the owner's policies apply, and because table owners bypass row security unless the table has `FORCE ROW LEVEL SECURITY`, a view owned by the table owner, or by a superuser, can silently return every row. Setting `security_invoker = true` (in `CREATE VIEW ... WITH (...)` or `ALTER VIEW ... SET`) makes PostgreSQL check the **caller's** privileges on the base tables and apply the caller's policies, as if they queried the tables directly. Neither setting changes functions: `CURRENT_USER` in a view is the caller, and a `SECURITY DEFINER` function still runs as its own owner.
code
sql · 12 linesCREATE VIEW support.customer_contact AS
SELECT c.customer_id, c.full_name, c.email, c.region
FROM crm.customers c; -- card_last4, birth_date left out
GRANT USAGE ON SCHEMA support TO support_agent;
GRANT SELECT ON support.customer_contact TO support_agent;
-- Default: checked as the view owner; no grant on crm.customers needed.
ALTER VIEW support.customer_contact SET (security_invoker = true);
-- Now checked as the caller, and the caller's RLS policies apply:
GRANT SELECT (customer_id, full_name, email, region)
ON crm.customers TO support_agent;go deeper
Recall that a PostgreSQL view checks base-table access as its owner by default, so readers can be granted the view alone.
Explain how security_invoker moves both privilege checks and row-level security policies from the view owner to the caller, while functions are unaffected.
Spot the trap where a view owned by the table owner or a superuser silently bypasses row-level security, and choose the mode per boundary.
Decide where the access boundary lives, view definition or RLS policy, so a team does not maintain two inconsistent sources of truth.
## Two questions a view has to answer When a role reads a PostgreSQL view, two separate checks run against the base tables behind it: - **Privileges**: may this read touch these tables and columns at all? - **Row-level security (RLS)**: if a base table has RLS enabled, whose policies decide which rows come back? In both cases the answer is either the **view owner** or the **invoking user**, and the `security_invoker` view option picks between them. The caller always needs a privilege on the view itself, such as `SELECT`. ## The default: owner's rights Without `security_invoker`, PostgreSQL checks base-table access as the role that owns the view. That is what makes a restricted view work: - the support agent gets `SELECT` on `support.customer_contact` and `USAGE` on schema `support`; - the agent gets **no** grant on `crm.customers` and does not even need `USAGE` on schema `crm`, because schema lookup happened when the view was created; - the view's select list leaves out the sensitive columns, so the agent can see contact data but never card or birth-date columns. ## The row-level security trap RLS follows the same rule by default: the **view owner's** policies are applied, and any relations those policies touch are accessed with the owner's permissions. Combine that with how RLS treats owners: | Who owns the view | What an agent sees through it | |---|---| | A superuser or a role with `BYPASSRLS` | Every row; RLS is bypassed | | The table's owner, table without `FORCE ROW LEVEL SECURITY` | Every row; owners bypass RLS | | A separate role subject to the policies | Rows that role's policies allow, the same for every reader | | Anyone, with `security_invoker = true` | Rows the agent's own policies allow | So a team that writes per-agent policies on `crm.customers` and then exposes the table through a default view, created by the table owner, has quietly switched those policies off for every view reader. The policies themselves, `FORCE ROW LEVEL SECURITY` and `BYPASSRLS` belong to the row-security topic; the point here is that the view decides whose policies run. ## What security_invoker = true changes Set with `CREATE VIEW ... WITH (security_invoker = true)` or `ALTER VIEW ... SET (security_invoker = true)`: 1. Base-table privileges are checked against the **caller**, so the agent now needs `SELECT` on the referenced columns of `crm.customers` as well as on the view. Column-level grants keep the sensitive columns closed. 2. The **caller's** RLS policies apply, as if the base tables were named directly in the query. 3. Writes through an auto-updatable view need the caller, not the owner, to hold `INSERT`, `UPDATE` or `DELETE` on the base table. 4. If a security-invoker view sits underneath another view, it is still checked as the current user, even when the outer view has no `security_invoker`. 5. Schema lookup is unchanged: the caller still needs `USAGE` only on the view's own schema. ## What neither setting changes - **Functions.** A function in the view runs as the caller, or as its owner if it is `SECURITY DEFINER`, regardless of the view option. `CURRENT_USER` inside a view therefore returns the querying role, which is why a filter such as `WHERE assigned_agent = current_user` works in an owner-rights view. - **An owner-rights view is not a `SECURITY DEFINER` function.** Only table access is delegated; function execution is not. - **Leaky predicates.** A caller can add a `WHERE` clause with a function that sees rows the view's own filter would remove. Preventing that is the job of `security_barrier` and `LEAKPROOF`, owned by the row-security topic. ## Choosing for a support-agent view - Use the **default** when the view itself is the security boundary: fixed columns, a fixed filter, and agents with no direct table access. - Use **`security_invoker = true`** when per-user RLS policies are the boundary and the view is convenience: it keeps one source of truth for who sees which rows. - In either case, check who owns the view; an owner who bypasses RLS turns a default view into an unfiltered window.
- With security_invoker = true on a PostgreSQL view, does the caller need USAGE on the schemas the view query references?No. Names in the view query are looked up when the view is created or replaced, so the reader needs `USAGE` only on the schema that holds the view. What changes is table and column privileges and RLS policies, which are now checked as the caller.
- A default PostgreSQL view filters on assigned_agent = current_user; whose name does current_user return?The querying role's. Functions and special values in a view are evaluated as if called directly from the outer query, so `current_user` is the caller even though base-table access is checked as the owner. That makes per-agent filtering possible in an owner-rights view, though leaky user predicates still need `security_barrier`.
- A migration reruns CREATE OR REPLACE VIEW on a PostgreSQL security_invoker view without its WITH clause; what happens to the option?It is dropped. `CREATE OR REPLACE VIEW` replaces the view's options list with the one in the new statement, even when that list is empty, so the view silently returns to owner's rights and the owner's RLS policies. Restate `WITH (security_invoker = true)` in every replacement, or reapply it with `ALTER VIEW ... SET`.
- A view without security_invoker behaves like a SECURITY DEFINER function.
- Row-level security always filters by the role running the query, even through a view.
- With security_invoker = true the reader no longer needs a grant on the view itself.
- Reading a security_invoker view needs USAGE on every schema the view query references.
- An owner-rights view is safe against any WHERE clause a caller adds.
- CREATE OR REPLACE VIEW keeps security_invoker even when the WITH clause is omitted.