Техника безопасности при работе с PostgreSQL

Так получилось, что я начал работать с PostgreSQL три года назад и за это время умудрился методично собрать все возможные грабли, которые можно вообразить. И сказать по правде, если бы была возможность поделиться с собой трехлетней давности нынешним горьким опытом, моя жизнь была бы куда проще и нервные клетки целее. Именно поэтому я решил написать абсолютно субъективную статью со сводом правил, которых придерживаюсь при разработке на PostgreSQL. Возможно, кому-то эта статья поможет обойти собранные мной грабли (и наступить на другие, ха-ха!).




Тот самый список правил


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

  1. Версионируйте схему базы данных

    Схема базы данных — это код, который вы написали. Она должна лежать в системе контроля версий и версионироваться с остальным проектом. В случае PostgreSQL мне больше всего для этих целей понравился Pyrseas. Он превращает схему со всеми специфичными для PostgreSQL объектами в yaml файл, который версионируются. С таким файлом удобно работать в ветках и сливать изменения, в отличие от чистого SQL. Финальным шагом yaml файл сравнивается со схемой базы данных и автоматически генерируется миграция на SQL.

  2. Боль! Никогда не применяйте изменения сразу на боевую базу

    Даже если изменение простое, невероятно срочное и очень хочется. Вначале нужно применить его на базе разработчиков, закомитить в ветку, изменения применить на базе транка (идентичной боевой базе). И только потом, когда все хорошо в транке, применять на боевой базе. Это долго, параноидально, но спасает от многих проблем.

  3. Боль! Перед тем, как написать delete или update, напишите where

    А еще перед тем, как запустить код, выдохните, просчитайте до трех и удостоверьтесь, что вы в сессии нужной базы. Про truncate я вообще молчу, без трех «Отче наш» даже не думайте запускать, аминь!
    UPD. koropovskiy: полезнее выставлять set autocommit off для текущего сеанса.
    tgz: или перед каждым update и delete пишите begin.

  4. Test Driven Development

    Вначале всегда пишите тесты, а потом создавайте объекты базы данных. Речь идет про любые объекты: схемы, таблицы, функции, типы, расширения — никаких исключений! Вначале это кажется тяжко, но впоследствии вы много раз скажете себе спасибо. Даже при первичном создании схемы легко что-то упустить. А при рефакторинге таблиц через полгода только написанные вами тесты уберегут от внезапного выстрела в ногу в какой-нибудь функции. В случае PostgreSQL есть замечательное расширение pgTAP. Я рекомендую для каждой схемы создавать дополнительно схему «имя_схемы_tap», в которой писать функции для тестирования. А потом просто прогонять тесты через pg_prove.

  5. Не забывайте настроить PITR

    Я боюсь выступить в роли Капитана Очевидности, но у любой базы должен быть настроен бэкап. При том желательно такой, чтобы иметь возможность восстанавливать базу на любой момент времени. Это необходимо не только для восстановления при сбоях, но и дает много интересных возможностей разработчикам для работы в определенных временных срезах базы. В PostgreSQL для этого есть barman.

  6. Согласованность данных

    Несогласованные данные в базе никогда не приводили ни к чему хорошему. Даже небольшое их количество может легко превратить всю базу в мусор. Поэтому никогда не стоит пренебрегать нормализацией и ограничениями вроде внешних ключей и проверок. Используйте денормализованную форму (например, jsonb) только удостоверившись, что не получается реализовать схему в нормализованном виде с приемлемым уровнем сложности и производительности — денормализованный вид потенциально может привести к несогласованным данным. На все доводы сторонников денормализации отвечайте, что нормализацию придумали не просто так и молчите с многозначительным видом.

  7. Создавайте внешние ключи deferrable initially deferred

    В таком случае вы откладываете проверку ограничения на конец транзакции, что позволяет безнаказанно получать несогласованность в ходе ее выполнения (но в конце все согласуется или вызовет ошибку). Более того, меняя флаг внутри транзакции на immediate, можно принудительно сделать проверку ограничения в нужный момент транзакции.
    UPD. В комментариях указывают, что deferrable — неоднозначная практика, которая упрощает ряд задач импорта, но усложняет процесс отладки внутри транзакции и является плохой практикой для начинающих разработчиков. Хоть я упрямо склоняюсь к тому, что лучше иметь deferrable ключи, чем не иметь их, учитывайте альтернативный взгляд на вопрос.

  8. Не используйте схему public

    Это служебная схема для функций из расширений. Для своих нужд создавайте отдельные схемы. Относитесь к ним как к модулям и создавайте новую схему для каждого логически обособленного набора сущностей.

  9. Отдельная схема для API

    Для функций, которые вызываются на стороне приложения, можно создать отдельную схему «api_v_номер_версии». Это позволит четко контролировать, где лежат функции, являющиеся интерфейсами к вашей базе. Для наименования функций в этой схеме можно использовать шаблон «сущность_get/post/patch/delete_аргументы».

  10. Триггеры для аудита

    Лучше всего триггеры подходят для аудита действий. Так же рекомендую создать универсальную триггерную функцию, для записи любых действий произвольной таблицы. Для этого нужно вытащить данные о структуре целевой таблицы из information_schema и понять, old или new строка будет вставляться в зависимости от производимого действия. За счет такого решения код становится любовным и прельстивым более поддерживаемым.
    Если же вы планируете использовать триггеры для подсчета регистра накоплений, то будьте аккуратны в логике — одна ошибка и можно получить неконсистентные данных. Поговаривают, это очень опасное кунг-фу.

  11. Боль! Импорт данных в новую схему

    Самое ужасное, но регулярно происходящее событие в жизни разработчика баз данных. В PostgreSQL очень помогают FDW, тем более их хорошо прокачали в 9.6 (если их разработчики озаботятся, то FDW могут строить план на удаленной стороне). Кстати, есть такая удобная конструкция как «import foreign schema», которая спасает от написания оберток над кучей таблиц. Так же хорошей практикой является иметь набор функции, сохраняющие набор SQL команд для удаления и восстановления существующих в базе внешних и первичных ключей. Импорт рекомендую осуществлять, вначале написав набор view с данными, идентичных по структуре целевым таблицам. И из них сделать вставку, используя copy (не insert!). Всю последовательность SQL команд лучше держать в отдельном версионируемом файле и запускать их через psql с ключом -1 (в единой транзакции). Кстати, импорт — это единственных случай, когда в PostgreSQL можно выключить fsync, предварительно сделав бэкап и скрестив пальцы.

  12. Не пишите на SQL:1999

    Нет, правда, с тех пор много воды утекло: целое поколение выпустилось из школы, мобильники из кирпичей превратились в суперкомпьютеры по меркам 1999 года. В общем, не стоит писать так, как писали наши отцы. Используйте «with», с ним код становится чище и его можно читать сверху вниз, а не петлять среди блоков join'ов. Кстати, если join делается по полям с одинаковым названием, то лаконичнее использовать «using», а не «on». Ну и конечно, никогда не используйте в боевом коде offset. А еще есть такая прекрасная вещь «join lateral», про которую часто забывают — и в этот момент в мире грустит котенок.
    UPD. Используя «with» не забывайте, что результат его выполнения создает CTE, которое отъедает память и не поддерживает индексы при запросе к нему. Так что употребленные слишком часто и ни к месту «with» могут негативно сказаться на производительности запроса. Поэтому не забывайте анализировать запрос через планировщик. «with» особенно хорош, когда нужно получить таблицу, которая будет по-разному использоваться в нескольких частях запроса ниже. И помните, «with» радикально улучшает читабельность запроса и в каждой новой версии PostgreSQL работает все эффективнее. При прочих равных — предпочитайте именно эту конструкцию.

  13. Временные таблицы

    Если можете написать запрос без временных таблиц — не раздумывайте и напишите! Обычно CTE, создаваемое конструкцией «with», является приемлемой альтернативой. Дело в том, что PostgreSQL для каждой временной таблицы создает временный файл… и да, еще один грустный котенок на планете.

  14. Боль! Самый страшный антипаттерн в SQL

    Никогда не используйте конструкции вида
    select myfunc() from table;
    

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

  15. Главный секрет запросов

    Если ваш запрос работает медленно на тестовом компьютере, то в продакшене он работать быстрее не будет. Тут самая лучшая аналогия про дороги с автомобилями. Тестовый компьютер — это дорога с одним рядом. Продакшен сервер — дорога с десятью рядами. По десяти рядам в час пик проедет куда больше машин без пробок, чем по одной полосе. Но если ваша машина — старое ведро, то как Феррари она не поедет, сколько свободных полос ей не давай.

  16. Используй индексы, Люк!

    От того, сколь правильно вы их создадите и будете использовать, зависит, будет запрос выполняться десятые доли секунды или минуты. Я рекомендую ознакомиться с сайтом Маркуса Винанда по устройству b-tree индексов — это лучшее общедоступное объяснение по сбалансированным деревьям, которое я видел в Интернете. И книжка у него тоже крутая, да.

  17. group by или window function?

    Нет, понятно, window function может больше. Но иногда агрегацию можно посчитать и так и так. В таких случаях я руководствуюсь правилом: если агрегация считается по покрывающим индексам — только group by. Если покрывающих индексов нет, то можно пробовать window function.

  18. set_config

    set_config можно использовать не только для выставление настроек для postgresql.conf в рамках транзакции, но и для передачи в транзакцию пользовательской переменной (если ее заранее определить в postgresql.conf). С помощью таких переменных в транзакции можно очень интересно влиять на поведение вызываемых функций.

  19. FTS и триграммы

    Они чудесны! Они даруют нам полнотекстовый и нечеткий поиск при сохранении всей мощи SQL. Просто не забывайте ими пользоваться.

  20. Вызов собственных исключений

    Зачастую, в большом проекте приходится вызывать много исключений со своими кодами и сообщениями. Чтобы в них не запутаться, есть вариант создать для исключений отдельный тип вида «код — текст исключения», а так же функции для их вызова (обертка над «raise»), добавления и удаления. А если вы покрыли все свои объекты базы тестами, то вы не сможете случайно удалить код исключения, который уже где-либо используется.

  21. Много паранойи мало не бывает

    Хорошая практика — не забывать настроить ACL на таблицы, а функции запускать с «security definer». Когда функции работают только на чтение, фэншуй требует выставлять у них флаг «stable».

  22. Боль! Вишенка на торте

    UPD. Никогда нельзя перенаправлять пользователя приложения через сервер в базу данных, взаимно-однозначно транслируя пользователя приложения в пользователя БД. Даже если вам кажется, что при этом можно настроить в БД безопасность для пользователей и их групп штатными средствами PostreSQL, никогда не делайте так, это ловушка! При такой схеме нельзя использовать пулы соединений, и каждый подключенный пользователь приложения будет отъедать ресурсоемкое соединение к базе данных. Базы данных держат сотни соединений, а сервера — тысячи и именно по этой причине в приложениях используют балансировщики нагрузки и пулы соединений. А при трансляции один к одному каждого пользователя в базу данных при росте нагрузки придется ломать схему и все переписывать.
