---

Для выполнения задания используем **pgAdmin 4** (стандартная программа для PostgreSQL).

---

### Шаг 1: Создание базы данных
1.  Откройте **pgAdmin 4**.
2.  В левом дереве нажмите правой кнопкой на **Databases** —> **Create** —> **Database...**
3.  В поле "Database" введите название: `audit_exam`. Нажмите **Save**.
4.  Нажмите правой кнопкой на созданную базу `audit_exam` —> выберите **Query Tool**. Откроется окно для ввода кода.

---

### Шаг 2: Создание таблиц (Структура)
Скопируйте этот код в окно Query Tool и нажмите **F5** (или иконку молнии):

```sql
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username TEXT NOT NULL,
    email TEXT,
    last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users_audit_log (
    log_id SERIAL PRIMARY KEY,
    target_user_id INT,
    operation_type TEXT,
    changed_by TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    old_data JSONB,
    new_data JSONB
);
```

---

### Шаг 3: Создание логики аудита и уведомлений
Сотрите предыдущий код, вставьте этот и нажмите **F5**:

```sql
CREATE OR REPLACE FUNCTION process_users_audit()
RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO users_audit_log (target_user_id, operation_type, changed_by, new_data)
        VALUES (NEW.user_id, 'INSERT', current_user, to_jsonb(NEW));
        PERFORM pg_notify('db_event', 'New user added: ' || NEW.username);
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO users_audit_log (target_user_id, operation_type, changed_by, old_data, new_data)
        VALUES (NEW.user_id, 'UPDATE', current_user, to_jsonb(OLD), to_jsonb(NEW));
        PERFORM pg_notify('db_event', 'User updated: ' || NEW.username);
    ELSIF (TG_OP = 'DELETE') THEN
        INSERT INTO users_audit_log (target_user_id, operation_type, changed_by, old_data)
        VALUES (OLD.user_id, 'DELETE', current_user, to_jsonb(OLD));
        PERFORM pg_notify('db_event', 'User deleted: ' || OLD.username);
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE FUNCTION process_users_audit();
```

---

### Шаг 4: Ответ на теоретические вопросы (для экзаменатора)

**Вопрос 1: Что такое аудит и как его настроить?**
> Аудит — это фиксация действий пользователей (кто, когда и что изменил). В PostgreSQL это реализуется через триггеры, которые перехватывают операции INSERT/UPDATE/DELETE и записывают старые и новые значения в таблицу-журнал. Также используется механизм NOTIFY для оповещения внешних систем в реальном времени.

**Вопрос 2: Защита данных, политики и роли?**
> 1. **Управление доступом:** Команды GRANT (дать права) и REVOKE (забрать). Создание ролей (например, `CREATE ROLE readonly`).
> 2. **Политики безопасности (RLS):** Ограничение доступа к строкам. Пользователь видит только свои данные.
> 3. **Шифрование:** Использование расширения `pgcrypto` для шифрования паролей и SSL для защиты канала связи.

---

### Шаг 5: Демонстрация работы (что показывать при сдаче)

**1. Создайте запись (имитация работы):**
```sql
INSERT INTO users (username, email) VALUES ('ivan_test', 'ivan@mail.ru');
UPDATE users SET email = 'ivan_new@mail.ru' WHERE username = 'ivan_test';
DELETE FROM users WHERE username = 'ivan_test';
```

**2. Сгенерируйте отчет за последний месяц:**
```sql
SELECT 
    changed_at AS "Дата",
    operation_type AS "Операция",
    changed_by AS "Кто изменил",
    new_data AS "Новые данные"
FROM users_audit_log
WHERE changed_at >= NOW() - INTERVAL '1 month'
ORDER BY changed_at DESC;
```

**3. Как показать уведомления (аналог Mail):**
В Query Tool введите команду:
```sql
LISTEN db_event;
```
Теперь при любом INSERT/UPDATE во вкладке **Messages** (внизу) будет появляться сообщение о событии. Это и есть реализация уведомлений.

---

### Финальный результат:
У вас есть:
1.  Рабочая таблица пользователей.
2.  Автоматический аудит всех изменений.
3.  Запрос для формирования ежемесячного отчета.
4.  Система уведомлений (через NOTIFY), которая заменяет Database Mail в PostgreSQL.