Postgresql: Интерфейс запросов к базе данных по http. Конфигурация базы данных

Разворачивание схемы

Для простоты разворачивания схемы в postgresql написан скрипт на python. В скрипте необходимо указать параметры пользователя с правами создания баз данных, имя создаваемой базы и имя схемы конфигурации. Скрипт будет посоелрвательно загружать и выполнять sql-скрипты, хранящиеся в этом же каталоге. Основные пояснения приведены в комментариях sql-скриптов. Отдельные моменты на которых следует оснановиться подробнее приведятся в данной публикации ниже.

query_check.sql

Чтобы получить нужные данные, пользователь по http обращается с запросом к приложению. При этом в параметрах запроса он должен передать следующие данные - 'project' - название проекта из которого необходимо извлечь данные - 'query' - псевдоним запроса - 'params' - словарь параметров запроса. ключ - псевдоним параметра, значение - значение параметра, ограничевающее выборку данных - 'user' - имя пользователя - 'password': хеш его пароля

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

• проверка авторизации пользователя. Если пользователь не авторизован, ему автоматически присваивается группа привелегий 'anonymous'
• проверка существования запрашиваемых данных в данном проекте. В случае неудачи возвращается соотвествующее информационное сообщение.
• проверка привелегий пользователя на получение данных в проекте. В случае неудачи пользователю возвращается информационное сообщение.

Следующий sql-запрос в выполняет первые две проверки и возвращает список запросов ПОТЕНЦИАЛЬНО доступных для пользователя

select * from config2.request_sql_list as rsl join
        --идентифицируем пользователя и определяем проект, к которому он хочет выполнить запрос
        (select * from config2.projects as p join
                (select * from config2.users as u join config2.privilege_groups as pg on(u.group_id=pg.id)
                        where u.user_name='admin'
                        and u.password_hash='p1') as u_pg
        on (p.id=u_pg.project_id)
        where p.project_name='zakupki') as p_u_pg
using (project_id)

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

Однако, выполнять данный запрос проверки при каждом обращении пользователя к проекту - слишком накладная операция, которая будет нагружать нашу бд. В то же время, добавление нового пользователя, и тем более, нового проекта и прав привелегий для него - относительно редкая операция. Поэтому логично создать промежуточное представление (таблицу), содержашую все необходимые для проверки данные и обновлять ее как только изменится любая из таблиц пользователей,пользовательских запросов, проектов или прав привелегий. Для этого создается материализованное представление rsl_p_u_pg:

• id и porject_id (дублирующее поле) - идентификатор проекта
• project_name - имя проекта
• pg_id - идентификатор группы превилегий
• group_name - имя группы привелегий
• u_id - идентификатор пользователя
• user_name - имя пользователя
• password_hash - хеш пароля пользователя
• rsl_id - идентификатор запроса
• alias - псевдоним запроса
• sql_query - строка запроса
• privilege - привелегии групп на выполнение запроса

Созданное материализованное представление сохраняет результат в виде физической таблицы. Эта таблица статична в том смысле, что изменения в одной из исходных таблиц, например, добавление нового пользователя, не отразятся в нашем представлении автоматически. Чтобы увидеть изменения в представлении его нужно обновить. Чтобы обновление выполнялось автоматически, удобно создать триггер update_view для каждой исходной таблицы, который будет выполнять обновления после каждой из операций добавления, удаления или изменения записи

Проверка прав привелегий (rule-check.sql)

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

select * from config2.rsl_p_u_pg
        where
                user_name='zorro' and
                password_hash='lll' and
                project_name='zakupki' and
                alias='getCount'

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

Функция getQuery помогает проверить привелегию прав выполнения пользовательского запроса

select config2.getQuery('admin', 'p1', 'zakupki', 'getRecords')

В случае успешного выполнения, команда вернет текст запроса. Подставление пользовательских параметров в запрос, выполняется средствами wsgi-скрипта, прочитать можно здесь !!!

Если пользователь не идентифицирован, не имеет привелегий на выполнение команды выполняется поиск этого запроса для группы anonymous. Если группа имеет привелегии на выполнение запроса - он возвращается. Если нет - возвращается пустая строка. Если запрос для проекта не определен, команда вернет пустое значение

Логирование обращений пользователей (create-log.sql)

Логирование является полезной функцией базы данных для анализа пользовательских запросов на предмет оптимизации и безопасности. Будем логировать следующие данные

• параметры пользователя на выполнение запроса (имя, пароль, проект, псевдоним)
• временная метка, пользоватеского запроса

Чтобы сохранять в таблице все обращения пользователей, в функцию getQuery была добавлена строка

INSERT INTO config2.log_requests(request_time, user_name, request_alias, project, passwd)
VALUES (now(), usr, al, project, passwd);

Таблица log_requests будет быстро увеличиваться в размере, поэтому предусматриваем патриционирование. Логи запросов пользователей будут группироваться в таблицах по месяцам. За этим будет следить функция add_log и триггер add_log_trigger

