Fleet Table TRUNCATE Default-Privilege Automation: Flawless Row Policies, Emptiable Anyway

Row-level security is a powerful, precise tool for one specific job: deciding which ROWS a query is allowed to touch, for the commands it is configured to intercept. It says nothing, by design, about a database-wide default that hands out a broader set of table-level commands automatically the moment a table is created — and TRUNCATE is exactly that kind of command: a schema-level operation that empties a table in one step without evaluating a single row-level policy, because PostgreSQL never asks row-level security to weigh in on it in the first place. A team can review every policy on a table, find each one correct, and still miss that the table itself was never protected against a command row-level security was never in the loop for.

Centriu Guardian's fleet feature relies on 14 database tables — device records, alerts, incidents, enrollment codes, advocate relationships, warranty data, and more — each governed by row-level security policies scoping access to the organization a given authenticated user belongs to. Those policies were reviewed and found correct.

What the review did not surface, because nothing in the migration SQL for any individual table mentioned it, was the database project's own default privilege configuration: every newly created table in the public schema automatically receives a broad table-level privilege grant — commonly summarized as "all standard commands" — for both the anonymous and authenticated roles. That default includes TRUNCATE, and TRUNCATE is a schema-level operation in PostgreSQL, evaluated entirely outside of row-level security. A table can have policies restricting every SELECT, INSERT, UPDATE, and DELETE to exactly the right organization's own rows, and still be fully, instantly emptied by a single TRUNCATE statement that never triggers a single one of those policies.

The gap was found not by re-reading the SQL, but by applying the fleet migrations to a real database and measuring the resulting privilege set directly — the same discipline the source commit applied to a second, unrelated function-level gap (covered on a companion page). Confirmed: all 14 fleet tables carried this default privilege, meaning any caller holding only the anonymous key — public by definition, present in every browser bundle Guardian ships — could truncate any of them.

The fix revokes the full default privilege grant from both the anonymous and authenticated roles on every one of the 14 tables, then grants back, table by table, only the specific narrower command that table's own row-level policy is actually written to authorize — SELECT alone on some, SELECT plus UPDATE on others, and so on, matching each table's real access pattern rather than leaving a blanket grant in place. The anonymous role receives no direct table privilege on any of the 14 tables: Guardian's own device agent, which does use the anonymous key as its transport (it authenticates via a device token checked inside a function, not via a user session), talks to dedicated database functions for every write it needs to make — never to a table directly. Two of the fourteen tables, which have row-level security enabled but intentionally carry no policies at all, receive no grant whatsoever and are readable only through a dedicated function built for that specific, narrow purpose — a table with row-level security on and no policy, left with any standing grant, would have been an accidental wide-open door.

Before the fix, a caller equipped with nothing more than Centriu Guardian's public anonymous key — the same key present in the browser bundle of any page the agent or console ships — could issue a TRUNCATE against any of the 14 fleet tables and have it succeed instantly, without a single row-level policy ever being consulted, regardless of how correctly those policies scoped ordinary reads and writes. After the fix, the identical attempt is rejected outright: the anonymous role holds no privilege on the table at all, and the only path the device agent has for writing fleet data is through a dedicated function that checks a device-specific token internally before doing anything.

Every one of Centriu Guardian's 14 fleet tables now carries table-level privilege that matches exactly what its own row-level security policy authorizes, with the anonymous role holding no standing privilege on any of them — closing a TRUNCATE path that existed entirely outside of, and invisible to, a correct row-level policy review.

This automation governs the internal table-privilege configuration underneath Centriu Guardian's own fleet feature — it does not change how row-level security itself scopes reads and writes (that was already correct), does not add a customer-facing setting, and is specific to this project's own default-privilege configuration; a database configured with a narrower schema-level default from the start would not have needed this specific correction, though the underlying lesson about TRUNCATE bypassing row-level security applies broadly.

Letting a database's default privilege setting apply automatically to every new table is convenient and, for many projects, never causes a problem — but the convenience trades away visibility: nothing in any individual table's own migration SQL discloses what the default actually grants, so a reviewer checking that table's policies has no local signal that a broader, schema-level command is also silently in play. Setting table privilege explicitly, matched to what each table's own policies are meant to authorize, costs a few extra lines of SQL per table and removes the gap entirely — the privilege a table has becomes something a reader of that table's own migration can see, not something they have to already know to go check elsewhere.