TRUNCATE CASCADE wiped every role and permission

Routine work on a database leaves everyone locked out of the system. Users, staff, sites and credentials are all still there, and every request returns a 403 naming a permission the person should have had. Authorisation has been deleted.

That is what a reset command did to a development database in this project. No customer data was involved, but the same command run against a live system would have meant an outage until someone worked out that the permission tables were empty and rebuilt them.

What was actually going on

The command that resets tenant data truncates the organisations table with CASCADE. That is a normal thing to have in a seeding script: clear the tenants and everything hanging off them goes too. It also emptied the roles table, including the four built-in roles that belong to no tenant, and with them every permission grant and every role assignment.

Roles come in two kinds. Four are built into the product (an administrator, a manager, a viewer and a staff-portal role) and tenants may define their own. Both live in one table, told apart by a nullable tenant column. That column is a foreign key to the organisations table, which it has to be, or a tenant role could name a tenant that does not exist. So the roles table is a child of the organisations table, and the truncate empties all of it, including the rows whose tenant column is null and which reference nothing.

DELETE with cascading keys removes the related rows. TRUNCATE ... CASCADE empties the related tables, every row in them. It does not scan rows, which is its whole point, so it cannot find a subset. A table with a nullable foreign key to something you truncate loses its unrelated rows too, and here those were the entire authorisation model.

What we changed

Two things remain in the codebase. The seeding script re-inserts the system roles and their permission grants after the truncate, with a one-sentence comment saying why: cascading the truncate to the roles table wipes the system roles. And a verification script that runs after the schema is applied counts the permission grants on the administrator role, a tripwire for this failure, because that number can silently become zero.

Both are compensating controls, correct in their place, and neither is a fix. They work as long as everyone uses the script. The schema options discussed were structural: separate system roles from tenant roles, since one is product reference data and the other customer data, or keep one table and truncate the tenant tables by name instead of relying on cascade.

What it did not fix

The tables have not been separated. The reset script is correct and the tripwire is in place, and the risk that remains is a person running the truncate by hand, where the re-seed does not happen and the tripwire does not run. I have not found a way to prevent that other than not having the privilege, and on a development database everybody has it.

The wider habit is that a destructive command relying on cascade has a blast radius defined by the schema, and the schema changes without the command being reviewed. Every foreign key added later widens it.

The nullable tenant column has a second cost. A unique index on the tenant column and the key together does not work, because nulls are distinct in a unique index, so any number of system roles could share a key. The schema uses two partial unique indexes with mutually exclusive conditions. That is complexity bought by the same decision.

What to ask your own team or supplier

• Which tables hold product reference data, such as roles and permissions, next to customer data in the same table?
• Does any reset or cleanup script use TRUNCATE ... CASCADE, and does anyone know what it reaches?
• Is there a check after a reset that counts the permission grants on an administrator role?
• Who can run destructive commands by hand against a shared or development database?
• When someone works out a trap like this, does the explanation get left in the code where the next person will meet it?

Where this ends up

One table holding two kinds of row, separated by a nullable tenant column, is a tenancy decision, and tenancy is expensive to reverse once a system holds data. It is the kind of data-model decision we settle first in custom software development, before the parts that are cheap to change later.