Схемы

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

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

База данных содержит одну или несколько именованных схем, которые, в свою очередь, содержат таблицы. Схемы содержат и другие виды именованных объектов, включая типы данных, функции и операторы. Одно и то же имя объекта можно без конфликтов использовать в разных схемах; например, и schema1 и myschema могут содержать таблицы с именем mytable. В отличие от баз данных, схемы разделены не жестко: пользователь может обращаться к объектам в любой схеме в базе данных, к которой он подключен, если у него есть для этого права.

Существует несколько причин, по которым стоит использовать схемы:

  • Чтобы позволить многим пользователям использовать одну базу данных, не мешая друг другу.

  • Чтобы организовать объекты базы данных в логические группы для лучшей управляемости.

  • Сторонние приложения можно поместить в отдельные схемы, чтобы они не конфликтовали с именами других объектов.

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

Создание схемы

Чтобы создать схему, используйте команду CREATE SCHEMA. Дайте схеме имя по вашему выбору. Например:

CREATE SCHEMA myschema;

Чтобы создать или получить доступ к объектам в схеме, напишите полное имя, состоящее из имени схемы и имени таблицы, разделенных точкой:

схема.таблица

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

На самом деле можно использовать даже более общий синтаксис:

база_данных.схема.таблица

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

Итак, чтобы создать таблицу в новой схеме, используйте:

CREATE TABLE myschema.mytable (
 ...
);

Чтобы удалить схему, если она пуста (все объекты в ней удалены), используйте:

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

Описание стоящего за этим общего механизма см. в разделе Отслеживание зависимостей.

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

CREATE SCHEMA имя_схемы AUTHORIZATION имя_пользователя;

Можно даже опустить имя схемы, и в этом случае оно будет совпадать с именем пользователя. Возможности применения этого см. в подразделе Шаблоны использования.

Имена схем, начинающиеся с pg_, зарезервированы для системных целей и не могут быть созданы пользователями.

Схема Public

В предыдущих разделах мы создавали таблицы без указания имен схем. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему с именем «public». Каждая новая база данных содержит такую схему. Таким образом, команда

CREATE TABLE products (...);

равнозначна команде

CREATE TABLE public.products (...);

Путь поиска схемы

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

Возможность создавать объекты с одинаковыми именами в разных схемах усложняет написание запроса, который каждый раз ссылается на одни и те же объекты. Это также открывает возможность одним пользователям злонамеренно или случайно изменять поведение запросов других пользователей. Из-за преобладания неполных имен в запросах и их использования во внутренних структурах QHB, добавление схемы в search_path по сути доверяет всем пользователям, имеющим право CREATE для этой схемы. Когда вы запускаете обычный запрос, злонамеренный пользователь, способный создавать объекты в схеме вашего пути поиска, может взять на себя управление и выполнять произвольные функции SQL, как если бы их выполняли вы.

Первая схема, названная в пути поиска, называется текущей схемой. Это не только первая искомая схема — в ней будут создаваться новые таблицы, если в команде CREATE TABLE не задано имя схемы.

Чтобы показать текущий путь поиска, используйте следующую команду:

SHOW search_path;

При настройках по умолчанию это возвращает:

search_path
--------------
"$user", public

Первый элемент указывает, что следует искать схему с именем текущего пользователя. Если такой схемы не существует, эта запись игнорируется. Второй элемент ссылается на схему public, которую мы уже видели.

Первая существующая схема в пути поиска является местоположением по умолчанию для создания новых объектов. По этой причине по умолчанию объекты создаются в схеме public. Когда на объекты ссылаются в любом другом контексте без указания схемы (команды изменения таблицы, изменения данных или запросов), путь поиска просматривается до тех пор, пока не будет найден соответствующий объект. Следовательно, в конфигурации по умолчанию любое обращение с неполным именем, опять же, может ссылаться только на схему public.

Чтобы поместить нашу новую схему в путь, необходимо использовать:

SET search_path TO myschema,public;

(Мы опускаем здесь $user, потому что прямо сейчас оно не нужно.) И тогда можно обратиться к таблице без указания схемы:

DROP TABLE mytable;

Кроме того, поскольку myschema является первым элементом в пути, новые объекты будут по умолчанию создаваться именно в этой схеме.

Также можно написать:

SET search_path TO myschema;

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

Другие способы управления путем поиска схемы см. также в разделе Системные информационные функции и операторы.

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

OPERATOR(схема.оператор)

