Jak zarządzać DOMYŚLNYMI PRZYWILEJAMI dla UŻYTKOWNIKÓW na BAZIE DANYCH vs SCHEMA?

Chcę migrować dość prostą, wewnętrzną aplikację bazodanową z SQLite3 do PostgreSQL 9.3 i za każdym razem zaostrzać uprawnienia w DB.

Aplikacja składa się obecnie z polecenia aktualizacji danych; i jeden do zapytania. Oczywiście będę także musiał utrzymywać bazę danych w inny sposób (tworzyć nowe tabele, widoki, wyzwalacze itp.).

Chociaż ta aplikacja będzie początkowo jedyna hostowana na serwerze, wolałbym piec w założeniu, że w przyszłości może ona być hostowana na serwerze z innymi bazami danych, niż później musieć szyfrować, jeśli stanie się to konieczne w przyszłość.

Myślę, że byłby to dość powszechny zestaw wymagań, ale mam problem ze znalezieniem prostego samouczka wyjaśniającego, jak skonfigurować nową bazę danych w PostgreSQL, z tego rodzaju separacją użytkowników / uprawnień. Odnoszone są obszerne odniesienia do grup, użytkowników, ról, baz danych, schematów i domeny; ale uważam je za mylące.

Oto, co do tej pory próbowałem (od wewnątrz psqljako „postgres”):

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;
\connect hostdb
CREATE SCHEMA hostdb;
CREATE USER hostdb_admin WITH PASSWORD 'youwish';
CREATE USER hostdb_mgr   WITH PASSWORD 'youwish2';
CREATE USER hostdb_usr WITH PASSWORD 'youwish3';

GRANT ALL PRIVILEGES ON DATABASE hostdb TO hostdb_admin;
GRANT CONNECT ON DATABASE hostdb TO hostdb_mgr, hostdb_usr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO hostdb_mgr;
ALTER DEFAULT PRIVILEGES IN SCHEMA hostdb GRANT SELECT ON TABLES TO hostdb_usr;

Ale nie otrzymuję zamierzonej semantyki. Chcę to skonfigurować, aby tylko hostdb_admintabele mogły tworzyć (upuszczać i zmieniać); hostdb_mgrmogą czytać, wstawianie, aktualizowanie i usuwanie wszystkich tabelach domyślnie; i hostdb_usrmoże czytać tylko wszystkie tabele (i widoki).

Kiedy spróbowałem, okazało się, że jestem w stanie tworzyć tabele hostdbjako dowolny z tych użytkowników; ale dla każdego użytkownika mogłem tylko czytać lub modyfikować tabele utworzone przez tego użytkownika - chyba że użyję jawnego GRANT.

Zgaduję, że czegoś brakuje między CREATE DATABASEi CREATE SCHEMAcoś, co można zastosować SCHEMAdo DATABASE?

(W miarę postępu rzeczy będę mieć pytania dotyczące podobnych ograniczeń TRIGGERS, procedur przechowywanych VIEWSi być może innych obiektów).

Gdzie mogę znaleźć porządny przewodnik, samouczek lub serię filmów na ten temat?

Gdzie mogę znaleźć porządny przewodnik, samouczek lub serię filmów na ten temat?

Znajdziesz wszystko w podręczniku. Linki poniżej.
To prawda, że ​​sprawa nie jest trywialna, a czasem myląca. Oto przepis na przypadek użycia:

Przepis

Chcę to skonfigurować, aby tylko hostdb_admintabele mogły tworzyć (upuszczać i zmieniać); mogą czytać, wstawianie, aktualizowanie i usuwanie wszystkich tabelach domyślnie; i może czytać tylko wszystkie tabele (i widoki).
hostdb_mgr
hostdb_usr

Jako administrator postgres:

CREATE USER schma_admin WITH PASSWORD 'youwish';
-- CREATE USER schma_admin WITH PASSWORD 'youwish' CREATEDB CREATEROLE; -- see below
CREATE USER schma_mgr   WITH PASSWORD 'youwish2';
CREATE USER schma_usr   WITH PASSWORD 'youwish3';

