proacl IS NULL means PUBLIC EXECUTE, not no grants

An audit query that returns no rows looks the same whether there's nothing to find or the query is blind. We ran into the second kind while checking function privileges on our own database.

The default that reads as nothing

PostgreSQL grants EXECUTE on functions to PUBLIC by default. The trap is how that renders: a function nobody has explicitly granted anything on has proacl = NULL, and NULL doesn't mean "no grants." It means "the defaults apply," and for a function the default is EXECUTE to PUBLIC.

So the obvious audit query, the one that asks which functions have PUBLIC in their ACL, comes back empty.

-- Zero rows, even when every function is world-callable.
SELECT p.oid::regprocedure
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND EXISTS (
        SELECT 1
        FROM aclexplode(p.proacl) a
        WHERE a.grantee = 0                 -- 0 is PUBLIC
          AND a.privilege_type = 'EXECUTE'
      );

aclexplode(NULL) yields no rows, so every function still carrying the default ACL is invisible to it. The ones this query can see are the ones somebody already thought about. The ones it can't see are the ones nobody did.

Expanding the default before checking is what makes them visible. acldefault(type "char", ownerId oid) returns the built-in privileges that apply when an object's ACL entry is null, and 'f' is the type code for functions and procedures. It's one line:

-        FROM aclexplode(p.proacl) a        -- before
+        FROM aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) a        -- after

One COALESCE is the whole difference between a check and a placebo.

Two refinements turn that into something you'd run on a schedule. Exclude extension-owned functions with a NOT EXISTS against pg_depend on deptype = 'e', or the output fills with rows nobody can act on, which is how a check gets ignored. And add p.prosecdef to the select list, because that's the column that tells you which hits are the urgent ones.

In full, ready to run against your own database:

-- Every non-extension function PUBLIC can execute.
SELECT p.oid::regprocedure, p.prosecdef
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND NOT EXISTS (
        SELECT 1
        FROM pg_depend d
        WHERE d.classid = 'pg_proc'::regclass
          AND d.objid = p.oid
          AND d.deptype = 'e'               -- extension-owned
      )
  AND EXISTS (
        SELECT 1
        FROM aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) a
        WHERE a.grantee = 0
          AND a.privilege_type = 'EXECUTE'
      );

Any row with prosecdef true is the one to look at first.

Why they're urgent

A SECURITY DEFINER function runs with the owner's privileges rather than the caller's, which is the entire point of it. One that PUBLIC can execute isn't untidiness. It's a privilege escalation primitive, and the naive query is precisely the wrong instrument for finding it.

It's also the shape of bug that survives review indefinitely, because the verification step agrees with the mistake. Nobody has to be careless. The query is just answering a slightly different question than the one being asked.

What we did

The check covers all non-extension functions, not only the SECURITY DEFINER ones, so anything added in later work is covered without someone remembering to add it.

It ships with a control. The test creates a function with a default ACL and asserts the check fires on it, for the same reason we now write the test that proves a guard fires. A check that's quietly stopped working produces the same output as a database with nothing wrong.

This time the audit found nothing. The explicit REVOKE ALL ... FROM PUBLIC was already in place, so the state was correct before we looked. We added the standing check anyway, because on an earlier build of ours the same gap survived twenty functions. What was missing here was never the revocation. It was the thing that would tell us if the revocation ever stopped being true.

Verify against the live system

Not the source that produced it.

Reading the migrations would have handed us the same reassuring answer as the naive query, and for the same reason: both of them describe intent. Only the running catalog, with defaults expanded, describes what is. That gap matters more the less of the code you wrote by hand, and agent-written migrations are code you didn't write by hand.

Written by Synthetixis, an AI-native product studio. More on what most AI software gets wrong.