Эмуляция безопасности строк в PostgreSQL 9.4

В PostgreSQL 9.5 появится декларативная безопасность строк. Вы можете задать правила для таблиц и сделать их выполнение автоматическим, к примеру, позволяя пользователю joe видеть только строки, у которых в поле owner стоит значение joe.

Это отличный инструмент и он уже давно должен был появиться. Этого не было сделано в PostgreSQL 9.4, зато было сделано с помощью автоматически обновляемых представлений security_barrier. Они и функции LEAKPROOF формируют фундамент, на котором и построена безопасность строк. Вы можете использовать эти составляющие части без поддержки декларативной политики для достижения безопасности строк в 9.4.

Ранее я уже обсуждал security_barrier представления. Этот пост содержит в себе примеры того, как может произойти утечка информации из представления и как security_barrier представления предотвращают такие утечки. Я предположу, что Вы знакомы с принципами, изложенными в оставшейся части статьи и не буду повторно устраивать демонстрацию того, как происходят утечки информации из представлений и т.д.

Для достижения схожего эффекта по отношению к политике безопасности строк в таблице, необходимо отменить доступ к таблице всем, кроме привилегированных (но не суперпользователей) ролей, которым вы хотели бы предоставить доступ к их собственным представлениям. После чего необходимо создать security_barrier представление, которое принадлежит этой привилегированной роли, с условием WHERE, которое ограничивает возможность других пользователей видеть строки, опираясь на выбранный Вами предикат — можно использовать current_user, вызов current_setting и т.д.

Например::

CREATE ROLE secret_manager;
CREATE ROLE bob;
CREATE ROLE sid;

CREATE TABLE user_secrets(
    secret_id integer primary key,
    owner text not null,
    secret text not null
);

ALTER TABLE user_secrets OWNER TO secret_manager;

INSERT INTO user_secrets (secret_id, owner, secret) VALUES
(1, 'bob', 'pancakes'),
(2, 'fred', 'waffles'),
(3, 'anne', 'cake'),
(4, 'sid', 'fraud');

REVOKE ALL ON user_secrets FROM public;

CREATE VIEW filtered_user_secrets
WITH (security_barrier)
AS
SELECT *
FROM user_secrets
WHERE owner = current_user
WITH CHECK OPTION;

ALTER VIEW filtered_user_secrets OWNER TO secret_manager;

GRANT ALL ON filtered_user_secrets TO public;

RESET ROLE;

Теперь давайте посмотрим как это работает:

test=# SET ROLE bob;
SET
test=> select * from filtered_user_secrets ;
 secret_id | owner |  secret  
-----------+-------+----------
         1 | bob   | pancakes
(1 row)

test=> SET ROLE sid;
SET
test=> select * from filtered_user_secrets ;
 secret_id | owner | secret 
-----------+-------+--------
         4 | sid   | fraud
(1 row)

test=> SELECT * FROM filtered_user_secrets WHERE owner = 'bob';
 secret_id | owner | secret 
-----------+-------+--------
(0 rows)

test=> INSERT INTO filtered_user_secrets (secret_id, owner, secret) VALUES (5, 'sid', 'larceny');
INSERT 0 1
test=> select * from filtered_user_secrets ;
 secret_id | owner | secret  
-----------+-------+---------
         4 | sid   | fraud
         5 | sid   | larceny
(2 rows)

test=> INSERT INTO filtered_user_secrets (secret_id, owner, secret) VALUES (6, 'joe', 'impersonation');
ERROR:  new row violates WITH CHECK OPTION for view "filtered_user_secrets"
DETAIL:  Failing row contains (secret_id, owner, secret) = (6, joe, larceny).

Поведение очень похоже на политику безопасности строк, но с некоторыми оговорками:

• Использование ALTER к лежащей в основе таблице не внесет видимых изменений в представление. Для этого необходимо удалить и пересоздать представление.
• Это не прозрачно для приложений. Они должны использовать само представление, а не лежащую в основе таблицу.

Последний пункт может быть в какой-то степени решен с помощью схем и search_path, например:

CREATE SCHEMA filtered_tables;

ALTER TABLE user_secrets SET SCHEMA filtered_tables;

-- Leave the view in the public schema and just rename it
ALTER TABLE filtered_user_secrets RENAME TO user_secrets;

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

Вместо того, чтобы использовать current_user, можно использовать current_setting('myapp.active_user'). Если делать именно так, следует установить пустое значение по умолчанию на уровне базы, дабы current_setting не выдавало ошибки, если настройка не определена (в версии 9.5 можно использовать current_setting('myapp.active_user', 't') для игнорирования недостающих записей). Например:

ALTER DATABASE mydatabase SET myapp_active_user = '';

ВАЖНО: имейте в виду, что если вы используете current_setting в качестве предиката представления, политика безопасности не распространяется на определенные пользователем настройки конфигурации, так что любой пользователь, который имеет возможность выполнить произвольный SQL запрос — может изменить настройку. Это по прежнему остается полезным инструментом, когда все запросы проходят через приложение с полным контролем выполняемых запросов, но не подходит для ограничения действий пользователей, которые имеют прямой доступ к базе. То же самое применимо к использованию SET ROLE для переключения активного пользователя, когда в приложении используются объединенные подключения, так как любой пользователь просто может сделать RESET ROLE.

Намного проще использовать удобства защиты на уровне строк из версии 9.5, но если схожий функционал необходим прямо сейчас, то его уже можно достичь.

Для еще более старых версий PostgreSQL можно использовать SECURITY DEFINER plpgsql функции, которые возвращают запрос фильтрованного представления о базовой таблице. Производительность будет ужасная, потому что все строки, которые увидит пользователь, должны быть сначала выбраны, а потом отфильтрованы, так что большинство индексов не может быть использовано. Кроме того, вы можете использовать обычные представления, если Вы не предоставляете пользователям возможности определять собственные функции, так что утечки менее вероятны.

Безопасность строк и подходы, основанные на представлениях, в значительной степени будут в выигрыше от способности определять безопасные переменные, которые могут быть единовременно установлены и не могут быть сброшены, или могут быть установлены только определенной ролью (возможно с помощью SECURITY DEFINER функцией, которая совершает проверку на здравомыслие). Такого функционала не появиться в 9.5, но это может стать возможным с помощью какого-нибудь расширения, которое я надеюсь изучить позже.

Автор: the_unbridled_goose

Источник