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

Так вийшло, що я почав працювати з PostgreSQL три роки тому і за цей час примудрився методично зібрати всі можливі граблі, які можна уявити. І сказати по правді, якби була можливість поділитися з собою трирічної давності нинішнім гірким досвідом, моє життя було б куди простіше і нервові клітини ціліше. Саме тому я вирішив написати абсолютно суб'єктивну статтю зі зведенням правил, яких дотримуюся при розробці на PostgreSQL. Можливо, комусь ця стаття допоможе обійти зібрані мною граблі (і наступити на інші, ха-ха!).




Той самий список правил
Майже за кожним з пунктів нижче стоїть сумна історія, повна страждань і превознемоганий. А словом «біль!» позначені пункти, вироблені історіями, при згадці про які я все ще здригаюся ночами.

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

    Схема бази даних — це код, який ви написали. Вона повинна лежати в системі контролю версій і версионироваться з іншим проектом. У разі PostgreSQL мені більше всього для цих цілей сподобався Pyrseas. Він перетворює схему з усіма специфічними для PostgreSQL об'єктами в yaml файл, який версионируются. З таким файлом зручно працювати в гілках і зливати зміни, на відміну від чистого SQL. Фінальним кроком yaml файл порівнюється зі схемою бази даних і автоматично генерується міграція на SQL.

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

    Навіть якщо зміна просте, неймовірно термінове і дуже хочеться. Спочатку потрібно застосувати його на базі розробників, закомитить в гілку, зміни застосувати на базі транк (ідентичною бойової базі). І тільки потім, коли все добре в транку, застосовувати на базі бойової. Це довго, параноїдально, але рятує від багатьох проблем.

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

    А ще перед тим, як запустити код, видихніть, прорахуйте до трьох і переконайтеся, що ви сесії потрібної бази. Про trancate я взагалі мовчу, без трьох «Отче наш» навіть не думайте запускати, амінь!

  4. Test Driven Development

    Спочатку завжди пишіть тести, а потім створюйте об'єкти бази даних. Мова йде про будь-які об'єкти: схеми, таблиці, функції, типи, розширення — жодних винятків! Спочатку це здається тяжко, але згодом ви багато разів скажіть собі спасибі. Навіть при первинному створенні схеми легко щось упустити. А при рефакторинге таблиць через півроку тільки написані вами тести вбережуть від раптового пострілу в ногу в який-небудь функції. У разі PostgreSQL є чудове розширення pgTAP. Я рекомендую для кожної схеми створювати додатково схему «имя_схемы_tap», в якій писати функції для тестування. А потім просто проганяти тести через pg_prove.

  5. Не забудьте налаштувати PITR

    Я боюся виступити в ролі Капітана Очевидності, але у будь бази повинен бути налаштований бекап. При тому бажано такий, щоб мати можливість відновлювати базу на будь-який момент часу. Це необхідно не тільки для відновлення при збоях, але і дає багато цікавих можливостей розробникам для роботи в певних часових зрізах бази. В PostgreSQL для цього є barman.

  6. Узгодженість даних

    Неузгоджені дані в базі ніколи не приводили ні до чого хорошого. Навіть невелика їх кількість може легко перетворити всю базу в сміття. Тому ніколи не варто нехтувати нормалізацією і обмеженнями типу зовнішніх ключів і перевірок. Використовуйте денормализованную форму (наприклад, jsonb) тільки впевнившись, що не виходить реалізувати схему в нормалізованому вигляді з прийнятним рівнем складності і продуктивності — денормализованный вид потенційно може призвести до неузгодженим даними. На всі доводи прихильників денормалізації відповідайте, що нормалізацію придумали не просто так і мовчіть з багатозначним виглядом.

  7. Створюйте зовнішні ключі deferrable initially deferred

    В такому випадку ви відкладаєте перевірку обмеження на кінець транзакції, що дозволяє безкарно отримувати неузгодженість в ході її виконання (але в кінці все узгоджується або викличе помилку). Більш того, змінюючи прапор всередині транзакції на immediate, можна примусово зробити перевірку обмеження в потрібний момент транзакції.

  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», про яку часто забувають — і в цей момент у світі сумує кошеня.

  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. Біль! Вишенька на торті

    Ніколи не можна перенаправляти клієнта з сервера в базу під власним, унікальним користувачем БД. Навіть якщо вам здається, що:

    • можна налаштувати на рівні PostreSQL безпеку для груп штатними засобами
    • користувач зможе використовувати тільки належні його групі об'єкти
    • внутрішній голос наполягає
    … ніколи не робіть так, це пастка! При такій схемі можна використовувати пули сполук, і кожен користувач буде від'їдати ресурсномістке з'єднання до бази даних. Бази даних тримають сотні сполук, а сервера — тисячі. І саме тому до бази даних створюють декілька підключень під одним користувачем, в які загортають сотні користувачів з сервера. Не робіть так, одним словом, інакше доведеться при зростанні навантаження все переписувати.
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.