Nowy użytkownik nie widzi tabel - domyślne uprawnienia (default privileges)

PostgreSQL

Nowy użytkownik nie widzi tabel - domyślne uprawnienia (default privileges)

W skrócie

To jeden z najczęstszych zgrzytów przy zarządzaniu uprawnieniami w PostgreSQL. Ktoś raz nadaje dostęp do wszystkich tabel, jest spokojny, a po tygodniu aplikacja albo analityk zgłasza, że część danych zwraca błąd o braku uprawnień. Winne są nowe tabele, które powstały po nadaniu GRANT-u. Rozwiązaniem nie jest powtarzanie GRANT-u w kółko, tylko domyślne uprawnienia, czyli mechanizm default privileges.

Jak to wygląda w praktyce

Scenariusz jest wręcz podręcznikowy. Zakładasz konto raporty_ro i nadajesz mu dostęp do odczytu: GRANT SELECT ON ALL TABLES IN SCHEMA public TO raporty_ro;. Wszystko działa. Kilka dni później zespół deweloperski wdraża migrację, która dodaje trzy nowe tabele. Nagle zapytania analityka do tych tabel zwracają "permission denied for table nowa_tabela", mimo że przecież "nadano dostęp do wszystkiego". To samo dotyczy sekwencji - konto zapisujące dane potrafi dostać błąd przy insertach do tabeli z kolumną SERIAL, bo nie ma prawa USAGE na nowo utworzonej sekwencji. Za każdym razem ktoś dobija brakujące uprawnienia ręcznie i za każdym razem problem wraca przy kolejnej migracji.

Dlaczego tak się dzieje

GRANT jest operacją jednorazową i punktową. Klauzula ALL TABLES IN SCHEMA brzmi jak "wszystkie na zawsze", ale w rzeczywistości oznacza "wszystkie tabele istniejące w tym schemacie w momencie wykonania polecenia". Baza rozwija ten skrót do listy konkretnych obiektów i nadaje im uprawnienia po jednym. Obiekty, które jeszcze nie istnieją, nie mają jak zostać objęte - GRANT nic o nich nie wie. Dochodzi do tego reguła własności: nowa tabela należy do roli, która ją utworzyła, i domyślnie tylko ta rola (oraz superużytkownik) ma do niej dostęp. Dlatego jeśli tabele tworzy inna rola niż ta, która czyta dane, konto czytające musi dostać uprawnienia osobno - i właśnie ten krok wypada przy każdym nowym obiekcie.

Jak to rozwiązać krok po kroku

1. Najpierw nadaj uprawnienia do już istniejących obiektów, bo default privileges obejmuje wyłącznie przyszłość: GRANT USAGE ON SCHEMA public TO raporty_ro; oraz GRANT SELECT ON ALL TABLES IN SCHEMA public TO raporty_ro;.
2. Ustaw domyślne uprawnienia dla przyszłych tabel: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO raporty_ro;. Od tej chwili każda nowa tabela w tym schemacie automatycznie da roli SELECT.
3. Zwróć uwagę na to, kto tworzy obiekty. Default privileges wiąże się z rolą tworzącą. Jeśli tabele zakłada rola deweloper, ustaw regułę w jej imieniu: ALTER DEFAULT PRIVILEGES FOR ROLE deweloper IN SCHEMA public GRANT SELECT ON TABLES TO raporty_ro;.
4. Nie zapomnij o sekwencjach, jeśli konto ma zapisywać dane: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO aplikacja;. Bez tego insert do tabeli z kolumną SERIAL będzie się wywalał na braku dostępu do sekwencji.
5. Dla konta zapisującego analogicznie ustaw domyślne prawa na tabelach: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO aplikacja;.

Jak sprawdzić, że zadziałało

Ustawione reguły domyślne obejrzysz w psql poleceniem \ddp - pokaże, jakie uprawnienia będą przyznawane nowym obiektom, dla której roli tworzącej i w którym schemacie. Najlepszy test jest jednak praktyczny: utwórz testową tabelę w danym schemacie, a potem zaloguj się jako konto czytające i wykonaj na niej SELECT. Jeśli zapytanie przechodzi bez ręcznego GRANT-u, mechanizm działa. Realne uprawnienia na konkretnej tabeli sprawdzisz też poleceniem \dp nazwa_tabeli, gdzie w kolumnie z prawami dostępu zobaczysz swoją rolę wraz z literą oznaczającą przyznany przywilej.

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