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

CREATE SCHEMA myschema;

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

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

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

Есть ещё более общий синтаксис

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

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

Таким образом, создать таблицу в новой схеме можно так:

CREATE TABLE myschema.mytable (
 ...
);

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

DROP SCHEMA myschema;

Удалить схему со всеми содержащимися в ней объектами можно так:

DROP SCHEMA myschema CASCADE;

Стоящий за этим общий механизм описан в Разделе 5.15.

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

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

Вы даже можете опустить имя схемы, в этом случае именем схемы станет имя пользователя. Как это можно применять, описано в Подразделе 5.10.7.

Схемы с именами, начинающимися с pg_, являются системными; пользователям не разрешено использовать такие имена.

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

CREATE TABLE products ( ... );

эквивалентна:

CREATE TABLE public.products ( ... );

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

Администратор безопасности — это единственный пользователь, не являющийся суперпользователем, который может выдавать (GRANT) или отзывать (REVOKE) права доступа к защищённой схеме и её объектам, а именно pg_class, pg_proc, pg_type и pg_collation. Ни владелец схемы, ни владелец объекта не могут выдавать или отзывать эти права.

Postgres Pro также позволяет создать защищённую схему на основе существующей схемы, задав для неё администратора безопасности. Чтобы задать администратора безопасности, воспользуйтесь командой ALTER SCHEMA.

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

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

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

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

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

Такая запись необходима для избежания синтаксической неоднозначности. Пример такого выражения:

SELECT 3 OPERATOR(pg_catalog.+) 4;

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

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

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

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

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

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

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

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

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

Стандарт SQL не поддерживает обращение в одной схеме к разным объектам, принадлежащим разным пользователям. Более того, в ряде реализаций СУБД нельзя создавать схемы с именем, отличным от имени владельца. На практике, в СУБД, реализующих только базовую поддержку схем согласно стандарту, концепции пользователя и схемы очень близки. Таким образом, многие пользователи полагают, что полное имя на самом деле образуется как имя_пользователя.имя_таблицы. И именно так будет вести себя Postgres Pro, если вы создадите схемы для каждого пользователя.

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

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