PostgreSQL: создание пользователей для чтения через DBeaver

Если бы задача стояла предоставить пользователям доступ на чтение к определенной базе в MySQL, то данный текст можно было бы и не писать. Но в случае с PSQL всё обстоит иначе, т.к. способы добавления пользователей всегда взрывают мозг, поэтому решил записать данный способ, который кажется мне самым удобным.

DBeaver – удобный опенсорсный инструмент для подключения к различным видам СУБД, также называемый “бобёр”. Его я буду использовать для подключения к серверу PostgreSQL 9.5.

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

Порядок действий:

Подключиться “бобром” к нужному серверу PSQL и выбрать активную (выделена жирным текстом) базу postgres:

Создать группу (по сути – роль) и предоставить ей доступ для подключения в нужные базы, используя редактор SQL:

CREATE ROLE live_read_only_group;
GRANT CONNECT ON DATABASE db_name1 TO live_read_only_group;
GRANT CONNECT ON DATABASE db_name2 TO live_read_only_group;

И запустить выполнение скрипта, нажав кнопку, выделенную на рисунке. Иначе если нажать просто Play, будет выполняться одна строчка, перед которой стоит курсор:

Далее создаётся роль пользователя и добавляется в созданную роль (группу) для чтения:

CREATE ROLE read_user_name WITH LOGIN ENCRYPTED PASSWORD 'm5NjNV3fBR2n8w3X';
GRANT live_read_only_group TO

Теперь нужно переключиться в нужную базу, правой кнопкой мыши нажав на имя базы, сделав её активной (будет выделена жирным текстом), и добавить права на использование и чтение для read-only группы:

GRANT USAGE ON SCHEMA public TO group live_read_only_group;
GRANT SELECT ON ALL TABLES IN schema public TO group live_read_only_group;

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

Ещё раз по-простому. Итого получается, нужно было:

В системной базе postgres:

CREATE ROLE live_read_only_group;
GRANT CONNECT ON DATABASE db_name1 TO live_read_only_group;
GRANT CONNECT ON DATABASE db_name2 TO live_read_only_group;
CREATE ROLE read_user_name WITH LOGIN ENCRYPTED PASSWORD 'm5NjNV3fBR2n8w3X';
GRANT live_read_only_group TO

В базе, для которой даётся доступ read-only:

GRANT USAGE ON SCHEMA public TO group live_read_only_group;
GRANT SELECT ON ALL TABLES IN schema public TO group live_read_only_group;

Для полного доступа в БД для роли потребуются следующие права:

GRANT USAGE ON SCHEMA public TO group live_dev_rw;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO live_dev_rw;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO live_dev_rw;