Jeśli chcesz mieć silniejszego administratora, który może również zarządzać bazami danych i rolami, dodaj atrybuty roli CREATEDBiCREATEROLE wyżej.

Przydziel każdą rolę do następnego wyższego poziomu, aby wszystkie poziomy „dziedziczyły” co najmniej zestaw uprawnień z następnego niższego poziomu (kaskadowanie):

GRANT schma_usr TO schma_mgr;
GRANT schma_mgr TO schma_admin;

CREATE DATABASE hostdb;
REVOKE ALL ON DATABASE hostdb FROM public;  -- see notes below!

GRANT CONNECT ON DATABASE hostdb TO schma_usr;  -- others inherit

\connect hostdb  -- psql syntax

Nazywam ten schemat schma( hostdbco nie byłoby mylące). Wybierz dowolne imię. Opcjonalnie ustaw schma_adminwłaściciela schematu:

CREATE SCHEMA schma AUTHORIZATION schma_admin;

SET search_path = schma;  -- see notes

ALTER ROLE schma_admin IN DATABASE hostdb SET search_path = schma; -- not inherited
ALTER ROLE schma_mgr   IN DATABASE hostdb SET search_path = schma;
ALTER ROLE schma_usr   IN DATABASE hostdb SET search_path = schma;

GRANT USAGE  ON SCHEMA schma TO schma_usr;
GRANT CREATE ON SCHEMA schma TO schma_admin;

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT SELECT                           ON TABLES TO schma_usr;  -- only read

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT INSERT, UPDATE, DELETE, TRUNCATE ON TABLES TO schma_mgr;  -- + write, TRUNCATE optional

ALTER DEFAULT PRIVILEGES FOR ROLE schma_admin
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO schma_mgr;  -- SELECT, UPDATE are optional 

Do and drop and alternotatek patrz poniżej.

W miarę, jak sprawy stają się bardziej zaawansowane, będę także miał pytania, aby zastosować podobne ograniczenia TRIGGERS, procedury składowane VIEWSi być może inne obiekty.

Widoki są wyjątkowe. Po pierwsze:

... (ale należy pamiętać, że ALL TABLESuważa się, że obejmuje widoki i tabele obce).

I dla widoków, które można aktualizować :

Pamiętaj, że użytkownik wykonujący wstawianie, aktualizację lub usuwanie w widoku musi mieć odpowiednie uprawnienia do wstawiania, aktualizacji lub usuwania w widoku. Ponadto właściciel widoku musi mieć odpowiednie uprawnienia do bazowych relacji bazowych, ale użytkownik wykonujący aktualizację nie potrzebuje żadnych uprawnień do bazowych relacji bazowych (patrz punkt 38.5 ).

Wyzwalacze też są wyjątkowe. Potrzebujesz TRIGGERprzywileju na stole i:

• Jakie uprawnienia są wymagane do wykonania funkcji wyzwalacza w PostgreSQL 8.4?

Ale już nadmiernie rozszerzamy zakres tego pytania ...

Ważne notatki

Własność

Jeśli chcesz pozwolić schma_admin(samemu) na upuszczanie i modyfikowanie tabel, ustaw rolę jako własność wszystkich obiektów. Dokumentacja:

Prawo do upuszczenia przedmiotu lub zmiany jego definicji w jakikolwiek sposób nie jest traktowane jako przyznany przywilej; jest nieodłączną cechą właściciela i nie może zostać przyznane ani odwołane. (Jednak podobny efekt można uzyskać, przyznając lub odwołując członkostwo w roli, która jest właścicielem obiektu; patrz poniżej.) Właściciel domyślnie ma również wszystkie opcje przyznania dla obiektu.

ALTER TABLE some_tbl OWNER TO schma_admin;

Lub utwórz wszystkie obiekty z roląschma_adminna początek, wtedy nie musisz jawnie ustawiać właściciela. Upraszcza to także domyślne uprawnienia, które należy ustawić tylko dla jednej roli:

Wcześniej istniejące obiekty

