Skip to main content

SEC.PRIV.ROUTINE_DEFINER — A routine that runs as its owner, correctly

  • Category: security
  • Severity: low
  • Level: 0
  • Confidence: deterministic
  • Downtime class: none — the finding is about a routine's privileges, not about a statement
  • Stability: stable
  • Suites: audit
  • Applies to: PostgreSQL 18

This is the construction done right​

The routine runs with its owner's privileges (SECURITY DEFINER) and pins its own search_path, so the substitution attack SEC.PRIV.ROUTINE_DEFINER_MUTABLE_PATH reports is closed. If you deliberately built a narrow, audited path for a low-privilege role, this is what it looks like, and you have done it correctly.

So why is it a finding at all?​

Because of what it does to every other judgment about your database.

EXECUTE on this routine is not an ordinary privilege — it is a bounded loan of the owner's rights. A reviewer reading a grant list cannot see that from the grant. Neither can the least-privilege check: a runtime role holding nothing but EXECUTE looks minimal, and may reach considerably further than any of its other grants allow.

This entry exists so that nobody is surprised. When somebody asks what can the application actually do, these routines are part of the answer, and a report listing only the grants would be missing the part that matters most.

What to do with it​

Read the grant list on the routine once and confirm it is the one you meant:

SELECT grantee, privilege_type
FROM information_schema.routine_privileges
WHERE specific_schema = 'app' AND routine_name = 'grant_access';

If it is, you are done. If your project has decided it already knows about its definer routines, raise security.min_severity above low in config/sqlens.php — that is a decision made once rather than a finding dismissed on every run.

Bad​

There is no bad way to write this routine — the rule reports a correct arrangement, and the one that is wrong is SEC.PRIV.ROUTINE_DEFINER_MUTABLE_PATH beside it. What can still be wrong is the grant, which is the thing this entry asks you to read:

-- The routine is right. This is what makes it reach further than anybody meant it to.
GRANT EXECUTE ON FUNCTION app.grant_access(bigint) TO PUBLIC;

PUBLIC is the implicit membership of every role in the database, including every role created from now on — so the loan of the owner's rights is handed to accounts nobody has made yet.

Good​

CREATE FUNCTION app.grant_access(uid bigint) RETURNS void
    LANGUAGE sql
    SECURITY DEFINER
    SET search_path = pg_catalog
AS $$
    INSERT INTO public.memberships (user_id, granted_at) VALUES (uid, pg_catalog.now());
$$;

-- and the grant that goes with it: one role, one routine, nothing wider
GRANT EXECUTE ON FUNCTION app.grant_access(bigint) TO app_runtime;