Skip to content

Module 04 / Lesson 03

Verify organization permissions with Row-Level Security

Module 04 · Real-World ContextStep 03 of 04

Never trust a green checkmark or a loading spinner until you have verified the database write and security boundaries.

You click "Publish Update". A green toast notification pops up saying "Published successfully!" But when you open a private browser window, nothing has changed. The AI had simply written a fake timer (setTimeout) that showed a toast without actually persisting data to the database. Or worse: anyone on the internet could edit your company’s incidents because row-level security was missing.

Narrated lesson · 1:27
Read the narration transcript

Separate visible controls from data permissions

The status app hides Publish for a read-only member. That's useful because the screen tells them what they can do.

Now ask what the server and database are meant to enforce. A screenshot only tells you what the page displayed. Use a local synthetic fixture to verify the expected outcome.

Write the expected access for each role

A Harbor operator can publish a Harbor update. We expect one saved row and a visible published response.

A Harbor viewer can read the authorized view, but the rule denies publication. The saved data should stay exactly as it was.

A Northline operator has a different organization. The private Harbor workspace should stay outside their view.

An anonymous visitor reads the public status page. They should see the approved update. The internal note should be absent.

See where each layer makes its decision

The application gets the current identity from the trusted session.

It checks the organization's membership and the permission for the operation. Review what the product promised and which role is being tested.

Row-Level Security is how Postgres applies policy to rows for ordinary roles. Use those ordinary user sessions in the local test. A privileged key would check a different path.

Review the expected results and side effects

Start with the allowed case, so you know the feature works for its intended user.

Then check the denied case. A message alone cannot tell you whether the data changed. Verify the stored result and the public response before approving the boundary.

The everyday problem

The status app’s operator view shows a Publish button only when the user belongs to the right organization. That is good interface behavior. A reviewer asks a different question. Where does the application actually decide whether this user may publish or read a given update? If the only evidence is a screenshot, the most important rule is still unverified.

Name three people with synthetic accounts: an operator from Harbor Labs, a read-only member of Harbor Labs, and an operator from Northline. Also name the anonymous visitor. Write down what each person should see in the private workspace and what visitors should see on the public incident page. Start from what your product promises each person.

Why the agent got it wrong

The interface decides which controls to show. The server and database enforce who can see or change data. A model can implement one layer beautifully and leave the ownership rule vague in another. A login check establishes who is signed in. It doesn’t establish that the signed-in person belongs to the organization that owns the update, or that their role can publish.

Row-Level Security, or RLS, is a way for Postgres to use policies when an ordinary database role reads or changes rows. Start by checking that policy and permission assumptions are clear for the path you’re reviewing. A private internal note and a published status update often need different treatment. Document that difference before writing a broad rule.

The tool and technique

Use an access matrix. Each row pairs a role, an organization relationship, and an expected allowed or denied operation. Review the product decision first, then inspect whether the server path and database policies follow it. Keep the UI role hints because they help people navigate. Validate the relevant access boundary independently in the project’s approved local tests.

Use synthetic test accounts tied to explicit roles. Verify with the same kind of user session the ordinary application uses. A highly privileged development credential can bypass the policies you think you’re testing. Keep those credentials out of test fixtures that represent normal users.

Review permissions at the database and server

Row-Level Security means Postgres checks which records each database request may read or change. Review the membership condition and the server’s authorization decision together. A hidden button helps explain the interface, while membership rules protect the records.

Local seeded permissions report
Operator · member of same organization → can publish
Viewer · read-only member → denied; record unchanged
Operator · different organization → denied; record unchanged
Public visitor → approved update visible; draft hidden

Policy review: compare organization_members.user_id with auth.uid()
and require the membership to match the row’s organization_id.
For publication, the action additionally checks the operator’s role.

Review the evidence for all four roles and include the unchanged-record assertion for denied writes. The authorization walkthrough uses seeded accounts and synthetic results.

On screen in the status app

The first review note below is based on how the page looks. The second gives the reviewer an observable rule. A Harbor operator can publish to a Harbor incident. A Harbor viewer can read the authorized workspace view but cannot publish. A Northline operator gets no Harbor workspace access. A visitor sees only public updates. The intended read and write outcomes are visible without explaining how to exploit a broken implementation.

Open the local test report and compare the outcomes with that table. Positive checks tell you the feature is usable. Negative checks tell you a prohibited action is rejected. Check that a denied write leaves the stored update unchanged. A generic failure message alone doesn’t establish the database state.

docs/incident-access-matrix.md

Before

Viewers cannot publish because the Publish button is hidden. Looks safe in the preview.

After

Harbor operator → Harbor incident → publish permitted.
Harbor viewer → Harbor incident → publish denied; saved data unchanged.
Northline operator → Harbor workspace → private rows excluded.
Anonymous visitor → Harbor public status → published text only.

What to watch for

One passing role test covers one role and operation. Add a case whenever the feature introduces a new path to the same data, such as an export, a background job, or a public feed. Review the permissions of server-side helpers that use elevated roles. Read the official policy and role documentation for any behavior you rely on.

A denied read might intentionally appear as an empty result in one layer and a generic failure in another. Match the expectations to the application contract. Avoid baking a particular HTTP status into all tests unless the route promises it. The underlying expected data and side effects matter more than a single status number.

How you know it worked

Review the four outcomes in the example. Run the project’s approved local test suite with synthetic accounts. Check the returned view and whether the stored update changed. Verify that the anonymous public page shows the approved public update and never includes the internal text in that fixture.

Before approval, ask a teammate to explain the rule in their own words and show the matching test. If they can only point to a hidden button, you still need evidence from the server or database boundary. Keep this matrix for future changes to access or publishing.

References