Безопасность на уровне столбцов
PostgreSQL позволяет ограничить доступ пользователя к определенному столбцу или набору столбцов, чтобы пользователь не мог просматривать и изменять данные столбца.
Реализовать безопасность на уровне столбцов можно одним из следующих способов:
Подключимся к базе данных как пользователь 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,
real_name VARCHAR NOT NULL,
address VARCHAR,
salary INT
);
Добавим данные в таблицу:
INSERT INTO users (user_name, real_name, address, salary)
VALUES
('james', 'James Brown', '123 2nd Street', 5000),
('mary', 'Mary Smith', '456 13th Street', 6000),
('alice', 'Alice Gray', '789 19th Street', 5700);
Создадим роли:
CREATE ROLE user1 WITH LOGIN PASSWORD 'password1';
CREATE ROLE user2 WITH LOGIN PASSWORD 'password2';
Создание представления
Можно создать представление, которое включает только разрешенные столбцы, и дать доступ пользователю с помощью команды GRANT к этому представлению, а не к базовой таблице. Например, мы можем скрыть столбец salary
от пользователя user1
. Создадим представление со столбцами user_name
, real_name
и address
и предоставим права доступа пользователю user1
к этому представлению:
CREATE VIEW users_info AS SELECT user_name, real_name, address FROM users;
GRANT ALL ON users_info TO user1;
Переподключимся к базе данных как пользователь user1
:
$ psql -h 10.92.6.36 -p 5432 -U user1 postgres
Просмотрим данные из представления users_info
:
SELECT * FROM users_info;
Поскольку user1
имеет права доступа к этому представлению, результат будет следующим:
user_name | real_name | address -----------+-------------+----------------- james | James Brown | 123 2nd Street mary | Mary Smith | 456 13th Street alice | Alice Gray | 789 19th Street
Если попытаться выбрать все данные из таблицы users
от имени user1
, произойдет ошибка, так как пользователь user1
не имеет прав доступа к таблице.
SELECT * FROM users;
Результат:
ERROR: permission denied for table users
Предоставление прав доступа на определенные столбцы
Можно предоставить пользователю доступ только к определенным столбцам таблицы. Например, мы можем скрыть столбец salary
от пользователя user2
. Переподключимся к базе данных как пользователь postgres
и добавим разрешения пользователю user2
на доступ ко всем столбцам таблицы users
, кроме salary
:
$ psql -h 10.92.6.36 -p 5432 -U postgres postgres
GRANT SELECT (user_name, real_name, address) ON users TO user2;
Переподключимся к базе данных как пользователь user2
:
$ psql -h 10.92.6.36 -p 5432 -U user2 postgres
Выберем данные из разрешенных столбцов таблицы users
:
SELECT user_name, real_name, address FROM users;
Результат:
user_name | real_name | address -----------+-------------+----------------- james | James Brown | 123 2nd Street mary | Mary Smith | 456 13th Street alice | Alice Gray | 789 19th Street
Обратите внимание, что если user2
попытается выбрать все столбцы из таблицы users
, возникнет ошибка:
SELECT * FROM users;
Результат:
ERROR: permission denied for table users
Шифрование столбцов
Также можно зашифровать определенные столбцы базы данных для их защиты. Для этого используем дополнительный модуль pgcrypto. За подробной информацией об установке и использовании этого модуля обратитесь к статье Шифрование.
В приведенном ниже примере используются функции pgp_sym_encrypt
и pgp_sym_decrypt
из модуля pgcrypto.
Переподключимся к базе данных как пользователь user1
и создадим таблицу users_encryption
:
$ psql -h 10.92.6.36 -p 5432 -U user1 postgres
CREATE TABLE users_encryption (
user_id serial PRIMARY KEY,
user_name VARCHAR NOT NULL,
real_name VARCHAR NOT NULL,
address VARCHAR,
salary TEXT
);
Добавим данные и зашифруем столбец salary
с помощью функции pgp_sym_encrypt
:
INSERT INTO users_encryption (user_name, real_name, address, salary)
VALUES
('james', 'James Brown', '123 2nd Street', pgp_sym_encrypt('5000','secret_encryption_key')),
('mary', 'Mary Smith', '456 13th Street', pgp_sym_encrypt('6000','secret_encryption_key')),
('alice', 'Alice Gray', '789 19th Street', pgp_sym_encrypt('5700','secret_encryption_key'));
Выберем данные, чтобы проверить, зашифрован ли столбец salary
:
SELECT * FROM users_encryption;
Результат:
user_id | user_name | real_name | address | salary --------+-----------+-------------+-----------------+----------------------------------------------- 1 | james | James Brown | 123 2nd Street | \xc30d04070302ec4106d9f16a58767ed23501d9679... 2 | mary | Mary Smith | 456 13th Street | \xc30d04070302a972f63c44aa0b8270d23501ce960... 3 | alice | Alice Gray | 789 19th Street | \xc30d04070302edf9f8629c0fb48669d23501560fe...
Переподключимся к базе данных как пользователь postgres
и разрешим доступ к таблице users_encryption
пользователю user2
:
$ psql -h 10.92.6.36 -p 5432 -U postgres postgres
GRANT ALL ON users_encryption TO user2;
Переподключимся как user2
и выберем все столбцы из users_encryption
.
$ psql -h 10.92.6.36 -p 5432 -U user2 postgres
SELECT * FROM users_encryption;
Результат будет таким же:
user_id | user_name | real_name | address | salary --------+-----------+-------------+-----------------+----------------------------------------------- 1 | james | James Brown | 123 2nd Street | \xc30d04070302ec4106d9f16a58767ed23501d9679... 2 | mary | Mary Smith | 456 13th Street | \xc30d04070302a972f63c44aa0b8270d23501ce960... 3 | alice | Alice Gray | 789 19th Street | \xc30d04070302edf9f8629c0fb48669d23501560fe...
Вызовем функцию pgp_sym_decrypt
в операторе SELECT
, чтобы просмотреть расшифрованные данные из столбца salary
:
SELECT user_id, user_name, real_name, address, pgp_sym_decrypt(salary::bytea, 'secret_encryption_key') AS salary FROM users_encryption;
Результат:
user_id | user_name | real_name | address | salary ---------+-----------+-------------+-----------------+-------- 1 | james | James Brown | 123 2nd Street | 5000 2 | mary | Mary Smith | 456 13th Street | 6000 3 | alice | Alice Gray | 789 19th Street | 5700
Обратите внимание, что любой пользователь, у которого есть ключ шифрования, может расшифровать данные столбца. В приведенном выше примере пользователь user1
шифрует данные, а user2
расшифровывает данные с помощью ключа шифрования.