Поделиться публикацией
Похожие публикации
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама
Комментарии 43
  • НЛО прилетело и опубликовало эту надпись здесь
    • +3
      И не просто наступал, но и героически преодолевал последствия. До сих пор вздрагиваю.
    • –9
      Капитан Очевидность, залогинтесь.
      • +5
        Можно, конечно, тешить себя мыслью, что Хабрахабр — это такой своеобразный Олимп, где сидят только наикрутейшие профессионалы, но это не так — хабр читают и обычные джуны, которым всё вышеописанное в новинку. Так зачем же кричать «очевидно»? Кому-то, может, и не очевидно совсем.
        • +3

          Могут быть и не только junior-разработчики, но и люди из других областей, которые хотят что-то новое освоить. И вот такие подборки — это очень ценный ресурс для них. То есть, для нас.

      • 0

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


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

        • +2
          Подскажите, откуда взялось, что «Дело в том, что PostgreSQL для каждой временной таблицы создает временный файл…»? Нигде в документации об этом не написано.
          Повлияет ли на производительность, если временную таблицу создавать в tablespace на RAM-диске?
          • 0
            Для каждой таблицы создается обычный физический файл. Не временный.
            См. реализацию heap_create_with_catalog и конкретно здесь
            • +1
              Извините, я ошибся. Копнул чуть глубже в документацию:
              Each table and index is stored in a separate file. For ordinary relations, these files are named after the table or index's filenode number, which can be found in pg_class.relfilenode. But for temporary relations, the file name is of the form tBBB_FFF, where BBB is the backend ID of the backend which created the file, and FFF is the filenode number.


              И внимание. При старте (если это действительно только при старте, то похоже на баг) postmaster через
              RemovePgTempFiles() в конечном итоге вызывает unlink() для всего, что выглядит как файл временной таблицы.
              • 0
                Судя по форумам tablespace на RAM-диске дает очень высокую вероятность убить всю базу.
                "...losing any tablespace would prevent the database server from starting,
                even if it was only for temporary things" https://www.postgresql.org/message-id/51806248.60401%40darrenduncan.net

                Сообщение датировано 2013 годом. Интересно, как сейчас дела обстоят?
              • +1
                В общем-то большинство пунктов относятся к любой SQL (и не SQL) БД.
                • +1
                  На все доводы сторонников денормализации отвечайте, что нормализацию придумали не просто так и молчите с многозначительным видом

                  Если не используете транзакции, зачем вам тогда РСУБД?
                • +1
                  > Не используйте схему public
                  > Это служебная схема для функций из расширений. Для своих нужд создавайте отдельные схемы. Относитесь к ним как к модулям и создавайте новую схему для каждого логически обособленного набора сущностей.

                  Почему?
                  • 0
                    С приложения не получилось оформить как цитрату :(
                    • 0
                      С помощью схем в PG можно логически группировать таблицы. Писать запросы не мешает, так как всегда можно указать все схемы в schema_search_path сессии и не указывать при именовании таблиц.

                      Как бонус можно создать таблицы с одинаковым именем в разных схемах для горячих и исторических данных. Тогда можно не меняя запросы подключатся между ними просто указав параметры в сессии.
                      • +3
                        Статья про безопасность. Так и не понял, чем это чревато, использовать public. Не каких лишает возможностей, а чем чревато.
                        • +2
                          Это, скорее, правило хорошего тона. Но теоретически, можно получить проблемы конфликта имен при установке расширений, которые используют схему public для своих функций. Маловероятно, но возможно.
                    • +2
                      А еще перед тем, как запустить код, выдохните, просчитайте до трех и удостоверьтесь, что вы в сессии нужной базы.

                      Ох, вспоминается случай из жизни. Правда не про СУБД, но в тему торопливости. 2 недели без выходных работал в командировке над проектом. Последний день. Около 6 часов вечера. С утра должен быть самолет домой. Сделал все, кроме небольшого сервиса, который никак не хотел работать. Я решил, что самая хорошая идея — переустановка софта. Заметил, что нахожусь не в той ssh сессии только когда полностью удалил все с одной из важнейших боевых машин. Первая мысль — накрылся мой перелет и грядет нарушение визового режима. Но в итоге это была моя самая быстрая установка и настройка за всю жизнь: уже к 9 вечера все было настроено вместе с тем небольшим сервисом.
                      • +2
                        Кстати, а что там нынче хорошего читают про PostgreSQL? Eng/рус, все равно, главное — специфика и тонкости работы. Что док есть, я знаю.
                        • +1

                          возможно, здесь вы найдете что-нибудь интересное (blogs, articles, tutorials)

                          • 0
                            Круто, благодарю!
                        • +6
                          Я еперд DELETE или UPDATE всегда пишу BEGIN.
                          • 0
                            Не распарсил п.22
                            • 0
                              darthunix п.22… именно поэтому к базе данных создают несколько подключений под одним пользователем, в которые заворачивают сотни пользователей с сервера. Не делайте так, одним словом, иначе придется при росте нагрузки все переписывать.

                              Не совсем понятно, что не нужно делать? Создавать на каждого пользователя приложения — пользователя в БД? Или наоборот?
                              А так, советы подходят почти к каждой СУБД, но спасибо за наводку на специфичные для Postgesql инструменты.
                              • 0
                                Я и правда не очень четко выразился. Речь о том, что создавать для каждого пользователя приложения своего пользователя в БД — очень плохая практика. При дальнейшем росте нагрузок (а они растут внезапно и незапланированно) это обернется невозможностью использовать пулы соединений к базе.
                                • 0
                                  Пожалуйста, перепишите пункт 22,
                                  иначе ваша мысль совсем не доходит
                                  (я уж подумал, что только до меня не доходит).
                                  • 0
                                    Поправил, это и правда было написано плохим стилем.
                              • 0
                                п7 совсем неясен. зачем несогласованность в транзакции? кому от этого лучше?
                                deferrable initially deferred только затруднит поиск ошибки.
                                • 0
                                  Вы получите согласованность на конец выполнения транзакции и (опционально) на любой момент внутри ее. Это позволяет упростить жизнь при вставке данных одной транзакцией в разные таблицы, где из-за внешних ключей нужно иначе было бы учитывать порядок вставки. Плюс это иногда удобно при импорте данных через insert.
                                  • 0
                                    это совсем не похоже ни на технику безопасности ни на «хорошую практику»
                                    применимо к приложению это создает исключения только на этапе коммита, вместо генерации исключения на этапе insert/update/delete, что точно плохо сказывается на диагностике неполадки.
                                    К тому же, поддерживать согласованность всегда полезно для выстраивания логики кода. Гораздо сложнее выстрелить себе в ногу при длинных транзакциях
                                  • 0
                                    Например, при обмене между базами (не через репликацию)- загружаешь последовательно данные из пришедшего пакета. А в нем данные идут сначала по одной таблице, потом по другой… Тут не обеспечишь на каждой вставке согласованность — только для всей транзакции.
                                    • +1
                                      Согласен. Их же не зря придумали и есть случаи, когда использование Defferable оправдано и полезно.
                                      К тому же, при наступлении подобного вашему случая, достаточно указать set constraints all deferred; точно на время подобной вставки. Что будет явно означать, что ожидаются неконсистентные данные, которые можно коммитить только после полной загрузки.

                                      Но. Статья претендует на набор граблей/хороших практик и «как надо, а как не надо делать», а в ней написаны достаточно спорные вещи. Отношение к Deferrable тому пример.
                                      Или рекомендация использовать With. Что тоже совсем не полезно для запросов. Ниже об этом уже написали.
                                      Или «Боль! Перед тем, как написать delete или update, напишите where»
                                      гораздо полезнее писать SET AUTOCOMMIT OFF! Всегда!
                                      Потому что только это позволит оценить последствия до их применения. Хотя бы по количеству удаленных записей.

                                      Плохо учить падаванов спорным практикам. Их приходится потом мучительно переучивать.
                                      В остальном пункты здравые, спасибо автору за статью. Я себе занес в список ссылок, который стоит показывать разработчикам :)
                                      • 0
                                        Я внес замечания в статью по поводу with, deferrable и set autocommit off. Спасибо большое за замечания и советы.
                                  • 0
                                    Боль! Перед тем, как написать delete или update, напишите where

                                    Я select пишу.

                                    Никогда нельзя перенаправлять клиента с сервера в базу под собственным, уникальным пользователем БД.

                                    На самом деле зависит от задачи. Если изначально клиентов мало, то журналирование производится гораздо проще. Кто, когда с какого ip и т.д. Это моё сугубо личное ИМХО.
                                    • 0
                                      Если количество клиентов гарантированно останется небольшим (десяток-другой) и увеличиваться никогда не будет, то да. Главное, не ошибиться.
                                    • 0
                                      А можно, если не сложно, на 14й пункт какой-нибудь простой пример.
                                      Что именно за функции имеются в виду?
                                      • 0
                                        Что именно за функции имеются в виду?

                                        предполагаю, что имеются ввиду функции с категориями изменчивости volatile (по умолчанию) и stable. в случае использования категории immutable с постоянными аргументами функция может быть выполнена всего один раз.


                                        подробнее можно почитать здесь.

                                      • +1
                                        Это необходимо не только для восстановления при сбоях, но и дает много интересных возможностей разработчикам для работы в определенных временных срезах базы. В PostgreSQL для этого есть barman.

                                        Если у вас нагруженная БД то хранение WAL для PITR это боль. Кроме того создание инкрементального бекапа при помощи braman это отдельная боль (если у вас БД больше 100 гигов). Большую часть этих проблем я решаю тут pg_arman .

                                        • 0
                                          Кроме того создание инкрементального бекапа при помощи braman это отдельная боль (если у вас БД больше 100 гигов)

                                          А можно поподробнее про подводные камни на 100 ГБ? У меня БД на 60 ГБ, хочу узнать, к чему готовиться через пару лет.
                                          • 0

                                            Для того что бы создать инкремент нужно вычитать ВСЮ бд т.е. прочитать каждую страницу и сверить её LSN.
                                            Чем больше БД тем дольше занимает этот процесс. ptrack умеет отслеживать изменения памяти на лету с почти нулевым оверхедом (1-2%), а pg_arman умеет читать эту информации для того, что бы без каких либо лишних движений делать бекап.

                                        • 0
                                          Используйте «with», с ним код становится чище и его можно читать сверху вниз, а не петлять среди блоков join'ов.

                                          Только оптимизатор Постгреса (в отличие от Оракла) не умеет заглядывать за «with», это для него как отдельный запрос, так что такое разбиение может (и скорее всего будет) работать существенно медленнее исходного запроса.


                                          Если можете написать запрос без временных таблиц — не раздумывайте и напишите! Обычно CTE, создаваемое конструкцией «with», является приемлемой альтернативой. Дело в том, что PostgreSQL для каждой временной таблицы создает временный файл…

                                          А какая база делает иначе? Как СУБД может предугадать сколько данных в таблице будет? Вроде логично всё. Не перепутаны ли тут временные таблицы с временными вьхами?

                                          • 0

                                            Хорошая история про несогласованные данные: https://habrahabr.ru/post/231213/

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