Skip to content

Jak dodać RLS do tabeli

Jump to bottom
kubolot33123 edited this page Sep 20, 2026 · 1 revision

Jak dodać RLS (row-level security) do tabeli

Instrukcja dla tabel, których wiersze należą do konkretnego użytkownika. Opisuje, co robi się ręcznie, bo po dodaniu kolumny z właścicielem RLS nie włącza się sam — żaden element modelu EF nie generuje polityk Postgresa.

Przykłady w kodzie: migracja AddChildUserIdAndRls (budżety i ich dzieci, jedna polityka) oraz EnableRulesRls (reguły kategoryzacji, dane własne + wspólne, cztery polityki).

1. Jak to działa

Dane użytkownika chronią trzy warstwy. Każda robi co innego i żadnej nie da się pominąć:

Warstwa Gdzie Zakłada się
Filtr EF HasQueryFilter(QueryFilterNames.Owner, …) AppDbContext ręcznie, per encja
Polityka RLS Postgres ręcznie, SQL-em w migracji
Uprawnienia ról do tabeli Postgres automatycznie dla nowych tabel (ALTER DEFAULT PRIVILEGES), raz na bazę

Aplikacja łączy się jedną wspólną rolą dla wszystkich użytkowników, więc Postgres nie wie, kto pyta. Dlatego RlsSessionInterceptor przy każdym otwarciu połączenia ustawia zmienną sesyjną app.current_user_id (identyfikator konta z tokenu), a polityki porównują z nią kolumnę UserId.

Role

Rola Po co RLS
budget superuser, właściciel tabel, konto administracyjne do psql i migracji zawsze omija
budget_app rola aplikacji na co dzień dotyczy jej naprawdę
budget_jobs NOLOGIN, BYPASSRLS: zadania bez użytkownika (seedy, czyszczenie, administracja) omija

⚠️ Superuser zawsze omija RLS, także z FORCE ROW LEVEL SECURITY. Lokalny connection string z Username=budget (domyślny w appsettings.json) oznacza więc, że lokalnie chroni wyłącznie filtr EF, a polityki działają dopiero na roli budget_app. Dlatego testy RLS muszą łączyć się jako budget_app (sekcja 4).

Role zakłada raz na klaster skrypt api/db/setup-rls-roles.sql, uprawnienia do tabel raz na bazę api/db/grant-app-privileges.sql. Hasła ról nie trafiają do repozytorium — aplikacja bierze je z connection stringa.

2. Lista kroków dla nowej tabeli

Krok 1. Encja i model

  • Pole właściciela Guid UserId w konstruktorze encji (EF wiąże parametry konstruktora z właściwościami po nazwie — parametr bez właściwości wywala się dopiero w runtime, na każdym teście używającym AppDbContext).
  • W AppDbContext: IsRequired(), HasIndex(x => x.UserId) i filtr:
e.HasIndex(x => x.UserId);
e.HasQueryFilter(QueryFilterNames.Owner, x => CurrentUserId == null || x.UserId == CurrentUserId);
  • Handler tworzący wiersz bierze właściciela z ICurrentUserAccessor.UserId — nigdy z ciała żądania.
  • Nazwa kolumny to UserId, jak w pozostałych tabelach. Kod administracyjny (OwnerDataService) i polityki jej szukają.

Krok 2. Migracja

$env:BaseOutputPath = "$env:TEMP\bt\bin\"; $env:IntermediateOutputPath = "$env:TEMP\bt\obj\"
dotnet ef migrations add <NazwaPoAngielsku> --project api/src/BudgetTracker.Api --output-dir Infrastructure/Migrations
Remove-Item Env:BaseOutputPath; Remove-Item Env:IntermediateOutputPath

EF wygeneruje kolumnę i indeks. Politykę dopisujesz sam w Up (i jej zdjęcie w Down):

migrationBuilder.Sql(
    """
    ALTER TABLE "MojaTabela" ENABLE ROW LEVEL SECURITY;
    ALTER TABLE "MojaTabela" FORCE ROW LEVEL SECURITY;
    CREATE POLICY owner_isolation ON "MojaTabela"
        USING ("UserId" = NULLIF(current_setting('app.current_user_id', true), '')::uuid);
    """);

