Управление ролями и разрешениями в PostgreSQL

Введение

В этой лабораторной работе вы изучите управление ролями и разрешениями в PostgreSQL. Основная цель — научиться создавать роли с определенными атрибутами и управлять привилегиями базы данных.

Вы начнете с создания роли с именем reporting_role с атрибутом NOLOGIN, который предотвратит прямое подключение к базе данных. Затем вы проверите создание роли. Последующие шаги будут включать предоставление и тестирование привилегий базы данных, переключение пользователей для проверки доступа и отзыв привилегий у роли.

Создание роли

На этом этапе вы научитесь создавать роль в PostgreSQL. Роли используются для управления доступом к базе данных и разрешениями.

Подключение к серверу PostgreSQL:

Откройте терминал и подключитесь к серверу PostgreSQL с помощью инструмента командной строки psql от имени пользователя postgres.
```
sudo -u postgres psql
```
Вы должны увидеть приглашение, похожее на:
```
postgres=#
```
Создание роли reporting_role:

Теперь давайте создадим роль reporting_role с помощью команды CREATE ROLE. Мы укажем атрибут NOLOGIN, что означает, что эта роль не может быть использована для прямого подключения к базе данных. Она будет использоваться только для предоставления разрешений другим ролям или пользователям.
```
CREATE ROLE reporting_role NOLOGIN;
```
Эта команда создает роль. Вы должны увидеть следующий вывод:
```
CREATE ROLE
```
Проверка создания роли:

Чтобы убедиться, что роль была создана, вы можете использовать команду \du (сокращение от \list users). Эта команда выводит список всех ролей в базе данных.
```
\du
```
Вы должны увидеть reporting_role в списке ролей. Вывод будет выглядеть примерно так:
```
List of roles
Role name | Attributes | Member of
----------------+------------------------------------------------------------+-----------
labex | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
reporting_role | Cannot login | {}
```
Выйдите из оболочки psql.
```
\q
```

Предоставление привилегий базы данных

На этом этапе вы научитесь предоставлять привилегии базы данных роли. Мы будем использовать роль reporting_role, созданную на предыдущем шаге.

Подключение к серверу PostgreSQL:

Подключитесь к серверу PostgreSQL с помощью инструмента командной строки psql от имени пользователя postgres.
```
sudo -u postgres psql
```
Создание базы данных:

Давайте создадим базу данных с именем reporting_db, к которой будет иметь доступ наша роль reporting_role.
```
CREATE DATABASE reporting_db;
```
Вы должны увидеть следующий вывод:
```
CREATE DATABASE
```
Подключение к базе данных reporting_db:

Подключитесь к только что созданной базе данных:
```
\c reporting_db
```
Приглашение должно измениться на reporting_db=#.
Создание таблицы:

Давайте создадим простую таблицу с именем sales_data в базе данных reporting_db.
```
CREATE TABLE sales_data (
    id SERIAL PRIMARY KEY,
    product VARCHAR(255),
    amount DECIMAL
);
```
Вы должны увидеть следующий вывод:
```
CREATE TABLE
```
Предоставление привилегии SELECT на таблицу sales_data роли reporting_role:

Теперь мы предоставим привилегию SELECT на таблицу sales_data роли reporting_role. Это позволит любой роли или пользователю, являющемуся членом reporting_role, читать данные из таблицы sales_data.
```
GRANT SELECT ON sales_data TO reporting_role;
```
Вы должны увидеть следующий вывод:
```
GRANT
```
Выйдите из оболочки psql.
```
\q
```

Создание пользователя и тестирование привилегий

На этом этапе вы создадите пользователя и предоставите ему членство в роли reporting_role для тестирования привилегий.

Подключение к серверу PostgreSQL:

Подключитесь к серверу PostgreSQL с помощью инструмента командной строки psql от имени пользователя postgres.
```
sudo -u postgres psql
```
Подключение к базе данных reporting_db:

Подключитесь к базе данных reporting_db:
```
\c reporting_db
```
Создание пользователя и предоставление членства в reporting_role:

