[Из песочницы] Техника безопасности при работе с PostgreSQL
Тот самый список правил
Почти за каждым из пунктов ниже стоит печальная история, полная страданий и превознемоганий. А словом «боль!» помечены пункты, выработанные историями, при воспоминании о которых я все еще вздрагиваю по ночам.
- Версионируйте схему базы данных
Схема базы данных — это код, который вы написали. Она должна лежать в системе контроля версий и версионироваться с остальным проектом. В случае PostgreSQL мне больше всего для этих целей понравился Pyrseas. Он превращает схему со всеми специфичными для PostgreSQL объектами в yaml файл, который версионируются. С таким файлом удобно работать в ветках и сливать изменения, в отличие от чистого SQL. Финальным шагом yaml файл сравнивается со схемой базы данных и автоматически генерируется миграция на SQL.
- Боль! Никогда не применяйте изменения сразу на боевую базу
Даже если изменение простое, невероятно срочное и очень хочется. Вначале нужно применить его на базе разработчиков, закомитить в ветку, изменения применить на базе транка (идентичной боевой базе). И только потом, когда все хорошо в транке, применять на боевой базе. Это долго, параноидально, но спасает от многих проблем.
- Боль! Перед тем, как написать delete или update, напишите where
А еще перед тем, как запустить код, выдохните, просчитайте до трех и удостоверьтесь, что вы в сессии нужной базы. Про trancate я вообще молчу, без трех «Отче наш» даже не думайте запускать, аминь!
- Test Driven Development
Вначале всегда пишите тесты, а потом создавайте объекты базы данных. Речь идет про любые объекты: схемы, таблицы, функции, типы, расширения — никаких исключений! Вначале это кажется тяжко, но впоследствии вы много раз скажите себе спасибо. Даже при первичном создании схемы легко что-то упустить. А при рефакторинге таблиц через полгода только написанные вами тесты уберегут от внезапного выстрела в ногу в какой-нибудь функции. В случае PostgreSQL есть замечательное расширение pgTAP. Я рекомендую для каждой схемы создавать дополнительно схему «имя_схемы_tap», в которой писать функции для тестирования. А потом просто прогонять тесты через pg_prove.
- Не забывайте настроить PITR
Я боюсь выступить в роли Капитана Очевидности, но у любой базы должен быть настроен бэкап. При том желательно такой, чтобы иметь возможность восстанавливать базу на любой момент времени. Это необходимо не только для восстановления при сбоях, но и дает много интересных возможностей разработчикам для работы в определенных временных срезах базы. В PostgreSQL для этого есть barman.
- Согласованность данных
Несогласованные данные в базе никогда не приводили ни к чему хорошему. Даже небольшое их количество может легко превратить всю базу в мусор. Поэтому никогда не стоит пренебрегать нормализацией и ограничениями вроде внешних ключей и проверок. Используйте денормализованную форму (например, jsonb) только удостоверившись, что не получается реализовать схему в нормализованном виде с приемлемым уровнем сложности и производительности — денормализованный вид потенциально может привести к несогласованным данным. На все доводы сторонников денормализации отвечайте, что нормализацию придумали не просто так и молчите с многозначительным видом.
- Создавайте внешние ключи deferrable initially deferred
В таком случае вы откладываете проверку ограничения на конец транзакции, что позволяет безнаказанно получать несогласованность в ходе ее выполнения (но в конце все согласуется или вызовет ошибку). Более того, меняя флаг внутри транзакции на immediate, можно принудительно сделать проверку ограничения в нужный момент транзакции.
- Не используйте схему public
Это служебная схема для функций из расширений. Для своих нужд создавайте отдельные схемы. Относитесь к ним как к модулям и создавайте новую схему для каждого логически обособленного набора сущностей.
- Отдельная схема для API
Для функций, которые вызываются на стороне приложения, можно создать отдельную схему «api_v_номер_версии». Это позволит четко контролировать, где лежат функции, являющиеся интерфейсами к вашей базе. Для наименования функций в этой схеме можно использовать шаблон «сущность_get/post/patch/delete_аргументы».
- Триггеры для аудита
Лучше всего триггеры подходят для аудита действий. Так же рекомендую создать универсальную триггерную функцию, для записи любых действий произвольной таблицы. Для этого нужно вытащить данные о структуре целевой таблицы из information_schema и понять, old или new строка будет вставляться в зависимости от производимого действия. За счет такого решения код становится любовным и прельстивым более поддерживаемым.
Если же вы планируете использовать триггеры для подсчета регистра накоплений, то будьте аккуратны в логике — одна ошибка и можно получить неконсистентные данных. Поговаривают, это очень опасное кунг-фу. - Боль! Импорт данных в новую схему
Самое ужасное, но регулярно происходящее событие в жизни разработчика баз данных. В PostgreSQL очень помогают FDW, тем более их хорошо прокачали в 9.6 (если их разработчики озаботятся, то FDW могут строить план на удаленной стороне). Кстати, есть такая удобная конструкция как «import foreign schema», которая спасает от написания оберток над кучей таблиц. Так же хорошей практикой является иметь набор функции, сохраняющие набор SQL команд для удаления и восстановления существующих в базе внешних и первичных ключей. Импорт рекомендую осуществлять, вначале написав набор view с данными, идентичных по структуре целевым таблицам. И из них сделать вставку, используя copy (не insert!). Всю последовательность SQL команд лучше держать в отдельном версионируемом файле и запускать их через psql с ключом -1 (в единой транзакции). Кстати, импорт — это единственных случай, когда в PostgreSQL можно выключить fsync, предварительно сделав бэкап и скрестив пальцы.
- Не пишите на SQL:1999
Нет, правда, с тех пор много воды утекло: целое поколение выпустилось из школы, мобильники из кирпичей превратились в суперкомпьютеры по меркам 1999 года. В общем, не стоит писать так, как писали наши отцы. Используйте «with», с ним код становится чище и его можно читать сверху вниз, а не петлять среди блоков join’ов. Кстати, если join делается по полям с одинаковым названием, то лаконичнее использовать «using», а не «on». Ну и конечно, никогда не используйте в боевом коде offset. А еще есть такая прекрасная вещь «join lateral», про которую часто забывают — и в этот момент в мире грустит котенок.
- Временные таблицы
Если можете написать запрос без временных таблиц — не раздумывайте и напишите! Обычно CTE, создаваемое конструкцией «with», является приемлемой альтернативой. Дело в том, что PostgreSQL для каждой временной таблицы создает временный файл… и да, еще один грустный котенок на планете.
- Боль! Самый страшный антипаттерн в SQL
Никогда не используйте конструкции вида
select myfunc() from table;
Время выполнения такого запроса возрастает в линейной зависимости от количества строк. Такой запрос всегда можно переписать в нечто без функции, применяемой к каждой строке, и выиграть пару порядков в скорости выполнения. - Главный секрет запросов
Если ваш запрос работает медленно на тестовом компьютере, то в продакшене он работать быстрее не будет. Тут самая лучшая аналогия про дороги с автомобилями. Тестовый компьютер — это дорога с одним рядом. Продакшен сервер — дорога с десятью рядами. По десяти рядам в час пик проедет куда больше машин без пробок, чем по одной полосе. Но если ваша машина — старое ведро, то как Феррари она не поедет, сколько свободных полос ей не давай.
- Используй индексы, Люк!
От того, сколь правильно вы их создадите и будете использовать, зависит, будет запрос выполняться десятые доли секунды или минуты. Я рекомендую ознакомиться с сайтом Маркуса Винанда по устройству b-tree индексов — это лучшее общедоступное объяснение по балансовым деревьям, которое я видел в Интернете. И книжка у него тоже крутая, да.
- group by или window function?
Нет, понятно, window function может больше. Но иногда агрегацию можно посчитать и так и так. В таких случаях я руководствуюсь правилом: если агрегация считается по покрывающим индексам — только group by. Если покрывающих индексов нет, то можно пробовать window function.
- set_config
set_config можно использовать не только для выставление настроек для postgresql.conf в рамках транзакции, но и для передачи в транзакцию пользовательской переменной (если ее заранее определить в postgresql.conf). С помощью таких переменных в транзакции можно очень интересно влиять на поведение вызываемых функций.
- FTS и триграммы
Они чудесны! Они даруют нам полнотекстовый и нечеткий поиск при сохранении всей мощи SQL. Просто не забывайте ими использоваться.
- Вызов собственных исключений
Зачастую, в большом проекте приходится вызывать много исключений со своими кодами и сообщениями. Чтобы в них не запутаться, есть вариант создать для исключений отдельный тип вида «код — текст исключения», а так же функции для их вызова (обертка над «raise»), добавления и удаления. А если вы покрыли все свои объекты базы тестами, то вы не сможете случайно удалить код исключения, который уже где-либо используется.
- Много паранойи мало не бывает
Хорошая практика — не забывать настроить ACL на таблицы, а функции запускать с «security definer». Когда функции работают только на чтение, фэншуй требует выставлять у них флаг «stable».
- Боль! Вишенка на торте
Никогда нельзя перенаправлять клиента с сервера в базу под собственным, уникальным пользователем БД. Даже если вам кажется, что:
- можно настроить на уровне PostreSQL безопасность для групп штатными средствами
- пользователь сможет использовать только принадлежащие его группе объекты
- внутренний голос настаивает
… никогда не делайте так, это ловушка! При такой схеме нельзя использовать пулы соединений, и каждый пользователь будет отъедать ресурсоемкое соединение к базе данных. Базы данных держат сотни соединений, а сервера — тысячи. И именно поэтому к базе данных создают несколько подключений под одним пользователем, в которые заворачивают сотни пользователей с сервера. Не делайте так, одним словом, иначе придется при росте нагрузки все переписывать.
Комментарии (1)
31 октября 2016 в 16:54
0↑
↓
Вы не поясните выкрик «Боль!» в заголовках? Это те грабли, на которые вы сами наступали?
