5. Row-level security as the only real boundary

Context

The application queries PostgreSQL directly through PostgREST. There is no backend service of this project's own sitting between the browser and the database, which means there is no place to put a traditional authorization layer in application code.

Access control therefore had to live either in the frontend, which anyone can skip, or in the database.

Decision

All authorization is enforced by PostgreSQL row-level security policies. Frontend checks exist only to make the interface coherent, and are explicitly not treated as protection.

Consequences

Skipping the application changes nothing. A caller querying the API directly with their own token is subject to the same policies. This is the property that makes the architecture viable at all.

The rules are not where most developers look for them. Someone reading a route component cannot see what protects the data; the rules are in a migration file. This is why the documentation has a dedicated permissions section, and why a reviewer's first question on a data change is which policy covers it.

Missing policies fail silently. A query with no permitting policy returns an empty result rather than an error. It looks like absent data, which is easy to misdiagnose as a frontend bug, and it looks correct when tested by an administrator whose policy does permit it.

Policies have two halves and both matter. USING controls which rows may be touched; WITH CHECK controls what a row may become. This project has one storage policy missing its WITH CHECK, which allows a member to move their own file into another member's folder. The consequence is limited, but it is a direct result of the omission.

Row-level security does not filter everything. TRUNCATE is not subject to policies. On tables where the stack's default grants were never revoked, a policy is the only protection, and it cannot stop that command. Newer tables revoke grants explicitly and force row-level security, which is the pattern to follow.

Functions that bypass policies need care. Anything declared SECURITY DEFINER runs with elevated privileges, must set an empty search path with fully qualified names, and must have execute revoked from PUBLIC, because creating a function grants it to everyone by default. This project has been caught by that default more than once.

Assessment

The decision holds. It puts the rules in the one place that cannot be bypassed, and a security review found no remotely exploitable weakness in them.

The cost is real but manageable: the rules are less discoverable than application code, and mistakes are quiet rather than loud. Both are addressed by documentation and review rather than by changing the approach.