Trino + OTP верификация запросов в bi – параноя.mode = true
Представьте: дашборд в BI видят все сотрудники. Закрыть его нельзя — так исторически сложилось. Но данные нужно скрыть. Решение — динамическая фильтрация на уровне SQL: пользователь вводит логин и одноразовый код (TOTP), а запрос показывает только те строки, которые ему разрешены.
Это не полноценная система безопасности, но это работающий барьер, который можно внедрить без изменения BI-системы. В статье — пошаговая инструкция: от демо-таблиц до настройки прав в Trino, генерации QR-кодов и увеличения окна действия OTP.
Архитектура
- `catalog.schema.dashboard_users` — справочник сотрудников: логин, имя, отдел, должность, руководитель, TOTP-секрет, флаг активности.
- `catalog.schema.dashboard_data` — данные дашборда. У каждой строки есть политика доступа: `ALL`, `OWNER`, `DEPARTMENT`, `MANAGER`, `DIRECTOR`.
- Функция `verify_totp` — Python UDF, проверяет OTP-код по секрету и текущему времени.
- Итоговый запрос — аутентифицирует пользователя и фильтрует строки по атрибутам.
Пользователь вводит логин и OTP как параметры BI. Если они верны — получает свои данные. Если нет — пустой результат.
Шаг 1. Демо-таблицы
Создадим две таблицы в вымышленной схеме `catalog.schema`.
CREATE TABLE catalog.schema.dashboard_users (
login VARCHAR,
employee_name VARCHAR,
department VARCHAR,
position_name VARCHAR,
manager_login VARCHAR,
totp_secret VARCHAR,
is_active BOOLEAN
);
INSERT INTO catalog.schema.dashboard_users VALUES
('vasya', 'Василий', 'sales', 'manager', 'ivan', 'JBSWY3DPEHPK3PXP', true),
('petya', 'Пётр', 'sales', 'analyst', 'vasya', 'KRSXG5CTMVRXEZLU', true),
('masha', 'Мария', 'finance', 'analyst', 'ivan', 'NB2W45DFOIZA', true),
('ivan', 'Иван', 'management','director', null, 'MFRGGZDFMZTWQ2LK', true);
CREATE TABLE catalog.schema.dashboard_data (
row_id BIGINT,
row_title VARCHAR,
amount DECIMAL(12,2),
owner_login VARCHAR,
department VARCHAR,
manager_login VARCHAR,
access_policy VARCHAR
);
INSERT INTO catalog.schema.dashboard_data VALUES
(1, 'Личная строка Васи', 1000.00, 'vasya', 'sales', 'ivan', 'OWNER'),
(2, 'Личная строка Пети', 2000.00, 'petya', 'sales', 'vasya', 'OWNER'),
(3, 'Общая строка отдела Sales',3000.00, null, 'sales', null, 'DEPARTMENT'),
(4, 'Общая строка Finance', 4000.00, null, 'finance', null, 'DEPARTMENT'),
(5, 'Строка команды Васи', 5000.00, 'petya', 'sales', 'vasya', 'MANAGER'),
(6, 'Строка команды Ивана', 6000.00, 'masha', 'finance', 'ivan', 'MANAGER'),
(7, 'Директорская строка', 7000.00, null, null, null, 'DIRECTOR'),
(8, 'Публичная строка', 8000.00, null, null, null, 'ALL');Либо можно сгенерировать коды разово прямо в запросе.
Шаг 1.1. Массовая генерация TOTP-секретов и QR-кодов
Когда пользователей много, вручную придумывать Base32-секреты неудобно. Можно сгенерировать их прямо в Trino при вставке в таблицу `dashboard_users`. Ниже — готовый блок для демо и тестов.
Важно: `random()` в Trino не является криптостойким генератором. Для продакшена секреты лучше генерировать вне Trino (например, через `openssl rand -base64 20` или Python `pyotp.random_base32()`) и вставлять уже готовыми. Для демонстрации и параноидального режима «лишь бы закрыть дашборд» этого достаточно.
Вставка пользователей с автоматической генерацией секретов
INSERT INTO catalog.schema.dashboard_users (
login,
employee_name,
department,
position_name,
manager_login,
totp_secret,
is_active
)
SELECT
login,
employee_name,
department,
position_name,
manager_login,
-- Генерация Base32-секрета длиной 16 символов (80 бит)
array_join(
transform(
sequence(1, 16),
i -> substr(
'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567',
CAST(floor(random() * 32) + 1 AS INTEGER),
1
)
),
''
) AS totp_secret,
true AS is_active
FROM (
VALUES
('vasya', 'Василий', 'sales', 'manager', 'ivan'),
('petya', 'Пётр', 'sales', 'analyst', 'vasya'),
('masha', 'Мария', 'finance', 'analyst', 'ivan'),
('ivan', 'Иван', 'management','director', CAST(NULL AS VARCHAR))
) AS src (
login,
employee_name,
department,
position_name,
manager_login
);Как получить строку для QR-кода
После вставки можно сразу сформировать URI формата `otpauth://`, который останется только превратить в QR-код любым генератором:
SELECT
login,
totp_secret,
'otpauth://totp/DemoBI:' || login
|| '?secret=' || totp_secret
|| '&issuer=DemoBI' AS otpauth_uri
FROM catalog.schema.dashboard_users;Пример результата:
| login | totp_secret | otpauth_uri |
| vasya | JBSWY3DPEHPK3PXP | otpauth://totp/DemoBI:vasya?secret=JBSWY3DPEHPK3PXP&issuer=DemoBI |
| petya | KRSXG5CTMVRXEZLU | otpauth://totp/DemoBI:petya?secret=KRSXG5CTMVRXEZLU&issuer=DemoBI |
Эту строку можно вставить в любой генератор QR-кодов (онлайн или офлайн), а затем отсканировать приложением:
- Google Authenticator,
- Yandex ID,
- KeePass (с плагином TOTP),
- и любым другим менеджером паролей, поддерживающим TOTP.
Настройка длины секрета
- `sequence(1, 16)` — 16 символов Base32 = 80 бит. Этого достаточно для большинства случаев и именно такую длину часто используют по умолчанию.
- Если хотите более длинный секрет, замените `16` на `32`. Тогда получится 160 бит.
- Алфавит `’ABCDEFGHIJKLMNOPQRSTUVWXYZ234567’` — это стандартный Base32-алфавит (без цифр 0, 1, 8, 9). Менять его не нужно.
Если нужен детерминированный секрет (не рекомендуется)
Иногда для тестов хотят, чтобы секрет зависел от логина. Можно использовать хеш:
substr(
upper(to_base32(from_utf8(login || 'some_salt'))),
1, 16
) AS totp_secretНо это небезопасно: зная логин и соль, можно вычислить секрет. Для реальной эксплуатации используйте случайную генерацию вне Trino.
Шаг 2. Inline-функция для тестов в DBeaver
В DBeaver можно использовать `WITH FUNCTION` — Python-код прямо в запросе. Это удобно для отладки, но не работает в BI, потому что BI схлопывает запрос в одну строку и ломает форматирование Python.
WITH FUNCTION verify_totp(secret VARCHAR, code VARCHAR, time_step BIGINT)
RETURNS BOOLEAN
LANGUAGE PYTHON
WITH (handler = 'verify_handler')
AS $$
import hmac, hashlib, struct, base64
def verify_handler(secret_b32, code, time_step):
if secret_b32 is None or code is None or time_step is None:
return False
code = str(code).strip()
if not code.isdigit() or len(code) != 6:
return False
secret = str(secret_b32).strip().replace(" ", "").upper()
padding = "=" * ((8 - len(secret) % 8) % 8)
try:
key = base64.b32decode(secret + padding, casefold=True)
except Exception:
return False
current = int(time_step // 30) # 30 секунд — стандарт TOTP
for offset in (-1, 0, 1):
counter = current + offset
msg = struct.pack(">Q", counter)
digest = hmac.new(key, msg, hashlib.sha1).digest()
o = digest[19] & 15
expected = (struct.unpack(">I", digest[o:o+4])[0] & 0x7fffffff) % 1000000
expected_code = f"{expected:06d}"
if hmac.compare_digest(expected_code, code):
return True
return False
$$
SELECT verify_totp('JBSWY3DPEHPK3PXP', '123456', CAST(to_unixtime(now()) AS BIGINT));Важно: тело функции должно начинаться с новой строки после `$$`. Если BI отправляет всё в одну строку — будет ошибка `Function definition must start with a newline after opening quotes`.
Шаг 3. Регистрация функции администратором
Чтобы BI-запросы не содержали Python-код, функцию нужно создать один раз на стороне Trino. Это делает администратор или пользователь с правами на создание функций.
DROP FUNCTION IF EXISTS catalog.schema.verify_totp(VARCHAR, VARCHAR, BIGINT);
CREATE FUNCTION catalog.schema.verify_totp(
secret VARCHAR,
code VARCHAR,
time_step BIGINT
)
RETURNS BOOLEAN
LANGUAGE PYTHON
WITH (handler = 'verify_handler')
AS $$
import hmac, hashlib, struct, base64
def verify_handler(secret_b32, code, time_step):
if secret_b32 is None or code is None or time_step is None:
return False
code = str(code).strip()
if not code.isdigit() or len(code) != 6:
return False
secret = str(secret_b32).strip().replace(" ", "").upper()
padding = "=" * ((8 - len(secret) % 8) % 8)
try:
key = base64.b32decode(secret + padding, casefold=True)
except Exception:
return False
current = int(time_step // 30)
for offset in (-1, 0, 1):
counter = current + offset
msg = struct.pack(">Q", counter)
digest = hmac.new(key, msg, hashlib.sha1).digest()
o = digest[19] & 15
expected = (struct.unpack(">I", digest[o:o+4])[0] & 0x7fffffff) % 1000000
expected_code = f"{expected:06d}"
if hmac.compare_digest(expected_code, code):
return True
return False
$$;Проверка:
SELECT catalog.schema.verify_totp(
'JBSWY3DPEHPK3PXP',
'465617',
CAST(to_unixtime(now()) AS BIGINT)
) AS is_valid;Примечание: в вашей среде создание функции может быть не разрешено без настройки прав. Об этом — следующий шаг.
Шаг 4. Настройка прав доступа
Нужно разрешить группе admins создавать функции в схеме `catalog.schema`, а группе bi_users — только вызывать `verify_totp`. В Trino это делается через system access control.
Вариант A. File-based ACL
Файл `/etc/trino/access-control.properties`:
access-control.name=file
security.config-file=/etc/trino/rules/access-control.json
security.refresh-period=1mФайл `access-control.json`:
{
"catalogs": [
{
"group": "admins",
"catalog": "catalog",
"allow": "all"
},
{
"group": "bi_users",
"catalog": "catalog",
"allow": "read-only"
}
],
"schemas": [
{
"group": "admins",
"catalog": "catalog",
"schema": "schema",
"owner": true
},
{
"group": "bi_users",
"catalog": "catalog",
"schema": "schema",
"owner": false
}
],
"functions": [
{
"group": "admins",
"catalog": "catalog",
"schema": "schema",
"function": ".*",
"privileges": ["EXECUTE", "GRANT_EXECUTE", "OWNERSHIP"]
},
{
"group": "bi_users",
"catalog": "catalog",
"schema": "schema",
"function": "verify_totp",
"privileges": ["EXECUTE"]
}
]
}Вариант B. OPA (Rego)
Если Trino использует OPA, политика может выглядеть так:
package trino
default allow := false
is_admin {
input.context.identity.groups[_] == "admins"
}
is_bi_user {
input.context.identity.groups[_] == "bi_users"
}
is_target_schema {
input.action.resource.catalog.name == "catalog"
input.action.resource.schema.name == "schema"
}
is_verify_totp {
input.action.resource.catalog.name == "catalog"
input.action.resource.schema.name == "schema"
input.action.resource.function.name == "verify_totp"
}
allow {
is_admin
is_target_schema
input.action.operation == "CreateFunction"
}
allow {
is_admin
is_target_schema
input.action.operation == "DropFunction"
}
allow {
is_admin
is_target_schema
input.action.operation == "ExecuteFunction"
}
allow {
is_bi_user
is_verify_totp
input.action.operation == "ExecuteFunction"
}Точные названия операций (`CreateFunction`, `ExecuteFunction`) лучше уточнить в логах OPA при тестовом запросе.
Шаг 5. Запрос для BI (без Python)
После регистрации функции BI-запрос становится обычным SQL. Пользователь вводит `:login` и `:otp` как параметры.
WITH auth_user AS (
SELECT
login,
employee_name,
department,
position_name,
manager_login
FROM catalog.schema.dashboard_users
WHERE login = lower(trim(':login'))
AND is_active = true
AND catalog.schema.verify_totp(
totp_secret,
trim(':otp'),
CAST(to_unixtime(now()) AS BIGINT)
)
LIMIT 1
)
SELECT
d.row_id,
d.row_title,
d.amount,
d.owner_login,
d.department,
d.manager_login,
d.access_policy,
a.login AS current_login,
a.employee_name AS current_employee_name,
a.department AS current_department,
a.position_name AS current_position_name,
CASE
WHEN d.access_policy = 'ALL' THEN 'Видно всем авторизованным'
WHEN d.access_policy = 'OWNER' AND d.owner_login = a.login THEN 'Видно владельцу строки'
WHEN d.access_policy = 'DEPARTMENT' AND d.department = a.department THEN 'Видно сотруднику отдела'
WHEN d.access_policy = 'MANAGER' AND d.manager_login = a.login THEN 'Видно руководителю'
WHEN d.access_policy = 'DIRECTOR' AND a.position_name = 'director' THEN 'Видно директору'
ELSE null
END AS access_reason
FROM catalog.schema.dashboard_data d
CROSS JOIN auth_user a
WHERE
d.access_policy = 'ALL'
OR (d.access_policy = 'OWNER' AND d.owner_login = a.login)
OR (d.access_policy = 'DEPARTMENT' AND d.department = a.department)
OR (d.access_policy = 'MANAGER' AND d.manager_login = a.login)
OR (d.access_policy = 'DIRECTOR' AND a.position_name = 'director');Важно про параметры: если BI подставляет их как сырой текст, нужны кавычки: `’:login’`, `’:otp’`. Если BI использует bind-параметры — кавычки не нужны: `:login`, `:otp`. В предыдущих ошибках (`Column ‘jbswy3dpehpk3pxp’ cannot be resolved`) видно, что в вашем случае нужны именно кавычки.
Шаг 6. Логика доступа
- `auth_user` — возвращает 0 строк, если логин не найден, пользователь неактивен или OTP неверный.
- `CROSS JOIN auth_user` — если `auth_user` пуст, результат всего запроса пуст.
- `WHERE` с `OR` — оставляет только те строки, которые разрешены политикой:
- `ALL` — всем авторизованным;
- `OWNER` — владельцу (`owner_login = a.login`);
- `DEPARTMENT` — сотруднику того же отдела;
- `MANAGER` — руководителю (`manager_login = a.login`);
- `DIRECTOR` — директору (`position_name = ‘director’`).
Пример: `vasya` увидит свои строки, строки отдела `sales`, строки, где он руководитель, и публичные. `petya` — только свои, отдел `sales` и публичные.
Шаг 7. Генерация QR-кода для OTP
Каждому пользователю нужно выдать TOTP-секрет и удобно передать его в приложение-аутентификатор. Стандартный способ — использовать URI формата `otpauth://`.
Пример для пользователя `vasya`:
otpauth://totp/DemoBI:vasya?secret=JBSWY3DPEHPK3PXP&issuer=DemoBIЗдесь:
- `DemoBI:vasya` — метка, которая отобразится в приложении. Можно заменить на название отчёта, отдела или что угодно, например `SalesDashboard:vasya`.
- `secret=JBSWY3DPEHPK3PXP` — Base32-секрет.
- `issuer=DemoBI` — имя издателя, обычно название компании или системы.
Эту строку можно вставить в любой генератор QR-кодов (онлайн или офлайн). После генерации QR-код сканируется приложением:
- Google Authenticator,
- Yandex ID,
- KeePass (с плагином TOTP),
- и любым другим менеджером паролей, поддерживающим TOTP.
Это стандарт, поэтому совместимость широкая. QR-код можно распечатать или отправить пользователю.
Совет: если у вас много пользователей, сгенерируйте QR-коды автоматически из таблицы `dashboard_users`, подставляя логин и секрет.
Шаг 8. Увеличение окна действия OTP (2–5 минут)
По умолчанию TOTP-код действует 30 секунд. Это может быть неудобно: пользователь не успевает ввести код. Можно увеличить окно действия, изменив функцию.
В Python-коде есть строка:
current = int(time_step // 30)Здесь `30` — это количество секунд в одном временном шаге. Если заменить `30` на `300`, то код будет действителен 5 минут (300 секунд). При этом приложение-аутентификатор продолжит генерировать коды каждые 30 секунд, но сервер будет принимать любой код, сгенерированный в течение этих 5 минут. Это удобно и безопасно: окно не бесконечное, но достаточное, чтобы успеть ввести код.
Пример изменённой функции:
current = int(time_step // 300) # 5 минут
for offset in (-1, 0, 1):
...Если оставить смещения `-1, 0, 1`, то общее окно станет 15 минут (5 минут назад, текущие 5 минут, 5 минут вперёд). Это может быть избыточно. Лучше убрать смещения и проверять только текущий интервал:
current = int(time_step // 300)
counter = current
msg = struct.pack(">Q", counter)
...
# без цикла forТогда код будет действителен ровно 5 минут.
Если хотите 2 минуты — используйте `120` вместо `300`.
Важно: после изменения функции её нужно пересоздать ( `DROP FUNCTION` и `CREATE FUNCTION` ). Все пользователи автоматически начнут работать с новым окном.
Шаг 9. Безопасность
- Секреты TOTP нельзя хранить в открытой схеме. Лучше вынести `dashboard_users` в закрытую схему, например `security.users`.
- BI-пользователи не должны иметь прямой доступ к таблице с секретами. Только к функции и к данным.
- OTP-код попадает в логи Trino и BI. Логи могут быть доступны администраторам или храниться в системе. Это не смертельно, потому что код короткоживущий, но помните: любой, кто видит лог, может увидеть введённый OTP. Если окно действия увеличено до 5 минут, риск возрастает. По возможности используйте реального пользователя из аутентификации BI, а не ручной ввод логина.
- Права на создание функций — только у группы `admins`. BI-пользователям — только `EXECUTE` на конкретную функцию.
Заключение
Мы построили параноидальный, но рабочий механизм: OTP-аутентификация + динамическая фильтрация строк на уровне SQL. Это не заменяет полноценный RBAC, но позволяет быстро закрыть дашборд от посторонних глаз без изменения BI-системы.
Что проверено: inline-функция в DBeaver, запрос с фильтрацией, логика доступа.
Что требует настройки: регистрация функции и права в Trino. После настройки прав (шаг 4) функция создаётся один раз, а BI-запросы становятся чистыми и безопасными.
Удачи в паранойе! :)