Давайте создадим нового пользователя с именем report_user. Затем мы предоставим этому пользователю членство в роли reporting_role. Это означает, что report_user унаследует привилегии, предоставленные reporting_role.
```
CREATE USER report_user;
GRANT reporting_role TO report_user;
```
Вы должны увидеть следующий вывод:
```
CREATE ROLE
GRANT ROLE
```
Настройка PostgreSQL для аутентификации без пароля:

По умолчанию PostgreSQL использует аутентификацию peer, которая может вызвать проблемы с подключением в этой лаборатории. Мы изменим ее на trust для упрощения доступа для report_user.

Сначала выйдите из оболочки psql:
```
\q
```
Теперь измените файл pg_hba.conf, чтобы изменить метод аутентификации. Следующая команда sed создаст резервную копию исходного файла как pg_hba.conf.bak и заменит peer на trust для локальных подключений.
```
sudo sed -i.bak 's/^local\s\+all\s\+all\s\+peer/local all all trust/' /etc/postgresql/14/main/pg_hba.conf
```
Перезагрузите PostgreSQL, чтобы применить изменения:
```
sudo service postgresql reload
```
Вставка данных в таблицу sales_data:

Подключитесь обратно к базе данных от имени пользователя postgres.
```
sudo -u postgres psql -d reporting_db
```
Вставьте некоторые примеры данных в таблицу sales_data для целей тестирования.
```
INSERT INTO sales_data (product, amount) VALUES ('Laptop', 1200.00), ('Mouse', 25.00), ('Keyboard', 75.00);
```
Вы должны увидеть следующий вывод:
```
INSERT 0 3
```
Выйдите из оболочки psql.
```
\q
```
Подключение к базе данных reporting_db от имени пользователя report_user:

Откройте новое окно терминала и используйте следующую команду:
```
psql -d reporting_db -U report_user
```
Вы будете подключены к базе данных как report_user без запроса пароля.
Тестирование привилегий:

Теперь попробуйте выбрать данные из таблицы sales_data:
```
SELECT * FROM sales_data;
```
Вы должны увидеть вставленные вами данные:
```
 id | product  | amount
----+----------+--------
  1 | Laptop   | 1200.00
  2 | Mouse    |   25.00
  3 | Keyboard |   75.00
(3 rows)
```
Если вы видите этот вывод, это означает, что report_user имеет привилегию SELECT на таблицу sales_data, которая была предоставлена через роль reporting_role.

Выйдите из оболочки psql.
```
\q
```

Отзыв привилегий

На этом шаге вы отзовете привилегию SELECT у роли reporting_role и убедитесь, что пользователь report_user больше не имеет доступа к таблице sales_data.

Подключение к серверу PostgreSQL от имени пользователя postgres:

Вернитесь в терминал, где вы подключены как пользователь postgres. Подключитесь к серверу PostgreSQL с помощью инструмента командной строки psql от имени пользователя postgres.
```
sudo -u postgres psql
```
Подключение к базе данных reporting_db:

Подключитесь к базе данных reporting_db:
```
\c reporting_db
```
Отзыв привилегии SELECT у роли reporting_role:

Теперь мы отзовем привилегию SELECT на таблицу sales_data у роли reporting_role.
```
REVOKE SELECT ON sales_data FROM reporting_role;
```
Вы должны увидеть следующий вывод:
```
REVOKE
```
Выйдите из оболочки psql.
```
\q
```
Подключение к базе данных reporting_db от имени пользователя report_user:

Вернитесь в терминал, где вы подключены как пользователь report_user.
```
psql -d reporting_db -U report_user
```
Проверка отказа в доступе от имени report_user:

От имени пользователя report_user снова выполните запрос SELECT, чтобы убедиться, что у вас больше нет доступа к данным в таблице sales_data:
```
SELECT * FROM sales_data;
```
Вы должны увидеть сообщение об ошибке, похожее на:
```
ERROR:  permission denied for table sales_data
```
Это подтверждает, что пользователь report_user больше не имеет привилегий SELECT на таблицу sales_data, поскольку привилегия была отозвана у роли reporting_role.

Выйдите из оболочки psql.
```
\q
```

Резюме

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