0028/0029 silently report zero findings for projects using a custom PostgREST request role

[bkereskenyi]

Summary

0029_authenticated_security_definer_function_executable (and 0028 for anon) decide exposure with a hardcoded role literal:

pg_catalog.has_function_privilege('authenticated', p.oid, 'EXECUTE')

For a project that runs signed-in requests as a custom PostgREST role — a supported pattern, via the custom_access_token auth hook rewriting the role claim — this predicate is false for anything granted only to that custom role. The lint then reports zero findings, which is indistinguishable from "correctly remediated".

Why this is worse than an ordinary false negative

The failure mode is silent and it looks like success. A project that adopts a custom request role will typically:

1. create the role and make it a member of authenticated (so it inherits existing table/RLS grants),
2. move EXECUTE grants from authenticated onto it.

Step 2 is exactly what drops these lints to 0. Role membership doesn't rescue the check, because it only runs one way: the custom role inherits from authenticated, so has_function_privilege('authenticated', …) stays false for a grant held solely by the custom role.

The result is a security category that reads 0 because the project changed how it authenticates, at the moment its exposure surface stopped being visible. In a project I work on, this category reported 0 while several hundred SECURITY DEFINER functions were executable by the actual request role — including internal helpers that were explicitly revoked from PUBLIC, anon, authenticated and were believed closed.

Suggested fix

Enumerate the roles the API can actually assume rather than naming one. authenticator is the login role PostgREST connects as, and it can only SET ROLE into roles it is a member of, so that set is discoverable:

-- roles the API can switch into, instead of the literal 'authenticated'
WITH request_roles AS (
    SELECT r.rolname
    FROM pg_catalog.pg_auth_members m
    JOIN pg_catalog.pg_roles r         ON r.oid = m.roleid
    JOIN pg_catalog.pg_roles grantee   ON grantee.oid = m.member
    WHERE grantee.rolname = 'authenticator'
      AND r.rolname <> 'anon'          -- 0028 covers the anonymous side
)
SELECT ...
FROM pg_catalog.pg_proc p
CROSS JOIN request_roles rr
WHERE p.prosecdef
  AND pg_catalog.has_function_privilege(rr.rolname, p.oid, 'EXECUTE')
...

That is a superset of today's behaviour: authenticated is itself a member of authenticator, so every finding the lint reports now is still reported. It only adds the custom roles that are currently invisible.

Two details worth deciding on:

• Deduplication — a function granted to two request roles should surface once. EXISTS (SELECT 1 FROM request_roles …) avoids the cross-join fan-out if the detail isn't wanted.
• Naming the role in the message — the remediation text currently says "the authenticated role". Reporting which role actually holds the grant would make the finding actionable for these projects.

Scope

Both function lints are affected symmetrically. The other lints that name a role literal (has_table_privilege/has_column_privilege/policy roles) are not affected in the same way provided the project leaves table and column grants on authenticated and lets the custom role inherit them — which is the natural setup. Worth confirming, but the function lints look like the load-bearing case.

Happy to open a PR if the approach seems right.