Lock down your data access.

Think in operations

A few moving parts are involved here. The points below cover each of them, so you can see the full scope before deciding where to start.

• SELECT: which rows may this role read?
• INSERT: which values may this role create, and who owns them?
• UPDATE: which existing rows may change, and what new values are allowed?
• DELETE: which rows may be removed?
• Functions: which trusted server-side operations may execute?

A secure ownership pattern

Store the owner ID on the row and compare it with the authenticated user ID inside the policy. For privileged roles, use a server-side role lookup function rather than a client flag or local storage value.

create policy "Users read their own rows"
on public.items
for select
to authenticated
using (owner_id = auth.uid());

1. Confirm the request is using the intended auth session.
2. Check table grants for the calling role.
3. Read the exact policy expression and test the owner and non-owner cases.
4. Check insert validation separately from select visibility.
5. Inspect function execution permissions and search path for database functions.

Test the negative path

A secure policy is proven when the wrong user cannot read, edit or delete another user's record. Include those cases in QA before adding a wider role.

Warning signs of a weak policy

This comes down to a handful of concrete details. Work through the list below and flag the ones you have not covered yet.

• A policy references a column the client can set on insert, such as a role or owner_id field with no server-side check.
• A policy passes because it was tested only with the owner account.
• A function used inside a policy runs with elevated privileges and no search path set.
• The same policy expression handles both read and write with no distinction between them.
• A table has RLS enabled but no policies, which silently blocks every request instead of failing loudly during testing.

Diagnostic sequence for a permission error

Here is the process broken into clear steps. Take them one at a time and check each off before continuing.

1. Reproduce the exact request with the same user session, not an admin session.
2. Read the returned error code and message rather than guessing.
3. List every policy on the table for the operation involved.
4. Evaluate the policy expression manually against the row and the user ID.
5. Check whether a trigger or function called during the request has its own separate permission requirement.