Dlaczego nowy użytkownik może utworzyć tabelę?

Zastanawiam się, dlaczego nowo utworzony użytkownik może utworzyć tabelę po połączeniu z bazą danych. Mam jedną bazę danych project2_core:

postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges       
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           | 
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

Na razie w porządku. Teraz tworzę użytkownika:

postgres=# CREATE ROLE dietrich ENCRYPTED PASSWORD 'md5XXX' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER

W porządku. Kiedy próbuję połączyć się z bazą danych, użytkownik nie może tego zrobić:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich: 
psql: FATAL:  permission denied for database "project2_core"
DETAIL:  User does not have CONNECT privilege.

Tego się spodziewałem. Teraz zaczynają się dziwne rzeczy. Przyznaję użytkownikowi CONNECT:

postgres=# GRANT CONNECT ON DATABASE project2_core TO dietrich;
GRANT
postgres=# \l
                                          List of databases
     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges       
---------------+--------------+-----------+-------------+-------------+-------------------------------
 postgres      | postgres     | SQL_ASCII | C           | C           | 
 project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2+
               |              |           |             |             | dietrich=c/project2
 template0     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
 template1     | postgres     | SQL_ASCII | C           | C           | =c/postgres                  +
               |              |           |             |             | postgres=CTc/postgres
(5 rows)

I bez dalszych dotacji użytkownik może utworzyć tabelę:

