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

CREATE SCHEMA myschema;

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

schema.table

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

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

database.schema.table

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

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

CREATE TABLE myschema.mytable (
 ...
);

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

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

В Раздел 5.14 приведен общий механизм, стоящий за ним.

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

CREATE SCHEMA schema_name AUTHORIZATION user_name;

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

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

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

CREATE TABLE products ( ... );

и

CREATE TABLE public.products ( ... );

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

Возможность создания объектов с одинаковыми именами в разных схемах усложняет написание запроса, который должен ссылаться каждый раз на одни и те же объекты. Это также дает пользователям возможность, намеренно или случайно, изменять поведение запросов других пользователей. Из-за распространенности неопределенных имен в запросах и их использования внутри Tantor BE, если вы добавляете схему в 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 отличается от других схем только тем, что она существует по умолчанию. Ее также можно удалить.

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

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

OPERATOR(schema.operator)

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

SELECT 3 OPERATOR(pg_catalog.+) 4;

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

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

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

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

(Первый “public” - это схема, второй “public” означает “каждый пользователь”. В первом случае это идентификатор, во втором случае — ключевое слово, поэтому различается регистр; обратитесь к рекомендациям из Раздел 4.1.1).

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

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

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

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

В стандарте SQL не предусмотрена возможность обращения в одной схеме к нескольким объектам, принадлежащим разным пользователям. Более того, некоторые реализации не позволяют создавать схемы с именем, отличным от имени их владельца. Фактически, понятия схемы и пользователя эквивалентны в базах данных, которые реализует только основную поддержку схем, указанную в стандарте. Поэтому многие пользователи считают, что полные имена на самом деле образовываются как user_name.table_name. Таким образом, Tantor BE будет работать эффективно, если вы создадите схему для каждого пользователя.

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

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