Предоставить все в определенной схеме в db для групповой роли в PostgreSQL

Используя PostgreSQL 9.0, у меня есть групповая роль, называемая "staff", и я хочу предоставить все (или определенные) привилегии этой роли для таблиц в конкретной схеме. Ни одна из следующих работ

GRANT ALL ON SCHEMA foo TO staff;
GRANT ALL ON DATABASE mydb TO staff;

Члены "персонала" по-прежнему не могут выполнить SELECT или UPDATE в отдельных таблицах в схеме "foo" или (в случае второй команды) в любую таблицу в базе данных, если я не предоставил все в этой конкретной таблице.

Что я могу сделать, чтобы облегчить жизнь моих и моих пользователей?

Обновление: Выяснено с помощью похожего вопроса на serverfault.com.

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA foo TO staff;

Ответ 1

Вы нашли стенографию для установки привилегий для всех существующих таблиц в данной схеме. Руководство разъясняет:

(но обратите внимание, что ALL TABLES считается включающим представления и внешние таблицы).

Смелый акцент мой. столбцы serial реализованы с nextval() в последовательности как по умолчанию столбца, а цитирует руководство:

Для последовательностей эта привилегия позволяет использовать функции currval и nextval.

Итак, если есть столбцы serial, вы также захотите предоставить USAGE (или ALL PRIVILEGES) в последовательностях

GRANT USAGE ON ALL SEQUENCES IN SCHEMA foo TO mygrp;

Примечание: столбцы идентификации в Postgres 10 или более поздних версиях используют неявные последовательности, которые не требуют дополнительных привилегий. (Рассмотрите возможность обновления столбцов serial.)

Что относительно новых объектов?

Вас также будет интересовать DEFAULT PRIVILEGES для пользователей или схем:

ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT ALL PRIVILEGES ON TABLES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo GRANT USAGE          ON SEQUENCES TO staff;
ALTER DEFAULT PRIVILEGES IN SCHEMA foo REVOKE ...;

Это автоматически устанавливает привилегии для объектов, созданных в будущем, но не для ранее существовавших объектов.

По умолчанию привилегии применяются только к объектам, созданным целевым пользователем (FOR ROLE my_creating_role). Если это предложение опущено, по умолчанию используется текущий пользователь, выполняющий ALTER DEFAULT PRIVILEGES. Чтобы быть явным:

ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo GRANT ...;
ALTER DEFAULT PRIVILEGES FOR ROLE my_creating_role IN SCHEMA foo REVOKE ...;

Обратите внимание также, что все версии pgAdmin III имеют тонкую ошибку и отображают привилегии по умолчанию на панели SQL, даже если они не применяются к текущей роли. Обязательно настройте предложение FOR ROLE вручную при копировании SQL script.

Ответ 2

Мой ответ аналогичен этому на сервере ServerFault.com.

Быть консервативным

Если вы хотите быть более консервативным, чем предоставлять "все привилегии", возможно, вам захочется попробовать что-то подобное.

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO some_user_;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO some_user_;

Использование public означает имя схемы по умолчанию, созданной для каждой новой базы данных/каталога. Замените свое имя, если вы создали схему.

Доступ к схеме

Чтобы получить доступ к схеме вообще, для любого действия пользователю должны быть предоставлены права использования. Прежде чем пользователь сможет выбрать, вставить, обновить или удалить, пользователю сначала должно быть предоставлено "использование" для схемы.

Вы не заметите этого требования при первом использовании Postgres. По умолчанию каждая база данных имеет первую схему с именем public. И каждый пользователь по умолчанию автоматически получает права использования на эту конкретную схему. При добавлении дополнительной схемы вы должны явно предоставить права использования.

GRANT USAGE ON SCHEMA some_schema_ TO some_user_ ;

Выдержка из Postgres doc:

Для схем допускается доступ к объектам, содержащимся в указанной схеме (при условии, что требования к собственной привилегии объектов также выполняются). По сути, это позволяет получателю "искать" объекты внутри схемы. Без этого разрешения все еще можно увидеть имена объектов, например. путем запроса системных таблиц. Кроме того, после отзыва этого разрешения существующие бэкэнд могут иметь инструкции, которые ранее выполняли этот поиск, поэтому это не полностью безопасный способ предотвращения доступа к объектам.

Для более подробного обсуждения см. вопрос, Что такое GRANT USAGE ON SCHEMA? Обратите особое внимание на ответ экспертом Postgres Craig Ringer.

Существующие объекты в сравнении с будущим

Эти команды влияют только на существующие объекты. Таблицы, которые вы создаете в будущем, получают привилегии по умолчанию, пока вы не выполните повторное выполнение этих строк. См. другой ответ Erwin Brandstetter, чтобы изменить значения по умолчанию, тем самым повлияв на будущие объекты.