Down:

DROP POLICY owner_isolation ON "MojaTabela";
ALTER TABLE "MojaTabela" NO FORCE ROW LEVEL SECURITY;
ALTER TABLE "MojaTabela" DISABLE ROW LEVEL SECURITY;

Uwagi:

  • NULLIF(current_setting(…, true), '')::uuid, nie gołe rzutowanie: poza żądaniem HTTP interceptor ustawia pusty string, a rzutowanie go na uuid rzuciłoby błędem SQL zamiast po prostu nie dopasować żadnego wiersza.
  • Bez USING … WITH CHECK jedna polityka obejmuje odczyt i zapis (warunek USING jest wtedy używany także do sprawdzania nowych wierszy).
  • Istniejące wiersze potrzebują backfillu UserId (np. z rodzica po kluczu obcym, jak w AddChildUserIdAndRls). Wiersze bez dopasowania dostają pusty Guid i stają się niewidoczne dla zwykłych kont.
  • Po wygenerowaniu przeczytaj migrację i dopisz XML <summary>/<remarks> z tym, co robi z istniejącymi danymi.

Krok 3. Kasowanie i przepisywanie danych konta

Usunięcie konta z ekranu administratora idzie przez OwnerDataService (API budżetu). Dopisz tam nową tabelę do: DeleteAsync (trwałe usunięcie, IgnoreQueryFilters()) i ReassignAsync (przepisanie na inne konto). Bez tego po usunięciu konta wiersze zostają w bazie jako dane, których nikt nie zobaczy.

Jeśli tabela jest dzieckiem budżetu, dopisz ją też do BudgetChildren.cs i BudgetPurger.cs (reset zostawia zasady budżetu, usunięcie zabiera wszystko).

Krok 4. Kod działający bez użytkownika

Seedy, zadania w tle i administracja nie mają app.current_user_id, więc pod budget_app widziałyby pustą tabelę, a ich zapis odrzuciłaby polityka. Wykonaj je w jednej transakcji na roli z BYPASSRLS:

await using var transaction = await db.Database.BeginTransactionAsync();
await db.Database.ExecuteSqlRawAsync("SET LOCAL ROLE budget_jobs");
// … praca …
await transaction.CommitAsync();

To SET LOCAL, na czas jednej transakcji. Zwykłe SET ROLE zostałoby w połączeniu wracającym do puli. Wzorce: Program.cs (seedy), BudgetPurger, OwnerDataService.AsSystemAsync.

Krok 5. Uprawnienia

Nic nie robisz dla nowych tabel — ALTER DEFAULT PRIVILEGES z grant-app-privileges.sql nadaje budget_app i budget_jobs SELECT/INSERT/UPDATE/DELETE, gdy tabelę tworzy budget. Wyjątek: baza założona przed uruchomieniem tego skryptu — uruchom go na niej ponownie (jest per baza).

Krok 6. Wdrożenie

Migracja i (w razie potrzeby) grant-app-privileges.sql na każdej bazie: deweloperska, demo (budgettracker_uiverify), testowe. Na prawdziwej bazie budgettracker tylko po wyraźnej zgodzie właściciela i po pg_dump.

3. Wariant: dane wspólne + własne (reguły kategoryzacji)

Gdy część wierszy ma być widoczna dla wszystkich (reguły bazowe), a użytkownik ma zmieniać tylko własne, jedna polityka nie wystarczy. Wiersze wspólne mają pusty UserId, a polityki są cztery:

CREATE POLICY rules_read ON "CategoryRules" FOR SELECT
    USING ("UserId" = '00000000-0000-0000-0000-000000000000'::uuid
        OR "UserId" = NULLIF(current_setting('app.current_user_id', true), '')::uuid);

CREATE POLICY rules_insert ON "CategoryRules" FOR INSERT
    WITH CHECK ("UserId" = NULLIF(current_setting('app.current_user_id', true), '')::uuid);

