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

Open
  • Is something wrong?
#185 0 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

@sidgaikwad is already working on this.

Since Sep 15, 2026.

  • #191 by @sidgaikwad — open
  • #199 by @mattmuller — open

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
68/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
postgresql, sql
Domain
databases, security

Research direction

Start with the 0028 and 0029 function lints and inspect their hardcoded role privilege predicates. Compare the suggested request-role query with the existing behavior, then verify that custom PostgREST roles produce findings without duplicate functions and that existing authenticated-role findings remain covered.

Written by the indexing model from the issue text.

Description

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.

Dominant language
PLpgSQL
Stars
284
Forks
110
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from supabase/splinter

All issues in supabase/splinter

Similar issues

More Databases issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.