Securing Supabase and Postgres with row-level security

By Marco Lopes · September 14, 2023 · Cloud & DevOps

When we build collaborative products, authorization becomes complicated quickly. A workspace owner can manage content, an editor can change it, and a viewer should only read it. Meanwhile, background workers, integrations, and internal tools also need database access.

At Kordspace, our remote software, AI, Web3, hardware, and design work often involves connecting several kinds of clients to shared infrastructure. We prefer Google Cloud, while also working with AWS and Azure. Across those environments, we want access rules that survive changes to the application layer.

Postgres row-level security, or RLS, gives us a useful foundation. With Supabase, it becomes especially important because the generated API can expose database operations directly to browser and mobile clients. Here is how we approach it without confusing authentication with authorization.

Put authorization beside the data

Authentication tells us who is making a request. Authorization determines what that identity may do. A valid login should never imply access to every row in a table.

Application filters are helpful, but they are not a security boundary. A query such as select * from projects where workspace_id = ... only protects data if every caller always supplies the correct condition. One forgotten filter can expose another workspace’s records.

RLS moves that boundary into Postgres. Policies attach conditions to reads and writes, and the database applies them to queries made by roles subject to RLS. This protects ordinary API requests even when application code forgets a tenant filter.

We still need table privileges, input validation, and application checks. RLS complements those controls; it does not replace them.

Understand the roles before writing policies

In a typical Supabase request, an unauthenticated client uses the anon database role. After authentication, requests generally run as authenticated, with verified JWT claims available to database functions. We use auth.uid() to obtain the requesting user’s UUID.

The project’s anon key is designed for public clients. Its safety depends on the permissions and policies behind it. The service-role key is different: it can authorize requests that bypass RLS and must remain on trusted infrastructure.

Postgres also has important exceptions. Superusers and roles with BYPASSRLS bypass row policies. Table owners normally bypass them too, although FORCE ROW LEVEL SECURITY can subject owners to policies. It does not constrain superusers or BYPASSRLS roles.

This means a successful query in a privileged SQL console proves very little about end-user access. We need to test as the actual application roles.

Model workspace membership explicitly

For a collaborative application, we usually prefer membership records over embedding every permission in a token. A membership table gives us one place to check workspace access and editing rights.

The following example uses two tables. Workspace identifiers are provisioned by a trusted backend; in a fuller schema, we would also reference a workspace registry with foreign keys.

create table public.workspace_members (
  workspace_id uuid not null,
  user_id uuid not null references auth.users(id),
  access_level text not null
    check (access_level in ('owner', 'editor', 'viewer')),
  primary key (workspace_id, user_id)
);

create table public.projects (
  id uuid primary key default gen_random_uuid(),
  workspace_id uuid not null,
  name text not null,
  body text not null default ''
);

create index projects_workspace_id_idx
  on public.projects (workspace_id);

alter table public.workspace_members
  enable row level security;

alter table public.projects
  enable row level security;

Once RLS is enabled, access through a role subject to RLS is denied unless an applicable policy allows it. We enable it in the migration that creates the table rather than relying on a later dashboard step.

We also configure privileges explicitly. These statements establish the intended access for this example; existing deployments should audit inherited grants and other database roles too.

revoke all on public.workspace_members, public.projects
  from anon, authenticated;

grant select on public.workspace_members
  to authenticated;

grant select, insert, update, delete on public.projects
  to authenticated;

Our API roles also need schema access, as configured in a standard Supabase project. Table grants allow operations; RLS decides which rows those operations may touch. Neither layer substitutes for the other.

Start with narrow read policies

We let users read their own membership records, but not create or change them. Otherwise, a user could grant themselves access to another workspace or promote themselves to owner.

create policy "Read own memberships"
  on public.workspace_members
  for select
  to authenticated
  using (user_id = auth.uid());

create policy "Read workspace projects"
  on public.projects
  for select
  to authenticated
  using (
    exists (
      select 1
      from public.workspace_members as m
      where m.workspace_id = projects.workspace_id
        and m.user_id = auth.uid()
    )
  );

The membership subquery is itself subject to RLS. That is intentional: the requesting user can see their own matching membership, which is sufficient to establish access.

We avoid a membership policy that queries the same membership table to determine whether someone is an administrator. That pattern can produce recursive policy evaluation. For this initial design, membership administration stays behind a trusted server endpoint with explicit authorization checks.

This also makes the distinction between visibility and authority clear: reading a membership does not grant permission to edit it.

Treat writes as separate decisions

For reads and deletes, USING determines which existing rows are available. For inserts, WITH CHECK determines whether a proposed row is acceptable. Updates need both: permission to target the old row and permission to produce the new one.

We allow owners and editors to create, modify, and delete projects, while viewers retain read access.

