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

The routine pins its path​

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 does not apply. Whether the pinned path is itself closed, with nothing in it somebody else can write to and pg_temp as its last entry, is what SEC.PRIV.ROUTINE_DEFINER_UNSAFE_PATH answers.

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, pg_temp
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;