Безопасность на уровне столбцов

Евгения Кузина

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 расшифровывает данные с помощью ключа шифрования.

Нашли ошибку? Выделите текст и нажмите Ctrl+Enter чтобы сообщить о ней