CREATE POLICY rules_update ON "CategoryRules" FOR UPDATE
    USING ("UserId" = NULLIF(current_setting('app.current_user_id', true), '')::uuid)
    WITH CHECK ("UserId" = NULLIF(current_setting('app.current_user_id', true), '')::uuid);

CREATE POLICY rules_delete ON "CategoryRules" FOR DELETE
    USING ("UserId" = NULLIF(current_setting('app.current_user_id', true), '')::uuid);

Skutki, o których trzeba pamiętać:

  • Filtr EF musi odpowiadać polityce: CurrentUserId == null || x.UserId == CategoryRule.SharedUserId || x.UserId == CurrentUserId.
  • Zapis wspólnego wiersza kończy się w bazie zmianą 0 wierszy (błąd 500), więc handler sprawdza to wcześniej i zwraca 409 (CategoryRuleSharedReadOnlyException).
  • Wiersze wspólne zakłada seed jako budget_jobs — budget_app nie ma prawa wstawić wiersza z cudzym (pustym) UserId.
  • ⚠️ Puste UserId = „właściciel bez konta" na ekranie „Dane bez właściciela". Podsumowanie i sierotki nie liczą takiej tabeli, a OwnerDataService nie kasuje i nie przepisuje wierszy wspólnych (guard ownerId == Guid.Empty) — inaczej usunięcie „pustego właściciela" zabrałoby reguły bazowe wszystkim kontom.

4. Test RLS

Testy handlerów łączą się jako budget (superuser), który omija RLS, więc nie mówią nic o politykach. Test RLS musi:

  1. zbudować bazę prawdziwymi migracjami (MigrateAsync) — EnsureCreated nie zakłada polityk,
  2. nadać uprawnienia (treść grant-app-privileges.sql),
  3. połączyć się jako budget_app, z RlsSessionInterceptor dla wybranego użytkownika,
  4. czytać z IgnoreQueryFilters(), żeby filtr EF niczego nie ukrył i sprawdzać wyłącznie bazę.

Wzorzec: CategoryRulesRlsTests. Sprawdza m.in.: użytkownik widzi własne i wspólne wiersze, nigdy cudzych; bez użytkownika tylko wspólne; nie wstawi wiersza cudzego ani wspólnego (SQLSTATE 42501); UPDATE/DELETE cudzego wiersza dotyka 0 wierszy; budget_jobs może zapisać wspólny.

Dobry nawyk: po napisaniu testu wyłącz na chwilę ENABLE ROW LEVEL SECURITY w migracji i zobacz, że testy robią się czerwone. Test, który przechodzi bez RLS, niczego nie pilnuje.

5. Ręczne sprawdzenie w psql

SET ROLE budget_app;
SELECT set_config('app.current_user_id', '<guid użytkownika>', false);
SELECT count(*) FROM "MojaTabela";     -- tylko wiersze tego użytkownika
RESET ROLE;

Stan tabeli: SELECT relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname = 'MojaTabela'; oraz SELECT policyname, cmd FROM pg_policies WHERE tablename = 'MojaTabela';

6. Pułapki (skrót)

  • Kolumna z właścicielem nie włącza RLS. Polityka to osobny SQL w migracji.
  • Superuser (budget) i BYPASSRLS (budget_jobs) omijają RLS. Aplikacja na co dzień powinna łączyć się jako budget_app.
  • SET LOCAL ROLE, nie SET ROLE — inaczej rola zostaje w puli połączeń.
  • ExecuteUpdate/ExecuteDelete na wierszu, którego RLS nie pokazuje, zwracają 0, nie błąd. Sprawdzaj wynik.
  • Nowa tabela musi trafić do OwnerDataService, inaczej usunięcie konta zostawi jej wiersze.
  • Zmiana nazwy parametru konstruktora encji albo parametr bez właściwości psuje model dopiero w runtime.
  • Polityka bez NULLIF(…, '') rzuca błąd SQL, gdy zmienna sesyjna jest pusta (zapytanie bez użytkownika), zamiast zwrócić zero wierszy.

Clone this wiki locally