PostgreSQL. Часть 4. Безопасность в PostgreSQL

В моем блоге уже был цикл статей с конспектом по MySQL. Эта одна из публикаций цикла конспектов по PostgreSQL. Материал этих публикаций не особо структурирован и представляет собой просто мои заметки – конспектировал что-то новое и то, что я посчитал интересным и важным. Эта публикация является конспектом про безопасность в PostgreSQL.

Список литературы

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

1. Книга PostgreSQL 14 Administration Cookbook.

Безопасность в PostgreSQL

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

Создание администратора PostgreSQL:

CREATE USER adm SUPERUSER LOGIN;

Отозвать разрешения суперпользователя:

ALTER USER adm NOSUPERUSER;

Выдать разрешения суперпользователя существующему пользователю:

ALTER USER admin SUPERUSER;

Отзыв разрешений

Пример отзыва всех разрешений у пользователя user2 для таблицы table1:

REVOKE ALL ON table1 FROM user2;

Список разрешений на таблицу:

Пример запросов разрешений для таблицы через консольный клиент psql:

\z table1
\dp table1

Описание привилегий роли:

Запрос с описанием привилегий роли через консольный клиент psql:

\du postgres

Выдача разрешений для таблицы

Пример запроса для выдачи разрешений (на схему) на определенной группы группу:

GRANT USAGE ON SCHEMA public TO user2;

Примеры выдачи привилегий на чтение и добавление данных:

GRANT SELECT ON public.pages TO user2;
GRANT INSERT ON public.viewlog TO user2;

Выдача разрешений на чтение всего:

GRANT SELECT ON ALL TABLES IN SCHEMA public TO user2;

Выдача разрешений на отдельную таблицу

Примеры запросов на выдачу разрешений на отдельный столбец таблицы:

GRANT SELECT, INSERT ON someschema.sometable2
TO somerole;

GRANT UPDATE (col2) ON someschema.sometable2
TO somerole;

Выдача доступов к конкретной строке

Сначала необходимо включить RLS (Row Level Security) для таблицы:

ALTER TABLE someschema.sometable3 ENABLE ROW LEVEL SECURITY;

Второй шаг – выдать доступ ко всей табюлице:

GRANT SELECT ON someschema.sometable3 TO somerole;

Затем необходимо создать политику:

CREATE POLICY example1 ON someschema.sometable3
FOR SELECT
TO somerole
USING (col1 > 0);

Второй пример – это политика для добавления строки:

GRANT INSERT ON someschema.sometable3 TO somerole;
CREATE POLICY example2 ON someschema.sometable3
FOR INSERT
TO somerole
WITH CHECK (col1 > 0);

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

Первый способ создания пользователя – через консольную утилиту createuser:

pguser@hvost:~$ createuser bob

Дополнительный вариант создания пользователя с запросом дополнительной информации в виде мастера:

pguser@hvost:~$ createuser --interactive bob

Пример создания пользователей через инструкции SQL:

CREATE USER bob;
CREATE USER alice CREATEDB;

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

\du alice

Временно запретить вход пользователю

Запретить вход для конкретного пользователя:

alter user bob nologin;

Разрешить вход для конкретного пользователя:

alter user bob login;

Альтернативный вариант запрета входа пользователя:

alter user bob connection limit 0;

Разрешить только 10 сессий для конкретного пользователя:

alter user bob connection limit 10;

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

alter user bob connection limit -1;

Пользователи с небезопасным паролем

Небезопасным считается пароль, который использует алгоритм хэширования слабее чем SCRAM-SHA-256. Например, пароль с MD5 хэшем.

Пример запроса для получения информации о пользователях с пустым или небезопасным паролем:

select usename,passwd from pg_shadow where passwd
not like 'SCRAM%' or passwd is null;

Другие связанные статьи

PostgreSQL. Часть 1. Информационные запросы PostgreSQL

PostgreSQL. Часть 2. Нюансы настройки сервера PostgreSQL

PostgreSQL. Часть 3. Управление сервером PostgreSQL

PostgreSQL. Часть 5. Мониторинг и диагностика PostgreSQL

PostgreSQL. Часть 6. Регулярное обслуживание PostgreSQL

PostgreSQL. Часть 7. Производительность и конкурентный доступ PostgreSQL

PostgreSQL. Часть 8. Резервное копирование и восстановление PostgreSQL