UZYSKAĆ ​​UŻYCIE we wszystkich schematach w bazie danych?

Chcę do GRANT USAGEużytkownika / roli dla danej bazy danych. Baza danych ma wiele schematów.

Wiem, że istnieje ON ALL TABLES IN SCHEMA, ale chcę „wszystkich schematów”. Próbowałem GRANT USAGE .. ON DATABASE, ale to oczywiście źle (tak naprawdę nie istnieje ).

Dotyczy to Postgres 9.3 lub 9.4, zdarza się, że jest to serwer działający na AWS RDS.

Masz co najmniej dwie opcje.

Pierwszy wykorzystuje małe zapytanie i edytor tekstu. Musimy zebrać schematy naszego zainteresowania:

SELECT nspname
  FROM pg_namespace;

Możesz dodać WHEREklauzulę, jeśli chcesz ograniczyć zakres. Skopiuj dane wyjściowe i popraw je, aby uzyskać szereg GRANT USAGE ON SCHEMA ... TO your_role;poleceń. Następnie po prostu nakarm go psql, na przykład:

psql -f multigrant.sql

Typowym wariantem tego może być skrypt powłoki, który zapętla zebrane nazwy i wywołania psql, przekazując zbudowaną GRANTinstrukcję do -copcji.

Drugie rozwiązanie robi to samo w jednym bloku pl / pgsql, tworząc dynamiczne zapytanie. Rdzeń jest taki sam - musimy zebrać schematy. Następnie zapętlamy je wszystkie, przyznając schemat uprawnień według schematu:

DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO your_role $$, sch);
    END LOOP;
END;
$do$;

Uwagi :

• w przeciwieństwie do tabel, sekwencji, funkcji i typów, nie można ustawiać domyślnych uprawnień dla schematów (od 9.4). Będziesz musiał przyznać to uprawnienie dla każdego nowo dodanego schematu ręcznie.
• tutaj używam cytowania dolara podczas tworzenia zapytania dynamicznego. Pozwala mi to na użycie „normalnej” składni, w przeciwieństwie do mnożenia pojedynczych cudzysłowów, na przykład (nieobecnych w tym przykładzie). W ten sposób większość redaktorów ładnie podświetli stwierdzenia.
• Używam również format()ze specyfikatorem %Iformatu, aby w razie potrzeby poprawnie podać nazwę obiektu. To podejście jest znacznie bardziej czytelne niż budowanie zapytania z konkatenacją stałych łańcuchowych i niektórych quote_ident()wywołań.
• pg_namespacemożna znaleźć w pg_catalogschemacie. Sprawdź inne znajdujące się tam obiekty - przechowują one każdy aspekt twoich schematów, tabel i tak dalej.

Ty też możesz użyć.

DO $do$
DECLARE
    sch text;
BEGIN
    FOR sch IN SELECT nspname FROM pg_namespace where nspname != 'pg_toast' 
    and nspname != 'pg_temp_1' and nspname != 'pg_toast_temp_1'
    and nspname != 'pg_statistic' and nspname != 'pg_catalog'
    and nspname != 'information_schema'
    LOOP
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO your_role $$, sch);
        EXECUTE format($$ GRANT USAGE ON SCHEMA %I to your_role $$, sch);
        EXECUTE format($$ GRANT SELECT ON ALL SEQUENCES IN SCHEMA %I TO your_role $$, sch);
        EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO backup_user $$, sch);

        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON TABLES TO your_role $$, sch);
        EXECUTE format($$ ALTER DEFAULT PRIVILEGES IN SCHEMA %I GRANT SELECT ON SEQUENCES TO your_role $$, sch);
    END LOOP;
END;
$do$;