Po co są schematy i jak je organizować w PostgreSQL

PostgreSQL

Po co są schematy i jak je organizować w PostgreSQL

W skrócie

W młodej bazie wszystko trafia do schematu public i nikomu to nie przeszkadza. Problem pojawia się z czasem: setki tabel w jednym miejscu, kolizje nazw między modułami, brak sposobu na nadanie uprawnień "na cały obszar aplikacji". Rozwiązaniem, które PostgreSQL ma od zawsze, są schematy - lekkie przestrzenie nazw wewnątrz bazy. Dobrze użyte porządkują strukturę, upraszczają uprawnienia i pozwalają trzymać dane różnych modułów obok siebie bez konfliktów.

Jak to wygląda w praktyce

Aplikacja rośnie i w schemacie public masz już tabele sprzedaży, magazynu, raportowania i kilku integracji - kilkaset obiektów w jednym worku. Dwa moduły chcą mieć tabelę o nazwie zamowienia, ale nie mogą, bo nazwa musi być unikalna w obrębie schematu. Nadanie komuś dostępu tylko do części tabel wymaga wyliczania ich pojedynczo, bo nie ma naturalnej granicy, którą można objąć jednym GRANT. Do tego dochodzi zamieszanie z widocznością: piszesz SELECT * FROM cennik i dostajesz "relation cennik does not exist", choć tabela istnieje - tyle że w innym schemacie niż ten na Twojej ścieżce wyszukiwania. Bez uporządkowania w schematy każdy taki drobiazg mnoży się z liczbą obiektów.

Dlaczego tak się dzieje

Schemat w PostgreSQL to nazwana przestrzeń, wewnątrz której nazwy obiektów muszą być unikalne, ale między schematami mogą się powtarzać - dlatego sprzedaz.zamowienia i logistyka.zamowienia spokojnie współistnieją w jednej bazie. Każda nowa baza dostaje domyślny schemat public, więc jeśli nie zrobisz nic więcej, wszystko trafia właśnie tam. O tym, które schematy widać bez podawania pełnej nazwy, decyduje parametr search_path - lista schematów przeszukiwanych po kolei. Domyślnie zawiera on schemat o nazwie roli i public. Jeśli tabela leży poza ścieżką, musisz odwołać się do niej z prefiksem schematu, inaczej PostgreSQL jej nie znajdzie. To wyjaśnia zarówno "relation does not exist" przy istniejącej tabeli, jak i to, dlaczego uprawnienia warto nadawać na poziomie schematu: schemat jest naturalną, obejmowalną granicą, na której można ustawić dostęp i domyślne uprawnienia dla przyszłych obiektów.

Jak to rozwiązać krok po kroku

1. Wybierz zasadę podziału i trzymaj się jej: albo schemat na moduł aplikacji (sprzedaz, magazyn, raporty), albo schemat na środowisko/zespół. Spójna konwencja jest ważniejsza niż to, którą wybierzesz.
2. Twórz schematy jawnie, najlepiej od razu z właścicielem: CREATE SCHEMA sprzedaz AUTHORIZATION rola_sprzedaz;. Właściciel schematu może swobodnie zakładać w nim obiekty.
3. Nadaj dostęp na poziomie schematu, zamiast wyliczać tabele: GRANT USAGE ON SCHEMA sprzedaz TO rola_raporty; pozwala wejść do schematu, a GRANT SELECT ON ALL TABLES IN SCHEMA sprzedaz TO rola_raporty; daje odczyt istniejących tabel.
4. Zadbaj o przyszłe obiekty przez domyślne uprawnienia, żeby nowe tabele automatycznie były dostępne dla właściwych ról: ALTER DEFAULT PRIVILEGES IN SCHEMA sprzedaz GRANT SELECT ON TABLES TO rola_raporty;.
5. Ustaw search_path tak, by codzienne zapytania nie wymagały prefiksów. Dla roli: ALTER ROLE analityk SET search_path = raporty, sprzedaz, public; - PostgreSQL będzie szukał obiektów w tej kolejności.
6. W kodzie i migracjach o krytycznym znaczeniu odwołuj się do obiektów z pełną nazwą schemat.tabela, żeby wynik nie zależał od bieżącej ścieżki wyszukiwania.

Jak sprawdzić, że zadziałało

Listę schematów w bazie zobaczysz w psql poleceniem \dn albo zapytaniem SELECT schema_name FROM information_schema.schemata;. Rozłożenie obiektów sprawdzisz przez SELECT schemaname, count(*) FROM pg_tables GROUP BY schemaname; - powinno pokazać, że tabele faktycznie rozeszły się po schematach, a nie tkwią w public. Aktualną ścieżkę wyszukiwania odczytasz przez SHOW search_path;, a to, że działa zgodnie z zamiarem, potwierdzi udane zapytanie bez prefiksu do tabeli z pierwszego schematu na liście. Poprawność uprawnień najłatwiej zweryfikować, logując się jako dana rola i próbując odczytać tabelę: dostęp powinien działać na schematach, do których nadałeś USAGE i SELECT, a być odrzucony tam, gdzie roli nie dopuściłeś. Domyślne uprawnienia dla przyszłych obiektów obejrzysz przez \ddp.

Wróć do listy: 100 najczęstszych pytań i problemów z PostgreSQL

Sprawdź szkolenie: Administracja, replikacja i tuning baz danych PostgreSQL

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5

Sprawdź szkolenie: Zaawansowana administracja PostgreSQL (HA, DR, monitoring, skalowanie)

To szkolenie może być dofinansowane z KFS lub BUR.

★★★★★Średnia ocena naszych szkoleń w Google: 5/5