permission denied for schema public - zmiany w PostgreSQL 15

PostgreSQL

W skrócie

Problem: po przejściu na PostgreSQL 15 lub nowszy zwykła rola dostaje ERROR: permission denied for schema public przy tworzeniu tabeli.
Dlaczego: od wersji 15 schemat public nie daje już domyślnie prawa CREATE wszystkim rolom - to celowa zmiana bezpieczeństwa.
Rozwiązanie: nadajemy roli prawo do schematu poleceniem GRANT albo zakładamy dedykowany schemat dla aplikacji.

To jeden z najczęstszych problemów po aktualizacji do PostgreSQL 15 lub nowszego. Kod, który latami działał, nagle nie może utworzyć tabeli w schemacie public. Powód nie jest błędem - twórcy PostgreSQL świadomie zaostrzyli domyślne uprawnienia schematu public, aby ograniczyć ryzyko nadużyć we współdzielonej bazie. Problem dotyczy każdego, kto migruje bazę albo zakłada nową na świeżej wersji. Pokazujemy, co dokładnie się zmieniło i jak przywrócić działanie w kontrolowany sposób.

Jak to wygląda w praktyce

Zwykła rola łączy się z bazą, ale nie może nic w niej utworzyć:

sklep=> CREATE TABLE test (id int);
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE test (id int);
                     ^

Ta sama komenda działała bez zarzutu na PostgreSQL 14 i starszych. Bywa też, że migracja aplikacji (na przykład narzędzie tworzące tabele przy starcie) przewraca się z tym właśnie komunikatem zaraz po podniesieniu wersji serwera.

Dlaczego tak się dzieje

Do PostgreSQL 14 schemat public miał nadane prawo CREATE dla specjalnej roli PUBLIC, czyli praktycznie dla każdej roli w bazie. Oznaczało to, że dowolny użytkownik mógł tworzyć obiekty w public - wygodne, ale ryzykowne we współdzielonych bazach. Od wersji 15 to prawo zostało odebrane - schemat public nadal istnieje, ale nie pozwala już domyślnie tworzyć w nim obiektów rolom innym niż właściciel. Dodatkowo właścicielem public jest teraz rola pg_database_owner, czyli właściciel danej bazy. W efekcie rola aplikacji, która wcześniej bez pytania tworzyła tabele, po migracji dostaje odmowę na poziomie schematu, jeszcze zanim dojdzie do samej tabeli.

Jak to rozwiązać krok po kroku

Zdecyduj, gdzie aplikacja ma tworzyć obiekty. Wariant zgodny z dawnym zachowaniem: nadaj roli prawo do schematu public: GRANT CREATE, USAGE ON SCHEMA public TO app;.
Wariant czystszy i zalecany: załóż osobny schemat dla aplikacji i uczyń rolę jego właścicielem: CREATE SCHEMA sklep_app AUTHORIZATION app;.
Ustaw roli search_path, aby domyślnie trafiała do właściwego schematu: ALTER ROLE app SET search_path = sklep_app, public;.
Jeśli chcesz przywrócić stare, otwarte zachowanie dla wszystkich ról (świadomie, tylko w zaufanej bazie): GRANT CREATE ON SCHEMA public TO PUBLIC; - używaj tego ostrożnie.
Nadaj też prawa do samych danych, jeśli tabele tworzy inna rola niż aplikacja czytająca - patrz przywileje na tabelach (GRANT ... ON ALL TABLES ...).
Polecenia GRANT i CREATE SCHEMA wykonuj jako właściciel bazy albo superuser - zwykła rola nie nada sobie sama prawa do schematu.

Jak sprawdzić, że zadziałało

Sprawdź efektywne prawa roli do schematu wprost - obie kolumny powinny zwrócić t:

SELECT has_schema_privilege('app', 'public', 'CREATE') AS moze_tworzyc,
       has_schema_privilege('app', 'public', 'USAGE')  AS moze_uzywac;

Najlepszym testem jest jednak utworzenie tabeli jako rola aplikacji - jeśli CREATE TABLE przejdzie bez odmowy, problem jest rozwiązany. Listę praw schematów obejrzysz też w psql poleceniem \dn+.

Poczytaj więcej
Lekcja z kursu PostgreSQL na ten temat
Cały bezpłatny kurs administracji PostgreSQL

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

Autorzy

Tekst: Redakcja JSystems

Najczęściej zadawane pytania

Dlaczego dopiero PostgreSQL 15 zaczął zwracać permission denied for schema public?

Bo od wersji 15 twórcy odebrali schematowi public domyślne prawo CREATE dla roli PUBLIC, czyli dla wszystkich ról. Wcześniej każdy mógł tworzyć obiekty w public, co było wygodne, ale niebezpieczne we współdzielonych bazach. Teraz zwykła rola musi dostać prawo do schematu jawnie, więc kod tworzący tabele w public przewraca się zaraz po migracji na nowszą wersję.

Jak przywrócić działanie tworzenia tabel w schemacie public?

Najprościej nadać roli prawo do schematu: GRANT CREATE, USAGE ON SCHEMA public TO app. Czystszym rozwiązaniem jest jednak założenie osobnego schematu dla aplikacji przez CREATE SCHEMA nazwa AUTHORIZATION app i ustawienie roli odpowiedniego search_path. Odtwarzanie starego, otwartego zachowania przez GRANT CREATE ON SCHEMA public TO PUBLIC stosuj tylko świadomie w zaufanej bazie.

Czym różni się prawo USAGE od CREATE na schemacie?

USAGE pozwala roli wejść do schematu i odwoływać się do obiektów, które w nim są, na przykład wykonać SELECT na istniejącej tabeli. CREATE pozwala tworzyć w schemacie nowe obiekty, takie jak tabele czy widoki. To dwa niezależne przywileje - rola może mieć USAGE bez CREATE, jeśli ma tylko czytać istniejące dane, a nie zakładać własnych obiektów.

Kto jest właścicielem schematu public w PostgreSQL 15 i nowszych?

Od wersji 15 właścicielem schematu public jest specjalna rola pg_database_owner, która odpowiada właścicielowi danej bazy. Dzięki temu w każdej bazie public należy do jej właściciela, a nie do jednej sztywnej roli. Właściciel może nadawać prawa do public innym rolom, a zwykłe role nie mają tam już domyślnie prawa tworzenia obiektów.