SQL в интерфейсе - React, Postgres и RLS / часть 1

SQL в интерфейсе - React, Postgres и RLS / часть 1

Когда дело доходит до обмена данными между интерфейсом и сервером, REST - самый распространенный способ. По крайней мере, так было до появления GraphQL. Когда я впервые услышал о GraphQL, я подумал, что это все. Одна конечная точка и декларативный способ получения серверных данных обещали более быстрый и простой процесс разработки. Казалось, что мне больше не нужно беспокоить разработчиков серверной части и что получение данных полностью в моем распоряжении.

Но однажды я немного погрузился в то, что меня увлекало несколько вещей:

1. Другой язык запросов
2. JSON как язык запросов (не очень читаемый)
3. Сложные мутации
4. Для эффективных запросов нужны хорошие и сложные преобразователи.

Так что да, я не писал ни одного серьезного приложения с GraphQL, но все же я думаю, что независимость от серверной части будет огромным преимуществом. И если бы мне не нужно было изменять код серверной части, связанный с отображением данных внешнего интерфейса каждый раз, когда изменяется форма этих данных или добавляются новые данные, это было бы большим облегчением.

Конечно, GraphQL имеет свое место и поможет вам, особенно если ваше приложение является одним из таких больших и если оно объединяет несколько источников данных, но для моих сторонних проектов должно быть что-то попроще, но с аналогичными преимуществами.

Мой типичный побочный проект выглядит так:

• есть бэкэнд-сервер (сейчас Kotlin + Spring / Micronaut)
• бэкэнд подключен к базе данных Postgres
• бэкэнд разделен на запрашивающую и записывающую части (простая форма CQS)
• бэкэнд использует REST для связи
• интерфейс в большинстве случаев построен на React и Typescript и Axios + React Query

Как видите, это типичная архитектура клиент-сервер для небольших приложений. И долгое время у меня было ощущение, что написание части запросов, добавление все новых и новых контроллеров, репозиториев и различных вариантов наборов данных - это не что иное, как ненужная работа только для удовлетворения требований внешнего интерфейса. Так почему бы не переместить объявление запросов sql прямо во внешний интерфейс? Звучит интересно, даже круто, но есть несколько проблем:

• как насчет безопасности?
• доступ к необработанным данным может быть опасным
• как ограничить доступ к данным?
• как версию модели базы данных?

Прежде чем ответить на эти вопросы, позвольте мне показать вам, как это сейчас выглядит в моем коде на самом раннем этапе:

const { total, data, offset, limit, loading } = usePaginatedSqlQuery
<{id: number, first_name: string, last_name: string}>(
    'clients', { currentPage: 2, pageSize: 10 },
    sql`
        select id, first_name, last_name
          from client
         order by id
    `
);

Здесь вы видите вызов ловушки запроса sql (его версия с разбивкой на страницы, которая добавляет модификаторы limit и offset, а также возвращает общее количество записей), который передает запрос sql на бэкэнд. и возвращает массив записей структуры, объявленной как универсальный тип в usePaginatedSqlQuery:

{
    total: 20,
    offset: 11,
    limit: 10,
    data: [{
        id: 1,
        first_name: "John",
        last_name: "Doe"
    }, {
        id: 2,
        first_name: "Mike",
        last_name: "Miller"
    }]
}

Я считаю, что это одновременно мощный и простой инструмент, который дает следующие преимущества:

1. Нет необходимости изучать другой язык запросов. Просто используйте sql, который хорошо известен десятилетиями. Здесь вы можете делать все: объединения, подзапросы, преобразование данных, вызов функций и так далее.
2. Бэкэнд-код может быть значительно сокращен, нет необходимости писать код, который является посредником для вашего внешнего интерфейса (конечно, публичный api для внешнего использования - это нечто иное).
3. Производительность запроса можно предсказать и при необходимости легко оптимизировать, не затрагивая серверную часть (в большинстве случаев).
4. Не нужно писать «волшебные» преобразователи данных. Эффективный движок СУБД работает непосредственно на нас.
5. Легкий доступ к схеме query и простой способ экспериментировать с ней, просто используйте свой любимый инструмент db.

Так это безопасно?

С точки зрения внешнего интерфейса это обычный REST-вызов конечной точки / api / query, защищенной с помощью JWT. Ничего необычного. А теперь интересная часть на стороне бэкэнда:

1. Конечная точка / api / query использует отдельное соединение с базой данных с пользователем u_query
2. u_query имеет доступ только к схеме query.
3. Схема query содержит только представления базы данных
4. u_query пользователь может выбирать только в представлениях
5. u_query пользователь не имеет прав на создание, изменение или удаление элементов схемы.
6. почти к каждой таблице, связанной с представлением, применена политика безопасности строк, ограничивающая доступ к определенным строкам

На самом деле ничего не изменилось. У Frontend по-прежнему нет прямого доступа к базе данных. Доступ к данным защищен на двух уровнях. Сначала в серверном приложении (авторизация, подключение к базе данных), затем на уровне самой базы данных (соответствующие права пользователя, политики строк). Ничего подобного не сработает:

update users set password = 'abc'
delete from customer where id = 1
alter table books remove column title

Ограниченный доступ к данным

Но что с доступом к данным пользователем приложения, у которого не должно быть возможности читать все строки. Здесь срабатывает RLS, механизм, представленный в Postgres 9.5.

Представим, что есть таблица client:

 Column     |           Type           
------------+--------------------------
 id         | bigint                   
 first_name | character varying        
 last_name  | character varying        
 owner_id   | bigint

со следующими данными:

 id | first_name | last_name | owner_id
----+------------+-----------+----------
 22 | Mike       | Beva      |       4
 23 | Jim        | Hester    |       4
 24 | Daisy      | Ryder     |       5
 25 | Orla       | Irving    |       5

owner_id ссылается на таблицу user (id).

Итак, если вошедший пользователь - это пользователь с идентификатором 4, запрос внешнего интерфейса:

select * from client

должен возвращать первые две строки, где owner_id равно 4. Но как это сделать, не добавляя явно предложение where (where owner_id = 4) в запрос. Добавление этого на уровне ловушки будет работать (с точки зрения отображаемых данных), но все же все данные будут доступны для вошедшего в систему пользователя. Затем он должен быть добавлен на уровне базы данных. Это можно сделать двумя способами:

Используя представления с условием where:

create view query.client as select * from client where id = current_setting('app.user.id')::bigint

Используя Текущий уровень безопасности:

create policy owner_policy on client as permissive for select to u_query using (owner_id = current_setting('app.user.id')::bigint)

create view query.users as select if, first_name, last_name from users