Domyślne uprawnienia dotyczą tylko nowo utworzonych obiektów i tylko dla określonej roli, w której zostały utworzone. Będziesz także chciał dostosować uprawnienia do istniejących obiektów:

• Odmowa zgody na relację <tabela>

To samo dotyczy tworzenia obiektów z nieokreśloną rolą DEFAULT PRIVILEGES, takich jak administrator postgres. Przypisz do własności schma_admini ustawić uprawnienia ręcznie - lub zestaw DEFAULT PRIVILEGESdo postgresjak również (gdy podłączony do prawego DB!):

ALTER DEFAULT PRIVILEGES FOR ROLE postgres GRANT ...  -- etc.

Domyślne uprawnienia

Brakowało ważnego aspektu ALTER DEFAULT PRIVILEGESpolecenia. Ma zastosowanie do bieżącej roli, chyba że określono inaczej:

• Nie można zmienić domyślnych uprawnień

Domyślne uprawnienia dotyczą tylko bieżącej bazy danych. Więc nie zadzieraj z innymi bazami danych w klastrze DB. Dokumentacja:

dla wszystkich obiektów utworzonych w bieżącej bazie danych

Państwo może również chcesz ustawić domyślne przywilejów FUNCTIONSi TYPES(nie tylko TABLESa SEQUENCES), ale te nie mogą być potrzebne.

Domyślne uprawnienia dla PUBLIC

Domyślne przywileje przyznane PUBLICsą przez niektórych szczątkowe i zawyżone. Dokumentacja:

PostgreSQL przyznaje domyślne uprawnienia do niektórych typów obiektów PUBLIC. Domyślnie nie przyznaje się żadnych uprawnień do PUBLICtabel, kolumn, schematów ani obszarów tabel. W przypadku innych typów, przywileje przyznane domyślne PUBLICsą następujące: CONNECTa CREATE TEMP TABLEdla baz danych; EXECUTEuprawnienie do funkcji; i USAGE przywilej dla języków.

Odważny nacisk moje. zazwyczaj jedno z powyższych poleceń wystarcza, aby objąć wszystko:

REVOKE ALL ON DATABASE hostdb FROM public;

W szczególności nie przyznaje się żadnych domyślnych uprawnień do PUBLICnowych schematów. Może być mylące, że domyślny schemat o nazwie „public” zaczyna się od ALLuprawnień dla PUBLIC. To tylko funkcja ułatwiająca rozpoczęcie pracy z nowo utworzonymi bazami danych. Nie wpływa w żaden sposób na inne schematy. Państwo może unieważnić te przywileje w bazie szablonu template1, a następnie wszystkich nowo tworzonych baz danych w tej grupie zacząć bez nich:

\connect template1
REVOKE ALL ON SCHEMA public FROM public;

Przywilej TEMP

Ponieważ cofnęliśmy wszystkie uprawnienia hostdbz PUBLIC, zwykli użytkownicy nie mogą tworzyć tabel tymczasowych, chyba że wyraźnie na to zezwalamy. Możesz lub nie chcesz dodać tego:

GRANT TEMP ON DATABASE hostdb TO schma_mgr;

search_path

Nie zapomnij ustawić search_path. Jeśli masz tylko jedną bazę danych w klastrze, możesz po prostu ustawić globalną wartość domyślną w postgresql.conf. W przeciwnym razie (bardziej prawdopodobne) ustaw ją jako właściwość bazy danych lub po prostu dla zaangażowanych ról lub nawet kombinacji obu. Detale:

• W jaki sposób ścieżka szukania wpływa na rozdzielczość identyfikatora i „bieżący schemat”

Możesz to ustawić, schma, publicjeśli używasz również schematu publicznego, a nawet (mniej prawdopodobne) $user, schma, public...

Alternatywą byłoby użycie domyślnego schematu „public”, który powinien działać z ustawieniami domyślnymi, search_pathchyba że to zmieniłeś. Pamiętaj, aby PUBLICw tym przypadku cofnąć uprawnienia .

Związane z

• Przyznaj uprawnienia do konkretnej bazy danych w PostgreSQL
• PostgreSQL - użytkownik DB powinien mieć możliwość wywoływania funkcji