$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich: 
psql (9.2.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

project2_core=> create table adsf ();
CREATE TABLE
project2_core=> \d
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | adsf | table | dietrich
(1 row)

Spodziewałbym się, że użytkownik nie będzie mógł nic zrobić, zanim jawnie zrobię to GRANT USAGEna schemacie, a następnie GRANT SELECTna tabelach.

Gdzie jest mój błąd? Co ja robię źle? Jak mogę osiągnąć to, czego chcę (aby nowy użytkownik nie mógł nic robić, zanim wyraźnie nada jej odpowiednie uprawnienia.

Zgubiłem się, a twoja pomoc jest bardzo mile widziana :)

EDYCJA Zgodnie z radą @ daniel-verite teraz odwołuję wszystko natychmiast po utworzeniu bazy danych. Dietrich użytkownika nie może już tworzyć tabeli. Dobry. ALE : Teraz także właściciel bazy danych, projekt2 , nie może tworzyć tabeli. Nawet po wydaniu GRANT ALL PRIVILEGES ON DATABASE project2_core TO project2i pojawiaGRANT ALL PRIVILEGES ON SCHEMA public TO project2 się błąd BŁĄD: nie wybrano żadnego schematu do utworzenia , a gdy próbuję CREATE TABLE public.WHATEVER ();, otrzymuję BŁĄD: odmowa dostępu do schematu publicznie . Co ja robię źle?

Podczas tworzenia nowej bazy danych dowolna rola może tworzyć obiekty w publicschemacie. Aby usunąć tę możliwość, możesz wydać natychmiast po utworzeniu bazy danych:

REVOKE ALL ON schema public FROM public;

Edycja: po powyższym poleceniu tylko superużytkownik może tworzyć nowe obiekty w publicschemacie, co nie jest praktyczne. Zakładając, że nieupoważniony użytkownik foo_userpowinien otrzymać ten przywilej, należy to zrobić za pomocą:

GRANT ALL ON schema public TO foo_user;

Aby wiedzieć, co ALLoznacza schemat, musimy odwołać się do GRANT w dokumencie (w PG 9.2 istnieje nie mniej niż 14 form instrukcji GRANT, które dotyczą różnych rzeczy ...). Wydaje się, że dla schematu oznacza to CREATEi USAGE.

Z drugiej strony, GRANT ALL PRIVILEGES ON DATABASE...przyzna CONNECTi CREATEi TEMP, ale CREATEw tym kontekście dotyczy schematów, a nie stałych tabel.

Odnośnie tego błędu: ERROR: no schema has been selected to create inzdarza się, gdy próbuje się utworzyć obiekt bez kwalifikacji schematu (jak w create table foo(...)), ale nie ma uprawnień do utworzenia go w dowolnym schemacie search_path.

Kluczową rzeczą do zrozumienia w tym przypadku jest to, że przywileje nie są dziedziczne i nie są dziedziczone z przechowywania obiektów . ALLoznacza wszystkie uprawnienia do tego obiektu, nie wszystkie uprawnienia do tego obiektu i wszystkich zawartych obiektów .

Kiedy udzielasz ALLbazy danych, udzielasz CREATE, CONNECT, TEMP. Są to działania na obiekcie bazy danych:

• CONNECT: Połącz z bazą danych
• CREATE: Utwórz schemat ( nie tabelę)
• TEMP: Twórz obiekty tymczasowe, w tym między innymi tabele tymczasowe

Teraz każda baza danych PostgreSQL domyślnie ma publicschemat, który jest tworzony podczas tworzenia bazy danych. Ten schemat ma wszelkie prawa przyznane roli public, której każdy jest niejawnie członkiem. Dla schematu ALLoznacza CREATE, USAGE:

• CREATE: Twórz obiekty (w tym tabele) w tym schemacie
• USAGE: Wyświetl listę obiektów w schemacie i uzyskaj do nich dostęp, jeśli pozwalają na to ich uprawnienia

Jeśli nie określisz schematu, aby utworzyć obiekt, taki jak tabela, aparat bazy danych użyje search_pathtego publicschematu , a domyślnie schemat będzie pierwszy w search_pathtabeli, więc tabela zostanie tam utworzona. Każdy ma publicdomyślnie prawa , więc tworzenie jest dozwolone. Prawa użytkowników do bazy danych są w tym momencie nieistotne, ponieważ użytkownik nie próbuje nic zrobić z obiektem bazy danych, tylko schematem.

Nie ma znaczenia, że ​​nie przyznałeś użytkownikowi żadnych praw innych niż przyznanie CONNECTbazy danych, ponieważ publicschemat domyślnie zezwala wszystkim użytkownikom na tworzenie tabel w nim. Daniel wyjaśnił już, jak w razie potrzeby odwołać to prawo.

Jeśli chcesz jednoznacznie przekazać wszystkie prawa, odwołaj wszystkie z publicznych lub po prostu upuść publiczny schemat. Możesz utworzyć nową bazę danych szablonów z zastosowaniem tej zmiany, jeśli chcesz. Alternatywnie możesz go zastosować template1, ale najprawdopodobniej zepsuje to wiele kodu innej firmy, który zakłada, że publicistnieje i jest możliwy do zapisu.

Może to mieć większy sens, jeśli spojrzysz na analogię systemu plików.

Jeśli mam strukturę katalogu (tryb uproszczony, aby pokazać tylko tryb, który dotyczy bieżącego użytkownika):

/dir1           mode=r-x
/dir1/dir2      mode=rwx

wtedy nie mogę niczego stworzyć /dir1, ponieważ nie mam uprawnień do zapisu. Więc jeśli touch /dir1/somefiledostanę błąd odmowy pozwolenia.

Ja jednak zrobić masz uprawnień do zajrzeć do środka /dir1i dostępu do plików i katalogów zawartych, w tym /dir1/dir2. Mam pozwolenie na pisanie w dniu dir2. Tak touch /dir1/dir2/somefilesię powiedzie , chociaż nie mam uprawnień do zapisu dir1.

To samo dotyczy baz danych i schematów.

Jeśli chcesz tylko uniemożliwić nowym użytkownikom tworzenie tabel, musisz uruchomić następujące polecenie:

REVOKE CREATE ON SCHEMA public FROM public;

Jeśli REVOKE ALL(jak sugerują inne odpowiedzi), uniemożliwisz również użytkownikom posiadanie USAGEuprawnień. USAGEoznacza, że ​​użytkownicy mogą korzystać z przypisanych im uprawnień, więc jeśli je usuniesz, użytkownicy nie będą mogli wyświetlać ani uzyskiwać dostępu do tabel, do których mają dostęp.

Alternatywnie możesz również REVOKE CREATEdla konkretnego użytkownika:

REVOKE CREATE ON schema public FROM myuser;

Zobacz także: Jak utworzyć użytkownika tylko do odczytu za pomocą PostgreSQL .