Разграничение прав доступа в PostgreSQL

Хочу описать один из способов разграничения доступа к данным в СУБД, который мне кажется довольно гибким и интересным. Этот способ позволяет получать информацию о текущем пользователе с помощью вызова простой хранимой процедуры. Но сперва рассмотрим известные существующие способы с их плюсами и минусами, среди которых можно выделить использование встроенных механизмов аутентификации СУБД и контроль доступа на уровне приложения.

Способ 1. Встроенные механизмы аутентификации

Для каждого бизнес-пользователя создаётся соответствующий пользователь в СУБД, которому раздаются необходимые права.

Плюсы такого подхода: его простота и прозрачность. По логам СУБД легко увидеть, какие запросы выполняют пользователи, несколько прав можно объединять в роли и раздавать их пользователям прямо "из коробки". Основной минус такого подхода — отсутствие контроля доступа на уровне строк. Да, в 9.5 появилась row-level security, но этот механизм работает не так быстро, как хотелось бы, особенно для JOIN.

К встроенным механизмам аутентификации также относятся LDAP, PAM, GSSAPI и прочие.

Способ 2. Проверка на уровне приложения

Многие осуществляют разграничение доступа прямо на уровне приложения. При этом можно использовать как внешний сервис для авторизации пользователей так и хранить хеши паролей непосредственно в базе и проверять их в приложении. Это не имеет значения. Главное то, что все пользователи в конечном итоге ходят в базу под одним пользователем. В таком подходе я вообще не вижу никаких плюсов, зато минусов предостаточно:

1. Отсутствует контроль доступа на уровне строк, либо он становится очень сложным.
2. В случае компрометации пароля пользователя СУБД злоумышленник получает полный доступ ко всем данным, причём он сможет не только читать их, но и изменять.
3. Приложение становится единственной звеном, контролирующим доступ и если вы, допустим, захотите реализовать ещё какой-нибудь сервис, работающий с базой, вам придётся писать весь код, выполняющий проверки, заново.

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

Способ 3. Введение сессии на уровне СУБД

Об этом способе я сегодня и хочу рассказать поподробнее. Суть его проста: в базе данных создаётся процедура авторизации, которая проверяет логин и пароль пользователя и в случае успеха устанавливает значение некоторой сессионной переменной, которая была бы доступна на чтение до конца текущей сессии. Для хранения значения переменной будем использовать глобальный массив GD, доступный процедурам на языке Pl/Python:

create or replace
function set_current_user_id(user_id integer) as $$
  GD['user_id'] = user_id
$$ language plpythonu;

Сама же процедура авторизации будет выглядеть следующим образом:

create or replace
function login(user_ text, password_ text) returns integer as $$
declare
  vuser_id integer; vis_admin boolean;
begin
  select id, is_admin
    into vuser_id, is_admin
    from users where login = login_ and password = password_;

  if found then
    perform set_current_user_id(vuser_id);
    /* код функции set_is_admin() аналогичен
       коду функции set_current_user_id() */
    perform set_is_admin(vis_admin);
  else
    raise exception 'Invalid login or password';
  end if;

  return vuser_id;
end;
$$ language plpgsql security definer;

После этого осталось реализовать функцию, которая будет возвращать ID залогиненного пользователя:

create or replace
function get_current_user_id() returns integer as $$
  return GD.get('user_id')
$$ language plpythonu stable;

Теперь о том, как это всё использовать. А использовать очень просто. После авторизации пользователя внутри любой функции теперь можно легко узнать, что за пользователь запрашивает доступ к данным и какие у него есть права. Например:

create or replace
function delete_branch(branch_id_ integer) returns void as $$
begin
  if not current_user_is_admin() then
    raise exception 'Access denied: this operation needs admin privileges';
  end if;
  ...
end;
$$ language plpgsql;

Для демонстрации того, как будет работать разграничение доступа на уровне строк, напишем функцию, которая будет возвращать список счетов в банке, причём только тех, которые открыты в филиале, к которому принадлежит пользователь (branch_id).

create or replace
function get_accounts() returns table (account_number text) as $$
begin
  return query
  select a.account_number
    from accounts a
    join users u on u.branch_id = a.branch_id
   where u.id = get_current_user_id();
end;
$$ language plpgsql;

В чём плюсы и минусы такого подхода? Плюсы:

1. Удобство использования, гибкость, расширяемость.
2. Обеспечение разграничения доступа на уровне строк практически без ущерба для производительности СУБД.
3. Вся логика сосредоточена в СУБД, таким образом можно предоставлять доступ к базе данных нескольким приложениям, в которых придётся реализовать лишь механизм авторизации.
4. Кроме информации о самом пользователе, можно оперативно получать любые метаданные, связанные с ним — например, является ли текущий пользоватль администратором, его имя для отображения в каком-нибудь личном кабинете, группы, к которым он принадлежит, и так далее.

Несмотря на это, есть также и минусы:

1. Всю логику работы с данными необходимо оборачивать в хранимые процедуры (на самом деле, для меня это плюс).
2. Необходимость авторизации пользователя в начале каждой сессии, а если код обёрнут в транзакции, то в начале каждой транзакции. Это может быть некритично для так называемых "толстых клиентов", но для веб приложений уже становится актуальным. В этом случае проблема решается оборачиванием драйвера, который предоставляет доступ к СУБД кастомным кодом таким образом, чтобы авторизация выполнялась перед выполнением каждого запроса. Звучит не очень красиво, но на самом деле всё не так страшно. Я в своих проектах использовал Flask и модуль flask_login, который сильно упрощает эту задачу.

Резюме

Конечно, наверняка существуют проекты где описанный мной подход будет неуместен и я буду рад, если вы поделитесь своими мыслями на этот счёт, — возможно, данный метод можно доработать и улучшить. Но, в целом, такой подход кажется мне довольно интересным.