RLS, Grants and Functions

Reachability, schema and object grants, RLS, column/API surface, and application workflows each matter. RLS never supplies missing grants, and grants never replace row restrictions where differently authorized users can reach the same exposed table.

• Grant only operations required by the actor; never broaden access to silence an error.
• Write policies separately for select, insert, update, and delete where behavior differs.
• For insert/update ownership rules, validate the resulting row with with check.
• Remember permissive policies combine with OR and can unintentionally widen access.
• Test using ordinary client roles, because owners and BYPASSRLS roles bypass policy behavior.

alter table public.documents enable row level security;

create policy "documents_select_own"
on public.documents for select to authenticated
using ((select auth.uid()) = owner_id);

create policy "documents_insert_own"
on public.documents for insert to authenticated
with check ((select auth.uid()) = owner_id);

create policy "documents_update_own"
on public.documents for update to authenticated
using ((select auth.uid()) = owner_id)
with check ((select auth.uid()) = owner_id);

Default to security-invoker functions. If elevated behavior is required, a security-definer function must have a safe search_path, schema-qualified references, revoked default execute privileges, narrow explicit grants, a non-exposed home when possible, and direct invocation tests.

PostgreSQL referential-integrity and uniqueness checks can reveal that a hidden row exists through constraint failures. Policies consulting membership or entitlement tables can also race with revocation. Sensitive designs must evaluate these cases and test required revocation semantics in hosted staging.