Предоставить привилегии для конкретной базы данных в PostgreSQL

Ответ 1

Основная концепция в Postgres

Роли - это глобальные объекты, которые могут обращаться ко всем базам данных в кластере db - с учетом требуемых привилегий.

В кластере хранится множество баз данных, которые содержат множество схем. Схемы (даже с тем же именем) в разных БД не связаны. Предоставление привилегий для схемы применяется только к этой конкретной схеме в текущей БД (текущая БД на момент предоставления).

Каждая база данных начинается со схемы public по умолчанию. Это соглашение, и многие настройки начинаются с него. Кроме того, схема public является просто схемой, подобной любой другой.

Исходя из MySQL, вы можете начать с единой схемы public, фактически полностью игнорируя уровень схемы. Я регулярно использую десятки схем для каждой базы данных.
Схемы немного (но не полностью), как каталоги в файловой системе.

Как только вы используете несколько схем, не забудьте понять search_path:

• Как влияет на идентификатор идентификатора поиска и "текущая схема"

привилегии по умолчанию

В документации по GRANT:

PostgreSQL предоставляет привилегии по умолчанию для некоторых типов объектов public. По умолчанию для таблиц нет привилегий publicстолбцов, схем или табличных пространств. Для других типов значение по умолчанию привилегии, предоставленные public, следующие: CONNECT и CREATE TEMP TABLEдля баз данных; EXECUTE привилегия для функций; и USAGE привилегии для языков.

Все эти значения по умолчанию можно изменить с помощью ALTER DEFAULT PRIVILEGES:

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

Групповая роль

Как @Craig прокомментировал, лучше всего использовать GRANT привилегии для групповой роли, а затем создать конкретный пользовательский член этой роли (GRANT роль группы к роли пользователя). таким образом, проще разобраться и отменить пакеты привилегий, необходимых для определенных задач.

Роль группы - это еще одна роль без входа в систему. Добавьте логин, чтобы преобразовать его в роль пользователя. Подробнее:

• Почему PostgreSQL объединил пользователей и группы в роли?

Рецепт

Скажем, у нас есть новая база данных mydb, группа mygrp и пользователь myusr...

При подключении к рассматриваемой базе данных в качестве суперпользователя (например, postgres):

REVOKE ALL ON DATABASE mydb FROM public;  -- shut out the general public
GRANT CONNECT ON DATABASE mydb TO mygrp;  -- since we revoked from public

GRANT USAGE ON SCHEMA public TO mygrp;

Назначить a user all privileges to all tables, как вы писали (я мог бы быть более ограничительным):

GRANT ALL ON ALL TABLES IN SCHEMA public TO mygrp;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO mygrp; -- don't forget those

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

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON TABLES TO mygrp;

ALTER DEFAULT PRIVILEGES FOR ROLE myusr IN SCHEMA public
GRANT ALL ON SEQUENCES TO mygrp;

-- more roles?

Теперь выделите группу пользователю:

GRANT mygrp TO myusr;

Связанный ответ:

• PostgreSQL - пользователю БД должно разрешаться только функции вызова

Альтернативная (нестандартная) настройка

Исходя из MySQL, и поскольку вы хотите сохранить привилегии для разделенных баз данных, вам может понравиться эта нестандартная настройка db_user_namespace. В документации:

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

Внимательно прочитайте руководство. Я не использую эту настройку. Это не отменяет вышеуказанного.

Ответ 2

Может быть, вы могли бы привести мне пример, который предоставляет конкретному пользователю выбрать/вставить/обновить/удалить на всех таблицах - существующие и не но созданный - конкретной базы данных?

То, что вы называете базой данных в MySQL, более близко напоминает схему PostgreSQL, чем базу данных PostgreSQL.

Подключитесь к базе данных "test" как суперпользователь. Вот это

$ psql -U postgres test

Изменить привилегии по умолчанию для существующего "тестера" пользователя.

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT INSERT, SELECT, UPDATE, DELETE ON TABLES
    TO tester;

Изменение привилегий по умолчанию не влияет на существующие таблицы. Это по дизайну. Для существующих таблиц используйте стандартный синтаксис GRANT и REVOKE.

Вы не можете назначать привилегии для пользователя, который не существует.

Ответ 3

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

OK. Когда вы назначаете таблицы правильной роли, предоставленные привилегии будут ролевыми, а не для всех пользователей! Затем вы можете решить, кому отдать roles.

• Создайте role для каждой базы данных. Роль может содержать много пользователей.
• Затем назначьте client-username правильную роль.
• Также назначьте your-username для каждой роли, если это необходимо.

(1a) Не все таблицы уже созданы, или (1b) таблицы уже созданы.

OK. Вы можете создавать таблицы позже.
Когда вы будете готовы, назначьте таблицы правильному клиенту role.

CREATE TABLE tablename();
CREATE ROLE rolename;
ALTER TABLE tablename OWNER TO rolename;

(2a) Пользователь еще не создан или (2b) пользователь уже создан.

OK. Создавайте имена пользователей, когда будете готовы. Если вашему клиенту требуется более одного имени пользователя, просто создайте второй client-username.

CREATE USER username1;
CREATE USER username2;

(3a) Привилегии еще не назначены пользователю, или (3b) привилегии ранее были назначены пользователю.

OK. Когда вы будете готовы предоставить привилегии, создайте пользователя и назначьте ей правильную роль.
Используйте команду GRANT-TO для назначения ролей пользователям.

GRANT rolename TO username1;
GRANT rolename TO username2;

(4a) Пользователю нужно только вставлять, обновлять, выбирать и удалять строки или (4b), пользователь также должен иметь возможность создавать и удалять таблицы.

OK. Вы выполняете эти команды для добавления разрешений вашим пользователям.

GRANT SELECT, UPDATE, INSERT, DELETE ON dbname TO role-or-user-name;
ALTER USER username1 CREATEDB;

Ответ 4

Вы можете забыть о схеме, если используете только PUBLIC. Затем вы делаете что-то вроде этого: (см. Документ здесь)

GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
    [, ...] | ALL [ PRIVILEGES ] }
    ON { [ TABLE ] table_name [, ...]
         | ALL TABLES IN SCHEMA schema_name [, ...] }
    TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]