When a stored routine is created with the SQL SECURITY DEFINER clause instead of SECURITY INVOKER, what changes at execution time, and how do you deploy such a routine safely?
answer
- definer = setuid, invoker = caller's rights
- pin the search path or fully qualify names
- revoke EXECUTE from PUBLIC
- own it with a minimal role, not a superuser
- MySQL defaults to DEFINER; PostgreSQL to INVOKER
basics
~20 sSECURITY INVOKER runs the body with the caller's privileges; SECURITY DEFINER runs it with the owner's. Definer routines let a low-privileged caller perform a narrow privileged action, but they are a privilege-escalation surface: pin the name-resolution search path, revoke PUBLIC execute, own them by a limited role, and validate every input.
solid answer
~60 s**INVOKER** (the usual default): the body is authorized as the calling user, so the caller still needs privileges on every table it touches. **DEFINER**: the body runs with the privileges of the routine's owner, so a caller who has only EXECUTE can perform actions they could never perform directly. That is the point — it is the database's answer to "let the app insert into `audit` but never read `salary`" — and it is also the danger. A definer routine is a `setuid` binary inside the database. Safe deployment: - **Pin name resolution.** Set an explicit schema search path on the routine, or fully qualify every object. Otherwise a caller who controls their own search path can create a table or function that shadows an unqualified name in the body and get it executed as the owner. - **Revoke EXECUTE from PUBLIC**, then grant to exactly the roles that need it — creation often grants PUBLIC by default. - **Own it by a purpose-built role**, never the superuser or the schema-owning admin. - **Treat all arguments as untrusted**; parameterize, never concatenate them into dynamic SQL. - Keep the body minimal — the whole body inherits the elevated rights.
code
sql · 14 linesCREATE FUNCTION app.record_login(p_user_id bigint)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = app, pg_catalog
AS $$
BEGIN
INSERT INTO app.login_audit(user_id, at) VALUES (p_user_id, clock_timestamp());
END;
$$;
ALTER FUNCTION app.record_login(bigint) OWNER TO app_audit_owner;
REVOKE EXECUTE ON FUNCTION app.record_login(bigint) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION app.record_login(bigint) TO app_runtime;go deeper
Recall the one-line distinction: definer runs with the owner's privileges, invoker with the caller's.
Add why definer exists — a narrow privileged API for a low-privileged caller — and that EXECUTE is then the only grant the caller needs.
Lead with the hardening checklist: pinned search path or fully qualified names, minimal owner role, revoke from PUBLIC, no dynamic SQL from inputs, minimal body.
Position it against alternatives — row-level security, security-barrier views, application-layer enforcement — and treat definer routines as a reviewed, versioned privileged surface with a dedicated owner role per capability.
## The two authorization modes Every SQL-invoked routine carries an authorization mode. The SQL standard spells it `SQL SECURITY INVOKER` and `SQL SECURITY DEFINER`. - **INVOKER**: while the body runs, the current privileges are the *caller's*. Every table read, every insert, every nested routine call is checked against the calling role. The routine is then just packaged SQL — it grants nothing extra. - **DEFINER**: while the body runs, privileges are those of the routine's **owner** (definer). A user with only EXECUTE on the routine can cause work that their own grants would forbid. Defaults differ and matter: PostgreSQL and the standard default to INVOKER; MySQL defaults to DEFINER with the creating user as definer, which is why MySQL routines and views are a recurring source of accidental escalation. ## Why definer routines exist They are the mechanism for building a *narrow privileged interface*. You revoke direct DML from the application role, expose a small set of definer routines, and now the application can only do exactly what those routines do. Typical uses: - append-only audit logging where the caller may insert but never update or delete; - returning an aggregate over a sensitive table without granting row access; - controlled maintenance actions such as rotating a partition or resetting a counter; - multi-tenant helpers that enforce a tenant filter the caller cannot bypass. The security argument is real: it moves the enforcement point from application code, which any client can bypass by connecting directly, into the database itself. ## The escalation surface A definer routine is a `setuid` program, and it inherits every classic `setuid` hazard. **Name-resolution hijacking.** If the body refers to `orders` rather than `sales.orders`, resolution happens against the *caller's* schema search path at execution time in engines that resolve dynamically. A hostile caller creates `myschema.orders`, puts it first on their path, calls the routine, and the owner's privileges are now operating on the attacker's object — or executing the attacker's function of the same name and signature. The fix is to attach a fixed search path to the routine itself (`SET search_path = ...` as a routine attribute) or to schema-qualify every single reference, including operators and functions where the engine resolves those by path too. **Dynamic SQL.** Concatenating an argument into a statement string inside a definer routine turns ordinary SQL injection into full privilege escalation. Use parameter binding; when the dynamic part is an identifier, run it through the engine's identifier-quoting function and validate it against an allowlist. **Over-broad ownership.** If the routine is owned by a superuser or by the role that owns the whole schema, its blast radius is everything. Create a dedicated, minimally privileged owner role per routine or per group of routines, and grant that role only the specific object privileges the bodies need. **PUBLIC execute.** In several engines, creating a routine grants EXECUTE to PUBLIC by default. A definer routine left that way is callable by every role in the database. Revoke from PUBLIC and grant explicitly; better, make that revoke part of the migration template so it cannot be forgotten. **Body sprawl.** Every statement in the body runs elevated, so a routine that started as "insert one audit row" and grew a debugging branch that reads arbitrary tables has quietly widened the interface. Keep definer routines short and single-purpose, and review changes to them as security changes. **Interaction with row-level security.** Where the engine supports row-level policies, running as the owner may bypass policies the caller was subject to — sometimes intended, often not. Check whether the owner is exempt from the relevant policies before relying on them for isolation. ## Reviewing one A checklist that finds most real problems: Who owns it, and what can that owner do? Is the search path pinned or every name qualified? Is any input concatenated into SQL? Who holds EXECUTE, and is PUBLIC among them? Does the body do more than its name implies? Does it call other routines, and do those inherit the elevated context? ## When not to use DEFINER If the caller already has the privileges the body needs, DEFINER buys nothing and only adds risk — use INVOKER. Likewise, if what you actually want is *row* filtering rather than *object* access, row-level security policies or a security-barrier view are usually the better-fitting mechanism, because they compose with ordinary queries instead of forcing every access through a routine.
- Concretely, how does an unpinned schema search path let a caller escalate through a SECURITY DEFINER routine?If the body references an object by an unqualified name, the engine resolves that name using the search path in effect at call time, which the caller controls. The caller creates an object with the same name and a matching signature in a schema they own, puts that schema first on their path, and calls the routine; the body then executes the attacker's table or function while carrying the owner's privileges. Pinning a search path on the routine, or fully qualifying every reference including functions and operators, removes the ambiguity.
- When is SECURITY INVOKER the right choice even for a routine that touches sensitive tables?Whenever the caller is already entitled to the data the body touches — then the routine is only packaging, and DEFINER would add an escalation surface for no gain. INVOKER also keeps auditing and row-level policies aligned with the real end user rather than a shared owner identity. Reach for DEFINER only when the whole point is to let a caller do something their own grants forbid.
SECURITY DEFINER is a setuid binary: it runs as its owner no matter who launches it, so everything it touches by an unqualified name is an attack surface.
saying these in an interview costs you the question
- Thinking SECURITY DEFINER controls who may call the routine rather than whose privileges the body uses.
- Leaving the search path unset and relying on unqualified object names inside a definer routine.
- Making a superuser or schema owner the definer "so it just works".
- Forgetting that creation may grant EXECUTE to PUBLIC by default.
- Assuming the mode defaults are the same across engines — MySQL defaults to DEFINER, PostgreSQL to INVOKER.
- Building dynamic SQL from arguments inside a definer routine.