Przyznanie dostępu do wszystkich tabel dla użytkownika

Jestem nowy w Postgres i próbuję migrować nasze bazy danych MySQL. W MySQL mogę przyznać SELECT, UPDATE, INSERToraz DELETEprzywilejów na niskim uprzywilejowanego użytkownika i umożliwiają tych dotacji stosuje się do wszystkich tabel w określonej bazie danych. Coś mi brakuje w Postgresie, ponieważ wygląda na to, że muszę przyznać te uprawnienia dla każdego stołu pojedynczo. Z wieloma bazami danych i setkami tabel na bazę danych, co wydaje się być trudnym zadaniem, aby po prostu zacząć od podstaw. Ponadto, gdy baza danych działa, dodawanie tabel odbywa się na tyle często, że nie musiałbym przyznawać uprawnień za każdym razem, chyba że jest to absolutnie konieczne.

Jak można to najlepiej osiągnąć?

Po pierwsze, musisz być w stanie połączyć się z bazą danych, aby uruchomić zapytania. Można to osiągnąć przez

REVOKE CONNECT ON DATABASE your_database FROM PUBLIC;

GRANT CONNECT
ON DATABASE database_name 
TO user_name;

Jest REVOKEto konieczne, ponieważ

Słowo kluczowe PUBLIC wskazuje, że uprawnienia należy przyznać wszystkim rolom, w tym także tym, które mogą zostać utworzone później. PUBLIC można traktować jako domyślnie zdefiniowaną grupę, która zawsze obejmuje wszystkie role. Każda konkretna rola będzie miała sumę przywilejów przyznanych bezpośrednio jej, przywilejów przyznanych każdej roli, której jest obecnie członkiem, oraz przywilejów przyznanych PUBLIC.

Jeśli naprawdę chcesz ograniczyć użytkownika do instrukcji DML, musisz zrobić coś więcej:

REVOKE ALL
ON ALL TABLES IN SCHEMA public 
FROM PUBLIC;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public 
TO user_name;

Zakładają one, że będziesz mieć tylko jeden schemat (domyślnie nazywany „publicznym”).

Jak zauważył Jack Douglas, powyższe daje uprawnienia tylko dla już istniejących tabel. Aby osiągnąć to samo w przyszłych tabelach, musisz zdefiniować domyślne uprawnienia :

ALTER DEFAULT PRIVILEGES 
    FOR ROLE some_role   -- Alternatively "FOR USER"
    IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

Tutaj some_rolejest rola, która tworzy tabele, podczas gdy user_nameto ta, która otrzymuje uprawnienia. Aby to zdefiniować, musisz być zalogowany jako some_roleczłonek lub członek.

I w końcu musisz zrobić to samo dla sekwencji (dzięki PlaidFan za wskazanie tego) - tutaj jest USAGEprzywilej, którego potrzebujesz.

zakładając, że chcesz dać im wszystkie uprawnienia - zrób to:

grant all privileges on database dbname to dbuser;

gdzie dbnamejest nazwą twojej bazy danych i dbuserjest nazwą użytkownika.

Przyznanie wszystkich uprawnień do wszystkich tabel w bazie danych jest osiągane za pomocą

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO <username>;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <schema_name> TO <username>;

Możliwe, że robiłem tutaj coś złego, ponieważ jestem bardzo nowy w PostgreSQL. Ale to tylko rozwiązało dla mnie pierwszą część problemu - ustawienie uprawnień na wszystkich istniejących tabelach.

Aby uprawnienia były poprawnie ustawione dla mojego użytkownika na nowe tabele, które są tworzone, muszę ustawić domyślne uprawnienia dla użytkownika:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON tables TO user_name;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, USAGE ON sequences TO user_name;

--Create User

CREATE USER my_user_test WITH LOGIN NOSUPERUSER NOCREATEDB  NOCREATEROLE    INHERIT NOREPLICATION   CONNECTION LIMIT -1 PASSWORD 'xxxxxxx';

-- Grant connect to my data base

GRANT CONNECT ON DATABASE my_db_test TO my_user_test;

-- Grant usage the schema

GRANT USAGE ON SCHEMA my_sch_test TO my_user_test ;

-- Grant all table for SELECT, INSERT, UPDATE, DELETE

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA my_sch_test TO my_user_test;