PostgreSQL Row-Level Security além do básico

Row-Level Security do PostgreSQL é a resposta pra fazer multi-tenant funcionar sem paranoia. Não precisa de filtro SQL em toda query, não precisa fazer tudo em aplicação, você coloca regra no banco e acabou.

O problema é que 90% dos tutoriais param em WHERE user_id = auth.uid(). Pronto. Funciona pra case simples. Mas quando você tá fazendo multi-tenant de verdade, com 50 clientes compartilhando tabelas, com policies complexas, com performance que importa, a história é outra.

Vou mostrar aqui o que ninguém fala. Setup real multi-tenant com tenant_id no JWT. Como usar SECURITY DEFINER pra policies deixarem de ser lentas. Como debugar quando RLS quebra. Como evitar deadlock. Código SQL real, pronto pra copiar.

O problema que RLS resolve

Imagina esse cenário: você tem um SaaS de agendamento. Clínica A e Clínica B usam seu app. Mesma tabela appointments no banco.

Sem RLS:

• Você precisa adicionar WHERE tenant_id = $1 em toda query
• Se esquecer em uma, data de Clínica A vaza pra Clínica B
• Quanto mais queries, mais chance de esquecer
• Pior: seu código JavaScript pode ter bug. RLS no banco não.

Com RLS:

• Você define: “esse tenant só vê dados dele”
• Banco garante isso em nivel de row, não importa que query é executada
• Aplicação não consegue vazar dado mesmo se tentar

Isso é segurança em profundidade. Aplicação é primeira linha, RLS é segunda linha.

Setup básico (que você acha em qualquer tutorial)

Eu pularia essa seção, mas preciso pra context depois.

Tabelas:

CREATE TABLE clinics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE appointments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  patient_name TEXT NOT NULL,
  scheduled_at TIMESTAMP NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE auth.users (
  id UUID PRIMARY KEY,
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  email TEXT UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

Policy básica (RLS 101):

ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users can see their clinic's appointments" ON appointments
  FOR SELECT
  USING (clinic_id = (SELECT clinic_id FROM auth.users WHERE id = auth.uid()));

Agora quando usuário de Clínica A roda SELECT * FROM appointments, ele só vê appointments onde clinic_id = id da Clínica A dele. Banco executa:

SELECT * FROM appointments 
WHERE clinic_id = (SELECT clinic_id FROM auth.users WHERE id = auth.uid());

Automático. Sem você pensar.

OK, isso funciona. Mas tem três problemas que aparecem em produção.

Problema 1: JWT não passa tenant_id, precisa subquery

No setup básico, você tá rodando subquery toda vez:

(SELECT clinic_id FROM auth.users WHERE id = auth.uid())

Essa subquery roda em cada row da sua policy. Se sua tabela tem 100 mil rows, ela roda 100 mil vezes (ou banco otimiza, mas não é garantido).

Solução: botar tenant_id direto no JWT.

Seu login (exemplo com Supabase):

const { data, error } = await supabase.auth.signInWithPassword({
  email: user.email,
  password: user.password,
});

Supabase tá pegando user.id e botando no JWT. Mas você precisa de clinic_id também.

Criança, Supabase tem user_metadata. Você pode botar clinic_id lá:

const { data, error } = await supabase.auth.admin.updateUserById(user.id, {
  user_metadata: {
    clinic_id: clinic.id,
  },
});

Aí seu JWT fica assim:

{
  "sub": "user-id-xxx",
  "user_metadata": {
    "clinic_id": "clinic-id-yyy"
  }
}

Agora sua policy fica:

CREATE POLICY "users see their clinic's data" ON appointments
  FOR SELECT
  USING (clinic_id = (auth.jwt() ->> 'user_metadata')::jsonb ->> 'clinic_id');

Isso tira a subquery. Você tá buscando direto do JWT (que já tá em memória).

Pode ser mais elegante:

CREATE POLICY "users see their clinic's data" ON appointments
  FOR SELECT
  USING (clinic_id::text = auth.jwt() -> 'user_metadata' ->> 'clinic_id');

Ou se você controlar JWT diretamente (tipo com supabase.rpc()):

CREATE POLICY "users see their clinic data" ON appointments
  FOR SELECT
  USING (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

Performance win: sem subquery, RLS roda em O(1) tempo de acesso, não O(n).

Problema 2: DELETE/UPDATE precisam de acesso diferente que SELECT

Você quer que usuário veja todos os appointments da clínica dele.

Mas não quer que delete um appointment de outro usuário da mesma clínica.

Solução: policies diferentes pra SELECT, INSERT, UPDATE, DELETE.

-- SELECT: vê todos da clínica dele
CREATE POLICY "select_own_clinic_appointments" ON appointments
  FOR SELECT
  USING (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

-- UPDATE: só atualiza appointment que ele criou
CREATE POLICY "update_own_appointments" ON appointments
  FOR UPDATE
  USING (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  )
  WITH CHECK (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  );

-- DELETE: só deleta appointment que ele criou
CREATE POLICY "delete_own_appointments" ON appointments
  FOR DELETE
  USING (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  );

-- INSERT: só insere na clínica dele
CREATE POLICY "insert_own_clinic_appointments" ON appointments
  FOR INSERT
  WITH CHECK (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

Repare o WITH CHECK. É parte importante que muita gente esquece.

USING = condição pra ler/deletar WITH CHECK = condição pra escrever/atualizar

Se você só colocar USING em UPDATE, usuário consegue rodar:

UPDATE appointments SET clinic_id = outro_clinic WHERE id = 'seu-id'

Com WITH CHECK, você garante que clinic_id fica igual pra sempre.

Problema 3: Policies com SECURITY DEFINER são a única forma de fazer permissões complexas

Tem case que você precisa: usuário pode ver appointment se está atribuído à esse appointment OU é admin da clínica.

Fazer isso em policy fica complexo:

CREATE POLICY "view_if_assigned_or_admin" ON appointments
  FOR SELECT
  USING (
    (assigned_to = auth.uid())
    OR (
      EXISTS (
        SELECT 1 FROM clinic_staff
        WHERE clinic_staff.user_id = auth.uid()
        AND clinic_staff.clinic_id = appointments.clinic_id
        AND clinic_staff.role = 'admin'
      )
    )
  );

Isso funciona, mas tem problema: OUTER JOIN em table outra.

Se sua table appointments tem 1M rows e você roda SELECT, essa policy roda EXISTS pra cada row. É lento.

Solução: usar função com SECURITY DEFINER.

CREATE OR REPLACE FUNCTION can_view_appointment(appointment_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM appointments
    WHERE id = appointment_id
    AND (
      assigned_to = auth.uid()
      OR (
        SELECT role FROM clinic_staff
        WHERE user_id = auth.uid()
        AND clinic_id = appointments.clinic_id
      ) = 'admin'
    )
  );
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE POLICY "view_if_assigned_or_admin" ON appointments
  FOR SELECT
  USING (can_view_appointment(id));

SECURITY DEFINER = função roda com permissões de quem criou (geralmente admin), não do usuário atual. Aí você pode fazer JOIN/EXISTS sem preocupação, banco otimiza melhor.

Trade-off: você perde um nível de proteção. Se função tem bug, pode vazar dado. Mas se função é bem testada, you ganha performance.

Na prática: use SECURITY DEFINER pra lógica complexa, use policy simples pra lógica trivial.

Problema 4: Debugar RLS quebrado

Usuário reclama: “Não consigo ver meu data”.

Como você debuga isso?

Primeiro: enable logging de RLS.

ALTER SYSTEM SET log_statement = 'all';
ALTER SYSTEM SET log_row_security = on;
SELECT pg_reload_conf();

Aí tail no log:

tail -f /var/lib/postgresql/data/log/postgresql.log | grep "policy"

Vai aparecer tipo:

QUERY (filtered) SELECT ... USING (clinic_id = ...)

(filtered) = RLS entrou em ação.

Segundo: validar JWT manualmente.

Se você usa Supabase, JWT deve estar assim:

SELECT auth.jwt();
-- Retorna: {..., "user_metadata": {..., "clinic_id": "xxx"}}

Se não retorna clinic_id, o problema é na função de login (não tá botando clinic_id em user_metadata).

Terceiro: testar policy sem RLS.

Desabilita RLS temporariamente:

ALTER TABLE appointments DISABLE ROW LEVEL SECURITY;
SELECT * FROM appointments; -- Agora vê tudo
ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

Se SELECT retorna diferente com RLS on/off, problema é na policy. Se retorna igual, problema é na app (não tá mandando clinic_id no JWT).

Quarto: debugar policy específica.

EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM appointments 
WHERE clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id';

EXPLAIN mostra qual policy tá sendo aplicada e quantos rows ela tá filtrando.

Se mostra “0 rows returned” mas deveria ter rows, problema é na condição da policy (clinic_id não tá bate com JWT).

Problema 5: Deadlock em SECURITY DEFINER functions

Imagina: você tem política de que admin pode criar appointment, system pode atualizar appointment.

CREATE OR REPLACE FUNCTION system_update_appointment(id UUID, status TEXT)
RETURNS VOID AS $$
BEGIN
  UPDATE appointments SET status = status WHERE id = id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Admin chama função assim:

SELECT system_update_appointment('appt-id', 'completed');

O que acontece:

1. Admin roda função (com permissões de admin)
2. Função tenta UPDATE em appointments
3. RLS valida: admin consegue atualizar? Sim.
4. Update acontece.

Problema: se você tiver triggers ou outros updates rodando paralelo, você pode ter deadlock.

Solução: set lock_timeout e statement_timeout:

CREATE OR REPLACE FUNCTION system_update_appointment(id UUID, status TEXT)
RETURNS VOID AS $$
BEGIN
  SET lock_timeout = '5s';
  SET statement_timeout = '10s';
  
  UPDATE appointments SET status = status WHERE id = id;
  
  IF NOT FOUND THEN
    RAISE EXCEPTION 'Appointment not found or no permission';
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Aí se função trava por mais de 5 segundos esperando lock, ela falha gracefully em vez de deadlock infinito.

Outra solução: índices bons.

CREATE INDEX idx_appointments_clinic_id ON appointments(clinic_id);
CREATE INDEX idx_appointments_created_by ON appointments(created_by);
CREATE INDEX idx_clinic_staff_user_id ON clinic_staff(user_id);

RLS roda com índices. Se índices não estão lá, PostgreSQL faz table scan, que trava mais facilmente.

Problema 6: GRANT é confuso com RLS

Você criou policy, testou, funciona. Aí você roda em produção e o usuário toma erro de permission.

Problema: RLS precisa de GRANT na table.

GRANT SELECT, INSERT, UPDATE, DELETE ON appointments TO "anon";
GRANT SELECT, INSERT, UPDATE, DELETE ON appointments TO "authenticated";

Com Supabase:

GRANT ALL ON public.appointments TO authenticated;

Se você não faz GRANT, usuário toma erro “permission denied for table appointments” antes de RLS nem rodar.

Ordem é:

1. GRANT (usuário consegue acessar table?)
2. RLS (usuário consegue acessar ROW?)

Se falha em 1, falha. Se passa em 1, RLS filtra.

Exemplo completo: multi-tenant de verdade

Aqui vou montar um sistema multi-tenant real com RLS.

Tabelas:

CREATE TABLE clinics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE auth.users (
  id UUID PRIMARY KEY,
  email TEXT UNIQUE,
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  role TEXT DEFAULT 'user',
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE appointments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id UUID NOT NULL REFERENCES clinics(id),
  patient_name TEXT NOT NULL,
  assigned_to UUID REFERENCES auth.users(id),
  status TEXT DEFAULT 'scheduled',
  scheduled_at TIMESTAMP NOT NULL,
  created_by UUID NOT NULL REFERENCES auth.users(id),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_appointments_clinic_id ON appointments(clinic_id);
CREATE INDEX idx_appointments_created_by ON appointments(created_by);

Policies:

ALTER TABLE appointments ENABLE ROW LEVEL SECURITY;

-- SELECT: vê se é da clínica dele
CREATE POLICY "select_own_clinic" ON appointments
  FOR SELECT
  USING (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

-- INSERT: só na clínica dele
CREATE POLICY "insert_own_clinic" ON appointments
  FOR INSERT
  WITH CHECK (clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id');

-- UPDATE: só appointment que ele criou
CREATE POLICY "update_own_appointment" ON appointments
  FOR UPDATE
  USING (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  )
  WITH CHECK (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND created_by = auth.uid()
  );

-- DELETE: admin da clínica pode deletar qualquer um
CREATE OR REPLACE FUNCTION is_clinic_admin()
RETURNS BOOLEAN AS $$
BEGIN
  RETURN (
    SELECT role FROM auth.users 
    WHERE id = auth.uid()
  ) = 'admin';
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE POLICY "delete_if_admin" ON appointments
  FOR DELETE
  USING (
    clinic_id::text = current_setting('request.jwt.claims')::jsonb ->> 'clinic_id'
    AND is_clinic_admin()
  );

Grants:

GRANT SELECT, INSERT, UPDATE, DELETE ON appointments TO authenticated;
GRANT SELECT ON clinics TO authenticated;
GRANT SELECT ON auth.users TO authenticated;

Checklist:

- RLS habilitado: ALTER TABLE ... ENABLE ROW LEVEL SECURITY
- GRANT feito pra usuários acessarem table
- JWT contém clinic_id em user_metadata
- Policies existem pra SELECT, INSERT, UPDATE, DELETE
- UPDATE tem WITH CHECK pra evitar escrita em outro tenant
- Funções SECURITY DEFINER usadas só pra lógica complexa
- Índices criados em foreign keys
- Logging testado com ALTER SYSTEM SET log_row_security

Próximos passos

RLS é confiável pra produção quando bem feito. Mas precisa:

1. Entender design de multi-tenant pra saber que dados segregar
2. Conhecer Supabase + React pra usar Supabase bem
3. Documentar policies pra outros devs não quebrarem

RLS não é overkill. É segurança básica que aplicação não consegue garantir sozinha.

• Criar JWT com clinic_id em user_metadata
• Criar policy pra SELECT de cada tabela
• Criar policy pra INSERT/UPDATE/DELETE diferenciado
• Adicionar GRANT pra usuários acessarem
• Usar índices em foreign keys (clinic_id, user_id)
• Testar RLS com ON e OFF, ver diferença
• Debugar com EXPLAIN ANALYZE se policy tá lenta
• Criar função SECURITY DEFINER pra lógica complexa
• Adicionar log_row_security em staging, validar queries
• Testar multi-tenant: criar 2 usuários de clínicas diferentes, validar isolamento