Adding membership to a static site with Supabase: RLS, keys and consent

A static site is fast, cheap and easy to maintain: there is no server-side code, and pages come straight from a content delivery network. But one day users say "I want to see my progress on my other device too", and suddenly you need accounts, sessions and per-user data. That does not mean moving the whole site onto an application server. A backend service such as Supabase makes authentication and a PostgreSQL database safely usable straight from the browser.

In this article we describe the path we took when adding optional membership to the static website of the SendenSana app: email and password sign-in, row level security (RLS) policies, which key may live in the browser, consent-gated data sync and account deletion. Membership is not required on that site; the practices are open to everyone, and an account exists only for people who want to sync across devices. That choice shaped many of the design decisions below.

Two keys, two worlds

A Supabase project has two kinds of keys. The publishable key (formerly called the anon key) is designed to live in the browser; anyone can see it and that is fine, because on its own it grants access to no data. Access is decided by the RLS policies in the database. The secret key (the service role key) bypasses RLS entirely; it can read and write everything in the database.

The rule that follows is simple: the secret key never goes into client code, a static file, the repository or any configuration that reaches the browser. We keep it only in a server-side admin tool, as an environment variable. It is not even present in the static site's build script. The browser side starts like this:

import { createClient } from "./vendor/supabase.js";

const SUPABASE_URL = "https://PROJECT.supabase.co";
const PUBLISHABLE_KEY = "sb_publishable_...";   // fine in the browser; the secret key NEVER goes here

export const supabase = createClient(SUPABASE_URL, PUBLISHABLE_KEY);

Instead of pulling the library from a CDN, we serve a pinned local copy. That way the Content Security Policy does not need to allow an external script source, and there is no risk of the library changing underneath us without notice.

Sign-up, sign-in and email confirmation

With supabase-js v2, sign-up and sign-in take a few lines. On sign-up, emailRedirectTo decides where the link in the confirmation email sends the user back to; that address must be on the allowed redirect list in the Supabase dashboard.

export async function signUp(email, password) {
  const { data, error } = await supabase.auth.signUp({
    email,
    password,
    options: { emailRedirectTo: "https://example.com/account/" },
  });
  if (error) throw error;
  return data.user;   // no session until the email is confirmed (when confirmation is on)
}

export async function signIn(email, password) {
  const { data, error } = await supabase.auth.signInWithPassword({ email, password });
  if (error) throw error;
  return data.session;
}

In practice the part of email confirmation that causes the most trouble is the email itself. Supabase's built-in email sender has a low hourly limit and is meant for testing; before opening sign-up to real users you need to configure an SMTP provider that sends from your own domain. For that, setting up SPF, DKIM and DMARC records in the domain's DNS matters if you want the emails to stay out of spam folders. Confirmation and password reset templates also need to be prepared separately in each language the site supports.

Tables and row level security

The real security is built in the database. Each member has a profile row and a state row that is synced across devices. Both reference auth.users and disappear automatically when the account is deleted. Once RLS is enabled, no row without a matching policy is visible to anyone; the policies then allow only the signed-in owner to see their own row.

create table public.profile (
  id uuid primary key references auth.users (id) on delete cascade,
  created_at timestamptz not null default now(),
  consent_version text,
  sync_consent boolean not null default false
);

create table public.member_state (
  user_id uuid primary key references auth.users (id) on delete cascade,
  data jsonb not null default '{}'::jsonb,
  updated_at timestamptz not null default now(),
  constraint data_size check (octet_length(data::text) <= 262144)
);

alter table public.profile enable row level security;
alter table public.member_state enable row level security;

create policy "profile: read own row" on public.profile
  for select to authenticated using ((select auth.uid()) = id);
create policy "profile: update own row" on public.profile
  for update to authenticated
  using ((select auth.uid()) = id) with check ((select auth.uid()) = id);

create policy "state: read own row" on public.member_state
  for select to authenticated using ((select auth.uid()) = user_id);
create policy "state: insert own row" on public.member_state
  for insert to authenticated with check ((select auth.uid()) = user_id);
create policy "state: update own row" on public.member_state
  for update to authenticated
  using ((select auth.uid()) = user_id) with check ((select auth.uid()) = user_id);

A few details are worth pointing out. The update policy has both using and with check: the first controls which rows can be updated, the second makes sure the row still belongs to the user after the update. If with check is omitted, PostgreSQL applies the using expression to the new row as well; writing both explicitly still documents the intent and prepares for cases where the two conditions must differ. Wrapping auth.uid() in a select lets PostgreSQL evaluate it once instead of once per row. The size limit is enforced in the database with a check constraint; a limit in the client alone is easy to bypass and is not enough.

Rather than trusting the client to create the profile row, we create it with a trigger that runs when a new user is inserted into auth.users:

create function public.handle_new_member()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
begin
  insert into public.profile (id) values (new.id);
  return new;
end;
$$;

create trigger on_member_created
  after insert on auth.users
  for each row execute function public.handle_new_member();

Consent version and consent-gated sync

The data the app keeps in the user's browser falls into two groups. General records, such as completed practices or favourites, are synced for every member. More private records, such as personal notes, go to the server only if the user has given separate, explicit consent for that. Because the consent text can change, the profile row stores which version the user agreed to; when the text changes in substance, we raise the version and ask again.

const GENERAL = ["completed_practices", "favourites"];
const PRIVATE = ["notes"];

function payloadFor(local, privateConsent) {
  const keys = privateConsent ? [...GENERAL, ...PRIVATE] : GENERAL;
  const out = {};
  for (const k of keys) if (k in local) out[k] = local[k];
  return out;
}

export async function sync(local, privateConsent) {
  const { data: { user } } = await supabase.auth.getUser();
  if (!user) return null;
  const { error } = await supabase
    .from("member_state")
    .upsert({ user_id: user.id, data: payloadFor(local, privateConsent) },
            { onConflict: "user_id" });
  if (error) throw error;
  const { data, error: readError } = await supabase
    .from("member_state").select("data, updated_at").eq("user_id", user.id).maybeSingle();
  if (readError) throw readError;
  return data;
}

Because upsert rewrites the whole column, when a user withdraws private consent the next sync automatically removes the private records from the server copy. For upsert to work the table needs both an insert and an update policy; forgetting one shows up as a quiet failure on the first save or on later updates. A real app also needs a conflict rule: lists are merged, and for single-value fields the device wins. It is also good practice to use a trigger that writes the server time rather than trusting the client clock for the update timestamp.

Account deletion and the security definer risk

Being able to delete one's own account is an expected right from a data protection point of view. But the role used in the browser has no permission to delete from auth.users. So we leave the deletion to a function that runs with the privileges of the role that defined it:

create function public.delete_my_account()
returns void
language plpgsql
security definer
set search_path = ''
as $$
begin
  if auth.uid() is null then
    raise exception 'not signed in';
  end if;
  delete from auth.users where id = auth.uid();
end;
$$;

revoke execute on function public.delete_my_account() from public, anon;
grant execute on function public.delete_my_account() to authenticated;

security definer functions are powerful and need care for that reason. We follow three rules. First, search_path is pinned to an empty value and every object inside the function is schema-qualified; otherwise someone could place their own table or function on the search path and redirect the function to their code. Second, the function only acts on the signed-in user's identity and never takes a user id as a parameter. Third, execute permission is explicitly revoked from the anonymous role. Supabase's security advisor warns about functions like this; we accepted the warning knowing why it is there. Because the tables are linked with on delete cascade, the profile and state rows go away when the user is deleted. On the client the call is one line: await supabase.rpc("delete_my_account"). The other route Supabase documents is to delete the user on the server, inside an Edge Function that holds the secret key, with auth.admin.deleteUser; that route does not touch the auth schema directly. If the user owns objects in Storage, the deletion can be blocked because those objects reference the user; delete the files first.

Testing the policies

Rather than assuming RLS works, measure it. The first test is to read the table with only the publishable key and no session. If RLS is on and there is no policy for the anonymous role, the result is an empty list; if you have also revoked table privileges from the anonymous role, you get a permission error. In both cases not a single row should come back.

curl -s "$SUPABASE_URL/rest/v1/member_state?select=*" \
  -H "apikey: $PUBLISHABLE_KEY"
# expected: [] or a permission error; never a data row

The second test uses two separate test accounts: sign in as account A and try to read and update account B's row. The read should come back empty and the update should affect no rows. The third test checks that the related rows are really gone after an account is deleted. We run these tests only with test accounts created for the purpose, never with real user data.

Operational notes

You can start on Supabase's own free plan, but you should know its limits: free projects that are not used for a while can be paused, and sign-in does not work until the project is restored. On a site without steady traffic this needs monitoring. Choosing a database region close to your users and suitable for your data protection obligations matters too; for users in Turkey, transferring data abroad under KVKK needs its own assessment and must be reflected in the privacy notice. We recommend getting legal advice on this. In the notice shown to users, a short plain summary first and the details after it noticeably improves readability. You can see our own site's approach on our privacy page (Turkish text).

Conclusion

Adding membership to a static site does not require changing the whole architecture. Only the publishable key in the browser, RLS switched on for every table, policies that tie each user to their own row, a profile created by a trigger, versioned consent and a carefully written deletion function together make a safe and simple setup. What remains is to test every policy with real requests and to try email delivery before real users arrive. You can see the app where we use this approach on the SendenSana page.