create policy "Create workspace projects"
  on public.projects
  for insert
  to authenticated
  with check (
    exists (
      select 1 from public.workspace_members as m
      where m.workspace_id = projects.workspace_id
        and m.user_id = auth.uid()
        and m.access_level in ('owner', 'editor')
    )
  );

create policy "Update workspace projects"
  on public.projects
  for update
  to authenticated
  using (
    exists (
      select 1 from public.workspace_members as m
      where m.workspace_id = projects.workspace_id
        and m.user_id = auth.uid()
        and m.access_level in ('owner', 'editor')
    )
  )
  with check (
    exists (
      select 1 from public.workspace_members as m
      where m.workspace_id = projects.workspace_id
        and m.user_id = auth.uid()
        and m.access_level in ('owner', 'editor')
    )
  );

create policy "Delete workspace projects"
  on public.projects
  for delete
  to authenticated
  using (
    exists (
      select 1 from public.workspace_members as m
      where m.workspace_id = projects.workspace_id
        and m.user_id = auth.uid()
        and m.access_level in ('owner', 'editor')
    )
  );

Our read policy also supports the visibility required for normal update workflows. The update check prevents someone from moving a project into a workspace where they lack editing rights.

It does not make workspace_id immutable. Someone with editing rights in two workspaces could move a project between them. If that is unacceptable, we enforce immutability separately, using column privileges or a trigger. RLS is row authorization, not a complete business-rule engine.

We also review policies together. Ordinary permissive policies combine with OR, so adding a broad policy can silently weaken a narrow one. More policies do not automatically mean more security.

Keep privileged paths small

Some operations genuinely need elevated access: provisioning workspaces, accepting invitations, or processing administrative jobs. We isolate these paths and keep service credentials out of browsers, mobile bundles, logs, and source control.

On Google Cloud, we can store secrets in Secret Manager and give only the relevant workload access through IAM. AWS and Azure offer equivalent building blocks. Cloud IAM protects the credential’s delivery; it does not replace Postgres authorization once a privileged connection exists.

Before a server uses privileged credentials, we verify the caller and authorize the requested action. We never trust a submitted user_id or workspace_id simply because the request reached our backend.

We apply similar caution to SECURITY DEFINER functions. They run with the function owner’s privileges and can cross boundaries the caller could not cross directly. When necessary, we use a fixed, safe search_path, schema-qualified references, narrowly scoped ownership, and explicit execution grants.

Watch JWTs, views, and adjacent services

We do not base authorization on user-editable metadata. In Supabase, user metadata can be changed by the user, making it unsuitable for trusted role assignments. Server-controlled application metadata is a better source for claims, but JWT contents remain a snapshot until refreshed or replaced.

Checking membership in the database lets subsequent queries see committed membership changes without waiting for a token refresh, subject to transaction visibility. That is valuable when removing access.

Views deserve separate review. Traditional Postgres views can evaluate underlying access using the view owner’s permissions, potentially defeating the intended RLS boundary. Where supported, PostgreSQL 15’s security_invoker view option lets underlying access checks follow the invoking user. Otherwise, we keep sensitive views outside exposed schemas or revoke client access.

We also audit RPC functions and storage policies. Protecting projects does not automatically protect uploaded files, another table, or a function that returns the same information through a privileged path.

Test denial as carefully as success

Our test fixtures include two workspaces, an editor, a viewer, and an unrelated user. We check allowed reads and writes, but also cross-workspace requests, forged workspace identifiers, membership changes, and unauthenticated calls.

For database-level tests, a privileged test connection can temporarily assume the API role and supply claims matching a fixture user:

begin;
set local role authenticated;
select set_config(
  'request.jwt.claims',
  '{"sub":"11111111-1111-4111-8111-111111111111","role":"authenticated"}',
  true
);

select * from public.projects;
rollback;

We replace that UUID with an existing fixture identity. This tests policy behavior, not JWT verification, so we also run integration tests through Supabase’s API using real sessions.

Denied reads commonly return no rows rather than an error. An unauthorized update may likewise affect zero rows, while a failing WITH CHECK can produce an error. Our assertions inspect affected rows and final database state instead of relying only on HTTP status codes.

For performance, we inspect representative query plans under realistic roles. The membership primary key supports the workspace-and-user lookup, and the project workspace index supports tenant filtering. We add indexes based on actual query patterns rather than weakening policies to make a slow query disappear.

Our pre-release checklist

• Enable RLS and define grants in version-controlled migrations.
• Test anonymous, authenticated, cross-workspace, and privileged paths.
• Cover both existing-row access and proposed-row checks.
• Keep membership administration and service credentials server-side.
• Review views, functions, storage, and overlapping policies.
• Verify indexes, denial behavior, and access removal in staging.

RLS works best when we treat it as part of the product’s architecture, not a final security patch. At Kordspace, we combine database boundaries with application authorization, cloud secret management, and repeatable tests. To build or strengthen a collaborative product with us, start a project, or explore our related services to see how we approach software, infrastructure, and design.