ROLES в PostgreSQL

Статья основана на 27 видео из 30 тем курса SQL c 0 от Аристова Евгения. Ссылки на видео на платформах RUTUBE и VK видео.

В этой статье разберем роли, пользователей, группы и наследование в PostgreSQL.

ROLES

PostgreSQL, в отличии от других СУБД, не разграничивает USER/GROUP/ROLE

Пользователь/роль создаются на ВЕСЬ кластер.

Отличие пользователя и группы:

• Если роль (role) имеет право на login, то это пользователь (user)
• Если роль (role) права на login не имеет, то это группа (group)

Создание пользователя или роли:

CREATE ROLE name [ [ WITH ] option [ … ] ]
    SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit

По умолчанию все опции отключены, также не забывайте выдавать права на коннект в pghba.conf (какие пользователи имеют право на вход).

При создании ROLE будет выбрана опция LOGIN, а при USER – NOLOGIN.

Между группами можно наследоваться.

Изменение ролей

Удаление ролей

Отдельная статья про ALTER&DROP

Права

Помимо пользователей существуют права на объекты. Если права не выданы, то их нет (кроме суперпользователя).

Выдача прав на объекты:

GRANT – выдача прав

REVOKE – отзыв прав

INHERIT – выдача прав с правом наследования (пользователь сможет передать их другим пользователям)

Права выдаются только на существующие объекты, помимо DEFAULT прав.

Можно выдать права на определенную операцию (например, INSERT), только на определенную таблицу или несколько таблиц в схеме, на базы данных, схемы и последовательности.

Практика

Создадим базу данных test:

DROP DATABASE IF EXISTS test;
CREATE DATABASE test;

Создадим роль (предварительно удалив, если существует):

DROP ROLE IF EXISTS onlyread;
CREATE ROLE onlyread;

У роли нет права на login.

Теперь выдадим право на подключение к базе данных:

GRANT CONNECT ON DATABASE test TO onlyread;

Теперь в psql подключимся к базе данных:

Создадим схему в нашей базе данных и выдадим права на эту схему:

CREATE SCHEMA IF NOT EXISTS test;
GRANT USAGE ON SCHEMA test TO onlyread;

Создадим таблицу Склад:

CREATE TABLE test.warehouse (
    id serial UNIQUE,
    name text NOT NULL DEFAULT '',
    kolvo int NOT NULL DEFAULT 0,
    price numeric(17,2) NOT NULL DEFAULT 0.0
);

Также создаем таблицу Продажи:

DROP TABLE IF EXISTS test.sale;
CREATE TABLE test.sale(
    id serial PRIMARY KEY,
    kolvo int NOT NULL DEFAULT 0,
    summa numeric(17,2) NOT NULL DEFAULT 0.0,
    fk_warehouseID int references test.warehouse(id) ON DELETE CASCADE,
    saleDate date
);

Теперь добавляем значения:

INSERT INTO test.warehouse(name,kolvo,price) VALUES
    ('абрикосы',1,500);
INSERT INTO test.warehouse(name,kolvo,price) VALUES
    ('ананасы',2,100);
INSERT INTO test.sale(fk_warehouseID,kolvo,summa) VALUES
    (2,1,50);

Сделаем выборку:

SELECT w.name, s.kolvo, s.summa
FROM test.sale s
JOIN test.warehouse w
ON s.fk_warehouseid=w.id;

Теперь выдадим созданной роли права на таблицу Склад:

GRANT SELECT ON TABLE test.warehouse TO onlyread;

Создаем пользователя с паролем и правами на группу onlyread и SELECT в таблице sale:

DROP USER IF EXISTS bigboss;
CREATE USER bigboss WITH PASSWORD 'admin';
GRANT onlyread TO bigboss;
GRANT SELECT ON TABLE test.sale TO bigboss;

В psql переключимся на другого пользователя:

sudo -u postgres psql -h localhost -U bigboss -W -d test

Проверим доступ:

\d

Видим список доступных объектов и их владельцев.

Проверим, работает ли SELECT:

Да, так как права на SELECT в таблице Склад были выданы группе, а пользователю были выданы права группы + права на таблицу с продажами.

Попробуем добавить строчку:

INSERT INTO test.sale(fk_warehouseID,kolvo,summa) VALUES
    (1,1,50);

Прав на эту операцию нет.

Вернемся под суперпользователя:

sudo -u postgres psql -d test

Выдадим все права на sale для пользователя:

GRANT ALL PRIVILEGES ON TABLE test.sale TO "bigboss";

Создадим таблицу test и добавим значение:

CREATE TABLE test.test (
    t text
);
INSERT INTO test.test values
    ('test');

Переключимся под ранее созданного пользователя:

sudo -u postgres psql -h localhost -U bigboss -W -d test

Снова попробуем вставить значение:

INSERT INTO test.sale(fk_warehouseID,kolvo,summa) VALUES
    (1,1,50);

Возникла ошибка, так как при данной операции используется SEQUENCE, на который права мы не выдавали.

Можно задать руками поле id, в таком случае SEQUENCE использоваться не будет:

INSERT INTO test.sale(id,fk_warehouseID,kolvo,summa) VALUES
    (50,1,1,50);

Попробуем вставить значение в test.test:

INSERT INTO test.test values('test2');

Прав недостаточно, так как они выданы только на test.sale.

Просмотр всех прав:

\du+

\dg

DEFAULT права:

\ddp

Гранты ролей:

\drg

Привилегии конкретного объекта:

\dp+ test.sales

Можно включить ECHO_HIDDEN, который будет показывать исполняемые команды в расширенном виде:

\set ECHO_HIDDEN on

\dp+ test.sales

Можно посмотреть системные данные напрямую:

SELECT * FROM pg_user;

usebypassrls позволяет шифровать конкретные поля.

SELECT * FROM pg_group;

Видим системные группы.

Пароли в зашифрованном виде (доступ для суперпользователя):

SELECT * FROM pg_shadow;

Презентация к статье здесь.

28 из 30 тем будет скоро доступна. Если вы хотите быстрее получить доступ – присоединяйтесь к онлайн группе, ссылка доступна в описании курса.