Безопасность на уровне столбцов
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 postgresGRANT 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 postgresCREATE 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 postgresGRANT ALL ON users_encryption TO user2;Переподключимся как user2 и выберем все столбцы из users_encryption.
$ psql -h 10.92.6.36 -p 5432 -U user2 postgresSELECT * 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 расшифровывает данные с помощью ключа шифрования.