Device-Secret RPC Anonymous Execute Automation: A Revoke That Looked Complete and Was Not

Writing "revoke all ... from public" against a newly created function looks, and in many database configurations is, a complete way to close public access to it. The pattern breaks down specifically when a project's own configuration separately hands out access by a different mechanism — a named grant to a specific role, made automatically at creation time, that exists independently of whatever the PUBLIC pseudo-role is or is not allowed to do. Revoking from PUBLIC in that situation removes exactly what it says it removes, correctly — and leaves the other mechanism's grant completely untouched, with nothing in the revoke statement itself signaling that a second door exists.

Centriu Guardian's fleet feature is backed by database functions that the frontend and the device agent both call directly. Every one of these functions, when first created, was accompanied by a revoke statement removing execute privilege from the PUBLIC pseudo-role — the conventional way to prevent a function from being callable by just anyone.

That revoke did not achieve what it looked like it achieved. This particular database project has its own default access-control configuration that separately grants execute privilege, by name, to the anonymous and authenticated roles on every newly created function — a distinct mechanism from PUBLIC's own privilege, and one that a PUBLIC-only revoke does not touch at all. The two privilege paths coexist independently; closing one says nothing about the other.

The gap was found the same way its table-level counterpart was found (covered on a companion page): by applying the fleet migrations to a real database and directly measuring the resulting privilege, rather than by re-reading the migration SQL. The measurement asked, for each function, whether the anonymous role specifically could execute it — and the answer was yes for all 17 Guardian database functions, including the one that mints a brand-new device secret. That function is reachable directly through the database's own REST-style API layer, meaning the standing grant was not an abstract privilege sitting unused — it was a live, callable endpoint requiring nothing but the public, deliberately non-secret anonymous key to invoke.

The fix rewrites the access grant for all 17 functions from the ground up: revoke execute explicitly from PUBLIC, the anonymous role, AND the authenticated role on every one of them — closing both privilege paths at once rather than assuming one revoke handles both — and then grant execute back deliberately, function by function, in three bands matched to how each is actually meant to be used. Six device-facing functions (device enrollment, heartbeat, alerts, incidents, benchmarks, and passport updates) are granted to both the anonymous and authenticated roles, because Guardian's own device agent has no user session at all — it authenticates using the anonymous key purely as transport, presenting a device-specific token that each function verifies internally before doing anything privileged. Six console-facing functions are granted to the authenticated role only, since whoever operates the fleet console is logged in, and each of those functions independently checks organization membership regardless. Four remaining functions are granted to neither role, because they exist only to be called from inside other functions that already run with elevated privilege, and have no legitimate reason to be reachable as a public database endpoint at all.

One further, documented complication surfaced while writing this exact fix: a fifth function, used to check organization membership, looked like it belonged in the "internal only" group, since nothing calls it directly from outside the database. It does not belong there — that function is referenced inside the row-level security policy expression on all 13 of Guardian's row-secured tables, and a policy expression is evaluated using the privilege of whoever is running the query that triggered it, not the privilege of whoever owns the table. Denying that function execute privilege to the authenticated role broke every logged-in user's ability to read anything from any of those 13 tables — caught immediately by the database itself refusing every query, not by a code reviewer reading the migration. The function was granted execute privilege after all, safely: it takes no identity as an argument, runs with elevated privilege internally, and answers only whether the CURRENT session's own authenticated user belongs to a given organization — nothing a user could not already learn by querying tables they already have legitimate access to. "Nothing calls this function from outside the database" and "nothing needs this function's execute grant" turned out to be two different claims, and only the first one was true.

Before the fix, a caller holding nothing but Centriu Guardian's public anonymous key could call the device-secret-minting function directly through the database's own REST-style API — the standard "revoke all ... from public" written in the migration that created the function had no effect on this caller, because the anonymous role's execute privilege came from a separate, named default grant the revoke never addressed. After the fix, the identical call is rejected outright: the function's execute privilege was revoked from the anonymous role explicitly, and access is limited to the specific, deliberately scoped bands of functions the anonymous role is actually meant to reach.

Every one of Centriu Guardian's 17 fleet database functions now has execute privilege scoped deliberately across three bands matched to real calling patterns, with both the PUBLIC pseudo-role privilege path and the named-role default-grant path closed explicitly — rather than relying on a single revoke statement that, in this project's own configuration, only ever closed one of the two.

This automation governs the internal execute-privilege configuration underneath Centriu Guardian's own fleet database functions — it does not change what any function actually does when called by an authorized caller, does not add a customer-facing setting, and is specific to a database configuration where named-role default grants exist independently of PUBLIC's own privilege; a project without that specific default-grant mechanism would not have needed this exact correction, though the broader lesson — confirm what a revoke statement actually removes in your own database's configuration — applies generally.

Writing the conventional "revoke all ... from public" is a reasonable default and, in many database configurations, is genuinely sufficient — the risk is specific to any project where a separate mechanism (here, a default access-control list granting execute to named roles automatically) coexists with PUBLIC's own privilege, because nothing about the conventional revoke statement discloses whether that second mechanism is even present. Measuring actual resulting privilege directly against a real, migrated database — asking the database itself, for each role and each function, whether execute is actually possible — closes that blind spot regardless of how many independent privilege mechanisms a given project happens to have layered on top of each other.