Nadaj wszystkie uprawnienia użytkownikowi w bazie danych


206

Chciałbym dać użytkownikowi wszystkie uprawnienia do bazy danych, nie czyniąc go administratorem. Powodem, dla którego chcę to zrobić, jest to, że w tej chwili DEV i PROD są różnymi bazami danych w tym samym klastrze, więc nie chcę, aby użytkownik mógł zmieniać obiekty produkcyjne, ale musi mieć możliwość zmiany obiektów w DEV.

Próbowałem:

grant ALL on database MY_DB to group MY_GROUP;

ale wydaje się, że nie daje to żadnego pozwolenia.

Potem spróbowałem:

grant all privileges on schema MY_SCHEMA to group MY_GROUP;

i wydaje mi się, że daje mi pozwolenie na tworzenie obiektów, ale nie na zapytanie \ usuwanie obiektów na tym schemacie, które należą do innych użytkowników

Mógłbym kontynuować, udzielając użytkownikowi USAGE pozwolenia na MY_SCHEMA, ale wtedy narzekałoby na brak uprawnień na stole ...

Wydaje mi się, że moje pytanie brzmi: czy jest jakiś prosty sposób na udzielenie wszystkich uprawnień użytkownikowi bazy danych?

Pracuję nad PostgreSQL 8.1.23.

Odpowiedzi:


271

Użytkownik potrzebuje dostępu do bazy danych , oczywiście:

GRANT CONNECT ON DATABASE my_db TO my_user;

I (przynajmniej) USAGEprzywilej na schemacie :

GRANT USAGE ON SCHEMA public TO my_user;

Lub udzielić USAGEna wszystkich niestandardowych schematów:

DO
$$
BEGIN
   -- RAISE NOTICE '%', (  -- use instead of EXECUTE to see generated commands
   EXECUTE (
   SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ')
   FROM   pg_namespace
   WHERE  nspname <> 'information_schema' -- exclude information schema and ...
   AND    nspname NOT LIKE 'pg\_%'        -- ... system schemas
   );
END
$$;

Następnie wszystkie uprawnienia dla wszystkich tabel (wymaga Postgres 9.0 lub nowszego).
I nie zapomnij o sekwencjach (jeśli istnieją):

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;

W przypadku starszych wersji można użyć „Grant Wizard” programu pgAdmin III (domyślny GUI).

Istnieje kilka innych obiektów, instrukcja dlaGRANT ma pełną listę od Postgres 12:

uprawnienia do obiektu bazy danych (tabela, kolumna, widok, tabela obca, sekwencja, baza danych, opakowanie danych obcych, serwer obcy, funkcja, procedura, język proceduralny, schemat lub przestrzeń tabel)

Ale reszta jest rzadko potrzebna. Więcej szczegółów:

Rozważ uaktualnienie do bieżącej wersji .


133
GRANT ALL PRIVILEGES ON DATABASE "my_db" to my_user;

24
Przyznanie wszystkich przywilejów ON DATABASEwydaje się potężne, ale niewiele zdziała. To tylko początek. Nie przyznaje żadnych uprawnień do zawartych obiektów.
Erwin Brandstetter,

50

W PostgreSQL 9.0+ możesz wykonać następujące czynności:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA MY_SCHEMA TO MY_GROUP;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA MY_SCHEMA TO MY_GROUP;

Jeśli chcesz włączyć to również dla nowo tworzonych relacji, ustaw domyślne uprawnienia:

ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
  GRANT ALL PRIVILEGES ON TABLES TO MY_GROUP;
ALTER DEFAULT PRIVILEGES IN SCHEMA MY_SCHEMA
  GRANT ALL PRIVILEGES ON SEQUENCES TO MY_GROUP;

Jednak widząc, że używasz wersji 8.1, musisz sam go kodować:

CREATE FUNCTION grant_all_in_schema (schname name, grant_to name) RETURNS integer AS $$
DECLARE
  rel RECORD;
BEGIN
  FOR rel IN
    SELECT c.relname
    FROM pg_class c
    JOIN pg_namespace s ON c.namespace = s.oid
    WHERE s.nspname = schname
  LOOP
    EXECUTE 'GRANT ALL PRIVILEGES ON ' || quote_ident(schname) || '.' || rel.relname || ' TO ' || quote_ident(grant_to);
  END LOOP;
  RETURN 1;
END; $$ LANGUAGE plpgsql STRICT;
REVOKE ALL ON FUNCTION grant_all_in_schema(name, name) FROM PUBLIC;

Spowoduje to ustawienie uprawnień do wszystkich relacji: tabel, widoków, indeksów, sekwencji itp. Jeśli chcesz to ograniczyć, filtruj pg_class.relkind. Szczegółowe informacje można znaleźć w dokumentacji pg_class .

Powinieneś uruchomić tę funkcję jako superużytkownik i tak regularnie, jak wymaga tego twoja aplikacja. Opcją byłoby spakowanie tego w zadaniu cron, które jest wykonywane codziennie lub co godzinę.


Cześć Patrick, „WSZYSTKIE TABELE” nie są dostępne w wersji 8.1 ( postgresql.org/docs/8.1/static/sql-grant.html ) Wiem, że mogłem przeglądać tabele i udzielać uprawnień indywidualnie, ale właśnie to próbując uniknąć. ale dziękuję za pomoc
Diego

@Diego: Dodano rozwiązanie dla wersji 8.1
Patrick

dzięki Patrick, skończyło się na tym, że użyłem czegoś takiego jak ty, ale nie użyłem „GRANT ALL”. Z jakiegoś powodu wydaje się, że nic nie robi. Na przykład uruchomiłem: udziel ALL na test schematu użytkownikowi A; ale po tym użytkowniku nadal nie ma dostępu do odczytu z tabel testu testu
Diego

2
Powinieneś UDZIELIĆ UŻYCIA na schemacie. Następnie we wszystkich relacjach w ramach tego schematu (tabele, widoki, sekwencje, indeksy itp.) Musisz UDZIELIĆ WYBIERAĆ, WSTAWIAĆ, AKTUALIZOWAĆ, USUWAĆ, TRUNCATE oddzielnie. Schematy to przestrzenie nazw, relacje są tam, gdzie znajdują się twoje dane.
Patrick

28

Wykonałem następujące czynności, aby dodać rolę „eSumit” w bazie danych PostgreSQL 9.4.15 i wyrazić zgodę na tę rolę:

CREATE ROLE eSumit;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO eSumit;

GRANT ALL PRIVILEGES ON DATABASE "postgres" to eSumit;

ALTER USER eSumit WITH SUPERUSER;

Sprawdzono także wpisy pg_table poprzez:

wybierz * z pg_roles; wprowadź opis zdjęcia tutaj

Migawka zapytań do bazy danych: wprowadź opis zdjęcia tutaj


Mam wersję Postgres 10.3 i generuje błąd składniowy, gdy używam cudzysłowów wokół nazwy bazy danych.
sajid
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.