Написать в техподдержку Позвонить нам
Админпанель Выход

Содержание статьи:

    Расширения для PostgreSQL - Postgis и PGcrypto

    Дополнения БД могут существенно расширить функциональность базы как сервиса - добавить мониторинг, географические объекты, криптографию - это например, и многое другое тоже.

    Важно

    Для каждого типа базы данных доступен разный набор расширений. Данная статья описывает только частный случай установки и работы с расширениями Postgis и PGcrypto на базе данных PostgreSQL.

    Все операции по установке расширений проводятся в Личном кабинете MCS. Необходимо перейти в раздел "Базы данных"- "Инстансы баз данных".

    Далее нужно выбрать базу данных, в которую необходимо установить дополнения (расширения):

    В открывшемся окне надо выбрать вкладку "Расширения" и нажать на кнопку "Добавить".

    В появившемся окне нужно выбрать необходимые расширения и нажать на кнопку "Добавить":

    После этого начнётся установка расширений и они появятся в списке установленных.

    Расширение для поддержки географических объектов - postgis

    PostGIS - расширение объектно-реляционной СУБД PostgreSQL предназначенное для хранения в базе географических данных. PostGIS включает поддержку пространственных индексов R-Tree/GiST и функции обработки геоданных.

    После добавления этого расширения в базу можно работать с ней специализированными запросами. Полный перечень запросов и другую полезную информацию можно прочитать на официальном ресурсе - https://gis-lab.info/docs/postgis/manual/postgis-manual-ru-1.3.4.pdf.

    Ниже даны ответы на самые популярные вопросы.

    Какие виды геометрических объектов имеются в моем распоряжении?

    В вашем распоряжении point, line, polygon, multipoint, multiline, multipolygon, and geometrycollections (точка, линия, полигон, мультиточка, мультилиния, мультиполигон и геометрическая коллекция). Они определены в формате Well Known Text Open GIS (с расширениями XYZ, XYM, XYZM).

    Как построить пространственный запрос?

    Сначала вы должны создать таблицу со столбцом типа "geometry", который будет содержать ваши ГИС- данные. Соединитесь с вашей базой данных с помощью psql и выполните SQL:

                 CREATE TABLE gtest ( ID int4, NAME varchar(20) ); SELECT AddGeometryColumn('', 'gtest','geom',-1,'LINESTRING',2);

    Если не получилось добавить столбец геометрии, то, вероятно, вы не загрузили функции и объекты PostGIS в свою базу данных. Смотрите инструкцию по установке.

    Далее, вы можете вставлять геометрию в таблицу с помощью SQL-команды insert. Объект ГИС будет форматирован согласно формату "well-known text" Консорциума OpenGIS:

    INSERT INTO gtest (ID, NAME, GEOM) VALUES ( 1, 'Первая геометрия', GeomFromText('LINESTRING(2 3,4 5,6 5,7 8)', -1) );

    Подробную информацию о других объектах ГИС можно посмотреть в справочнике объектов. Просмотр ваших ГИС-данных в таблице:

                 SELECT id, name, AsText(geom) AS geom FROM gtest;

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

    id | name | geom ---+------------------+-----------------------------        1 | Первая геометрия | LINESTRING(2 3,4 5,6 5,7 8)       (1 row)

    Как вставить объект ГИС в базу данных?

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

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

    Чаще всего вам будет нужен "оператор пересечения" (&&), который проверяет пересекаются ли границы объектов. Польза оператора && заключается в том, что он может использовать пространственный индекс, если он существует. Это ускорит выполнения запроса.

    Кроме того, вы можете использовать пространственные функции, такие как Distance(), ST_Intersects(), ST_Contains() and ST_Within() и другие для сужения результатов поиска. Большинство пространственных запросов включают тест на индекс и тест на пространственную функцию. Тест индекса полезен тем, что ограничивает число проверок значений теми, которые могут попасть в требуемый набор. Далее используются пространственные функции используются для окончательной проверки условия.

                 SELECT id, the_geom FROM thetable              WHERE              the_geom && 'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))'              AND              _ST_Contains(the_geom,'POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))');

    Расширение для криптографических функций - pgcrypto

    Модуль pgcrypto предоставляет криптографические функции для PostgreSQL.

    После добавления этого расширения в базу можно работать с ней специализированными запросами. Ниже приведены некоторые из них, для примера. Полный перечень запросов и другую полезную информацию можно прочитать на официальном ресурсе - https://postgrespro.ru/docs/postgresql/11/pgcrypto.

    Ограничения безопасности

    Все функции pgcrypto выполняются внутри сервера баз данных. Это означает, что все данные и пароли передаются между функциями pgcrypto и клиентскими приложениями открытым текстом. Поэтому вы должны:

    • Подключаться локально или использовать подключения SSL.
    • Доверять и системе, и администратору баз данных.
    • Если это невозможно, лучше произвести шифрование в клиентском приложении.

    Стандартные функции хеширования

    digest()

    digest(data text, type text) returns bytea digest(data bytea, type text) returns bytea

    Вычисляет двоичный хеш данных (data). Параметр type выбирает используемый алгоритм. Поддерживаются стандартные алгоритмы: md5, sha1, sha224, sha256, sha384 и sha512. Если модуль pgcrypto собирался с OpenSSL, становятся доступны и другие алгоритмы, как описано в Таблице F.19.

    Если вы хотите получить дайджест в виде шестнадцатеричной строки, примените encode() к результату. Например:

    CREATE OR REPLACE FUNCTION sha1(bytea) returns text AS $$     SELECT encode(digest($1, 'sha1'), 'hex') $$ LANGUAGE SQL STRICT IMMUTABLE; F.25.1.2. hmac() hmac(data text, key text, type text) returns bytea hmac(data bytea, key bytea, type text) returns bytea

    Вычисляет имитовставку на основе хеша для данных data с ключом key. Параметр type имеет то же значение, что и для digest().

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

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

    Функции хеширования пароля

    Функции crypt() и gen_salt() разработаны специально для хеширования паролей. Функция crypt() выполняет хеширование, а gen_salt() подготавливает параметры алгоритма для неё.

    Алгоритмы в crypt() отличаются от обычных алгоритмов хеширования MD5 и SHA1 в следующих аспектах:

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

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

    В таблице ниже перечислены алгоритмы, поддерживаемые функцией crypt().

    Алгоритм Макс. длина пароля Адаптивный? Размер соли (бит) Размер результата Описание
    bf 72 да 128 60 На базе Blowfish, вариация 2a
    md5 без ограничений нет 48 34 crypt на базе MD5
    xdes 8 да 24 20 Расширенный DES
    des 8 нет 12 13 Изначальный crypt из UNIX

    crypt()

    crypt(password text, salt text) returns text

    Вычисляет хеш пароля (password) в стиле crypt(3). Для сохранения нового пароля необходимо вызвать gen_salt(), чтобы сгенерировать новое значение соли (salt). Для проверки пароля нужно передать сохранённое значение хеша в параметре salt и проверить, соответствует ли результат сохранённому значению.

    Пример установки нового пароля:

    UPDATE ... SET pswhash =" crypt('new "password', gen_salt('md5'));

    Пример проверки пароля:

    SELECT (pswhash =" crypt('entered "password', pswhash)) AS pswmatch FROM ... ;

    Этот запрос возвращает true, если введённый пароль правильный.

    gen_salt()

    gen_salt(type text [, iter_count integer ]) returns text

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

    Параметр type задаёт алгоритм хеширования. Принимаются следующие варианты: des, xdes, md5 и bf.

    Параметр iter_count позволяет пользователю указать счётчик итераций для алгоритма, который его принимает. Чем больше это число, тем больше времени уйдёт на вычисление хеша пароля, а значит, тем больше времени понадобится, чтобы взломать его. Хотя со слишком большим значением время вычисления хеша может вырасти до нескольких лет — это вряд ли практично. Когда параметр iter_count опускается, применяется количество итераций по умолчанию. Множество допустимых значений для iter_count зависит от алгоритма.

    Полезна ли была эта статья?