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

CREATE SCHEMA myschema;

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

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

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

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

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

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

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

CREATE TABLE myschema.mytable (
 ...
);

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

DROP SCHEMA myschema;

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

DROP SCHEMA myschema CASCADE;

См. Раздел 2.2.15 описание общего механизма, лежащего в основе этого процесса.

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

CREATE SCHEMA schema_name AUTHORIZATION user_name;

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

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

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

CREATE TABLE products ( ... );

и:

CREATE TABLE public.products ( ... );

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

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

См. также Раздел 2.6.27 для ознакомления с другими способами управления путем поиска схем.

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

OPERATOR(схема.operator)

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

SELECT 3 OPERATOR(pg_catalog.+) 4;

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

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

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

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

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

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

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

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

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

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

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

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