Политики защиты строк
К таблице можно применить политики защиты строк (row security policies). Эти политики определяют, какие строки могут быть доступны для каждого пользователя. Вы можете создать политику для определенных команд, таких как SELECT, INSERT, UPDATE и DELETE, или указать её для всех команд (ALL). Этот функционал также известен как безопасность на уровне строк (row-level security).
Подключимся к базе данных как пользователь postgres и создадим таблицу для примеров. Для этого можно использовать psql:
$ psql -h 10.92.6.36 -p 5432 -U postgres postgresВ приведенном выше примере 10.92.6.36 — адрес сервера, 5432 — порт для подключения, -U postgres — имя пользователя и postgres — имя базы данных.
CREATE TABLE users (
user_id serial PRIMARY KEY,
user_name VARCHAR NOT NULL,
pwhash VARCHAR,
real_name VARCHAR NOT NULL
);Добавим данные в таблицу:
INSERT INTO users (user_name, pwhash, real_name)
VALUES
('james', 'xxx', 'James Brown'),
('mary', 'xxx', 'Mary Smith'),
('alice', 'xxx', 'Alice Gray');Создадим роли и воспользуемся командой GRANT, чтобы определить их привилегии доступа к таблице users:
CREATE ROLE james WITH LOGIN PASSWORD 'password1';
GRANT ALL ON users TO james;
CREATE ROLE mary WITH LOGIN PASSWORD 'password2';
GRANT ALL ON users TO mary;Пользователи james и mary имеют привилегию ALL над таблицей users.
Включение защиты строк
Для того чтобы включить защиту на уровне строк, выполним команду ALTER TABLE с параметром ENABLE ROW LEVEL SECURITY.
ALTER TABLE users ENABLE ROW LEVEL SECURITY;Если для таблицы включена защита на уровне строк, доступ к таблице для выбора или изменения строк должен быть разрешен политикой безопасности. Если для таблицы не определена политика, доступ запрещен: строки не могут быть выбраны или изменены. Политика защиты строк не влияет на операции, применяемые ко всей таблице, например TRUNCATE или REFERENCES.
Политики защиты строк не распространяются на суперпользователей и роли с атрибутом BYPASSRLS. Также они не влияют на владельцев таблиц, но владельцы могут включить их для себя с помощью команды ALTER TABLE … FORCE ROW LEVEL SECURITY.
Переподключимся к базе данных как пользователь james:
$ psql -h 10.92.6.36 -p 5432 -U james postgresЕсли попытаемся выбрать строки из таблицы users, получим следующий результат:
SELECT * FROM users; user_id | user_name | pwhash | real_name ---------+-----------+--------+----------- (0 rows)
Пользователь james не может получить доступ к данным таблицы, так как включена защита на уровне строк и не создана политика безопасности.
|
ПРИМЕЧАНИЕ
Только владелец таблицы может включать и отключать защиту строк и добавлять политики таблице.
|
Управление политиками безопасности строк
Политики безопасности строк могут быть указаны для команд, для ролей или для тех и других вместе. Политика может быть применена ко всем командам (ALL) или к командам SELECT, INSERT, UPDATE или DELETE. Текущей политике можно назначить несколько ролей. За подробностями обратитесь к статье CREATE POLICY.
PostgreSQL также применяет стандартные правила членства и наследования ролей к политикам безопасности строк.
У каждой политики есть имя. Для одной таблицы можно определить несколько политик. Политики относятся к таблице, политики одной таблицы должны иметь уникальные имена. К разным таблицам могут относиться политики с одинаковыми именами.
Переподключимся к базе данных как пользователь postgres и определим следующую политику безопасности строк:
$ psql -h 10.92.6.36 -p 5432 -U postgres postgresCREATE POLICY policy1 ON users FOR ALL TO PUBLIC USING (user_name=current_user);Эта политика позволяет всем ролям выполнять все команды, если значение из столбца user_name обрабатываемой строки таблицы users равно текущему имени пользователя.
PUBLIC используется вместо имени роли и применяет политику ко всем ролям.
Выражение USING представляет собой любое выражение SQL, возвращающее логическое значение.
Согласно этой политике пользователи james и mary, созданные выше, могут обрабатывать строки, содержащие информацию о каждом из них.
Переподключимся к базе данных как пользователь james и выберем данные:
$ psql -h 10.92.6.36 -p 5432 -U james postgres SELECT * FROM users;Результат:
user_id | user_name | pwhash | real_name
---------+-----------+--------+-------------
1 | james | xxx | James Brown
Переподключимся к базе данных как пользователь mary и выберем данные:
$ psql -h 10.92.6.36 -p 5432 -U mary postgres SELECT * FROM users;Результат:
user_id | user_name | pwhash | real_name
--------+-----------+--------+-----------
2 | mary | xxx | Mary Smith
Переподключимся к базе данных как пользователь postgres, создадим нового пользователя alice и добавим новую политику:
$ psql -h 10.92.6.36 -p 5432 -U postgres postgresCREATE ROLE alice WITH LOGIN PASSWORD 'password3';
GRANT ALL ON users TO alice;
CREATE POLICY policy2 ON users TO alice USING (true) WITH CHECK (true);Выражение WITH CHECK должно возвращать true, чтобы пользователь смог вставлять и обновлять строки.
Эта политика разрешает пользователю alice все операции над всеми строками таблицы.
Переподключимся к базе данных как alice и проверим результат:
$ psql -h 10.92.6.36 -p 5432 -U alice postgresDELETE FROM users WHERE user_id=1;
SELECT * FROM users;Результат:
user_id | user_name | pwhash | real_name
---------+-----------+--------+-------------
2 | mary | xxx | Mary Smith
3 | alice | xxx | Alice Gray
Вы также можете использовать команду ALTER POLICY для обновления политики. Следующий код переименовывает policy1 в policy_for_users:
ALTER POLICY policy1 ON users RENAME TO policy_for_users;Команда DROP POLICY удаляет политику:
DROP POLICY policy_for_users ON users;