Для размещение таблицы логов создается отдельная схема logs. Это делается из соображений безопасности. Пользователь postgresql от имени которого будут выполняться запросы должен иметь право создавать дочерние таблицы логов и добавлять в них записи. Создание таблиц (и других объектов) можно ограничить только на уровне схемы. Но разрешать создавать пользователю объекты в схеме config, где расположены таблицы пользователей и паролей, таблицы доступа к кпроектам и т.д. не совсем разумно

Чтобы выполнять быстрый поиск по логам в функции предусмотрено создание индексов. С таблицами логов решаются следующие задачи

!!!?? тема индексов пока на пактике не реализована

• анализ запросов пользователя (1) по каким проектам выполняет запросы чаще всего, 2) когда был выполнен последний запрос) 1 - генерировать советы по работе с проектом, 2** - истек ли интервал времени (по привелегиям) для следующего запроса
• анализ запросов по проекту(какие запросы в проекте выполняются наиболее часто) - оптимизировать ресурсы для выполнения запросов

!!! Нужно разобраться какой подход будет лучше - сразу выполнять поиск с указанием конкретных данных для полей. или создать представление по всем вариантам и уже по нему искать конкретные значения параметра

1. Статистика запросов пользователя в разрезе проектов

select

user_name, project, count(request_alias) as c

from

config.log_requests

group by

user_name, project order by c desc

2. Время последнего запроса пользователя к конкретному проекту *** !! Этот запрос будет выполняться часто - при каждом обращении пользователя на получение данных - чтобы проверить истек ли интервал для следующего запроса согласно его привелегиям - Производительности этого запроса уделить особое внимание

select user_name, project, max(request_time)
from
        config.log_requests
--where
--      user_name='admin'
--      and project='zakupki'
group by
        user_name, project

3. Распределение частоты выполнения запросов в разрезе проектов

select
        request_alias,
        project,
        count(request_alias) as c
from
        config.log_requests
group by
        request_alias,
        project order by c desc

4. анализ неавторизованных запросов

4.1. отлавливаем запросы от несуществующих пользователей select * from config.log_requests as l left join config.users as u using (user_name) where u.user_name is null 4.2.

!!! нужно подумать как выявлять попытку взлома - многократная неправильная авторизация

Элементы защиты (user-role.sql)

Здесь рассмотрим два элемента.

• ограничение списка адресов, разрешенных для выполнения пользовательских запросов
• ограничение пользователей имеющих право выполнять пользовательские запросы.

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

Чтобы ограничить пользователей имеющих право на выполнение запросов применим следующий подход. Все поступающие запросы пользователей проверяются скриптом-роутером (в нашей реализации wsgi-скриптом на python) и уже от него выполняется команда сначала на проверку возможности получения данных пользователем с точки зрения доступа и привелегий, а затем, и на получение данных запрошенных пользователем. Данный подход дает нам возможность предоставить право на выполнение запросов к базе только для одного пользователя (о котором конечные пользователи ничего не знают)

Создание пользователя postgresql с нужными превелегиями - достаточно обшмрная тема, поэтому она была вынесена в отдельную статью.

Внимание !!! При разворачивании приложения с помощью python-скрипта пользователь задается с паролем по умолчанию. Позже необходимо обязательно пароль поменять.

Выполнение пользовательских запросов

После размышлений логику выполнения пользовательских запросов решено было сделать средствами postgresql. Логика реализуется через набор функций, описанных в файле !!!!

1. Скрипт-роутер на python от имени пользователя external_user направляет sql-запрос со следующими параметрами

• имя пользователя
• хеш пароля
• имя проекта
• псевдоним запроса
• значения параметров запроса

sql-запрос выполняет следующие проверки

• сущестование данного запроса в проекте
• идентификацию пользователя

Если пользователь не идентифицирован - ему присваивается группа анонимных пользователей с соотвествующими правами выполнения указанного запроса.

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

2. Создаем функцию которая на входе будет получать шаблон sql-запроса (шаг 1) и значения параметров, переданных пользователем а на выходе - готовый sql-запрос.

Здесь нужно учитывать ряд ограничений

1. При передаче значений параметров пользователя их нужно приводить к типу text
2. при составлении шаблона запроса в таблице request_sql_list нужно учитывать типы параметров
3. при вызове функции на данном этапе нужно знать порядок аргументов в шаблоне sql-запроса.

3. Функция !!! выполняет подготовленный на шаге 2 запрос и возвращает результат. Независимо от объема полученных данных весь результат будет свернут в один массив json (в результате работы функция !!! вернет отношение из одной строки и обного столбца). Все результаты пользовательского запроса будут в одной ячейке. Мне показалось это удобным. Если не так нет проблем - нужно только переписать !!! которая вернет результат в нужном формате