Это необходимо, чтобы избежать синтаксической неоднозначности. Пример:

SELECT 3 OPERATOR(pg_catalog.+) 4;

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

Схемы и права

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

Пользователю также может быть разрешено создавать объекты в чужой схеме. Для этого необходимо предоставить право CREATE для этой схемы. В базах данных, обновленных с QHB 1.5.1 или более ранних версий , это право есть у всех в схеме public. Некоторые шаблоны использования требуют отзыва этого права:

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(Первое слово «public» означает схему, второе — «каждый пользователь». В первом случае это идентификатор, во втором — ключевое слово, отсюда и другой регистр; см. разъяснения в подразделе Идентификаторы и ключевые слова.)

Схема системных каталогов

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

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

Шаблоны использования

Схемы можно применять для организации данных различными способами. Безопасный шаблон использования схемы не позволяет ненадежным пользователям изменять поведение запросов других пользователей. Когда база данных не применяет безопасный шаблон использования схемы, пользователям, желающим безопасно запросить объекты этой базы данных, придется в начале каждого сеанса предпринимать защитные меры. В частности, они будут начинать каждый сеанс, устанавливая в search_path пустую строку или иным образом удаляя оттуда схемы, доступные для записи обычным пользователям. Существует несколько шаблонов использования, с легкостью поддерживаемых конфигурацией по умолчанию:

  • Ограничить обычных пользователей частными схемами. Для реализации этого шаблона сначала удостоверьтесь, что ни у одной схемы нет общедоступных прав CREATE. Затем для каждого пользователя, которому нужно будет создавать не временные объекты, создайте схему с его же именем, например, CREATE SCHEMA alice AUTHORIZATION alice. (Напомним, что путь поиска по умолчанию начинается с имени $user, которое преобразуется в имя пользователя. Поэтому если у каждого пользователя есть отдельная схема, они по умолчанию получают доступ к своим схемам.) Этот шаблон является безопасным шаблоном использования схемы, если только недоверенный пользователь не является владельцем базы данных или не получил право ADMIN OPTION для соответствующей роли. В этом случае безопасного шаблона использования схемы не существует.
    В QHB версии 1.5.2 и выше стандартная конфигурация поддерживает этот шаблон. В предыдущих версиях или при использовании базы данных, обновленной с предыдущей версии, понадобится отозвать общедоступное право CREATE из схемы public (выполните REVOKE CREATE ON SCHEMA public FROM PUBLIC). Затем проверьте схему public на наличие объектов с такими же именами, что и у объектов в схеме pg_catalog.

  • Удалить схему public из пути поиска по умолчанию, изменив qhb.conf или выполнив команду ALTER ROLE ALL SET search_path = "$user". Затем нужно предоставить права на создание объектов в схеме public. Выбираться объекты в этой схеме будут только по полному имени. Тогда как обращения к таблицам по полному имени вполне допустимы, вызовы функций в схеме public будут небезопасными или ненадежными. Если вы создаете в схеме public функции или расширения, используйте вместо этого первый шаблон. Иначе, как и в первом шаблоне, это безопасно за исключением случаев, когда недоверенный пользователь является владельцем базы данных или получил право ADMIN OPTION для соответствующей роли.

  • Сохранить путь поиска по умолчанию и предоставить права на создание объектов в схеме public. Все пользователи неявно имеют доступ к схеме public. Это моделирует ситуацию, когда схемы вообще недоступны, обеспечивая плавный переход из среды без схем. Однако это не безопасный шаблон. Он допустим только в том случае, если в базе данных имеется всего один или несколько доверяющих друг другу пользователей. В базах данных, обновленных с версии QHB 1.5.1 или более ранней, это шаблон по умолчанию.

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

Возможность переноса

В стандарте SQL не существует концепции, что объекты в одной схеме могут принадлежать разным пользователям. Более того, некоторые реализации СУБД не позволяют создавать схемы, имена которых отличаются от имен их владельцев. Фактически в СУБД, реализующей только базовую поддержку схемы, указанную в стандарте, понятия схемы и пользователя почти равнозначны. Поэтому многие пользователи считают, что в действительности полные имена записываются как имя_пользователя.имя_таблицы. По сути именно так будет вести себя QHB, если вы создадите схемы для каждого пользователя.

Кроме того, в стандарте SQL отсутствует понятие схемы public. Для максимального соответствия стандарту использовать схему public не следует.

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

Copyright © KVANTOM LLC, 2019-2025