Схемы
Обзор
Схема — это пространство имен, содержащее именованные объекты базы данных, такие как таблицы, представления, индексы, типы данных, функции, хранимые процедуры и операторы. База данных может включать одну или несколько схем. Схемы относятся к логическому уровню модели данных.
Распределение объектов по разным схемам может решить следующие задачи:
-
Использование несколькими пользователями одной базы данных независимо друг от друга.
-
Объединение объектов базы данных в логические группы для эффективного управления ими.
-
Сосуществование разных приложений в одной базе данных без конфликтов имён.
Схемы не могут быть вложены друг в друга.
Управление схемами
Используйте команду CREATE SCHEMA для создания схемы:
CREATE SCHEMA schema1;Имена схем, начинающиеся с pg_, зарезервированы для системного использования и не могут быть назначены пользователями.
Чтобы создать объект или получить доступ к объектам схемы, укажите полное имя, состоящее из имени схемы и имени таблицы, разделенных точкой:
CREATE TABLE schema1.table1 (
column1 serial PRIMARY KEY,
column2 VARCHAR (50) UNIQUE NOT NULL
);Можно использовать одни и те же имена объектов в разных схемах без конфликтов. Создайте новую схему и добавьте в нее table1:
CREATE SCHEMA schema2;
CREATE TABLE schema2.table1 (
column1 serial PRIMARY KEY,
column2 VARCHAR (50) UNIQUE NOT NULL
);Используйте полные имена для доступа к этим таблицам:
SELECT * FROM schema1.table1;
SELECT * FROM schema2.table1;Выполните команду ALTER SCHEMA, чтобы переименовать схему или изменить ее владельца. Например:
ALTER SCHEMA schema2 RENAME TO myschema;Чтобы удалить схему, вызовите команду DROP SCHEMA. Для этой операции схема должна быть пустой. Если вам нужно удалить схему со всеми содержащимися в ней объектами, используйте ключевое слово CASCADE:
DROP SCHEMA myschema CASCADE;Для получения дополнительной информации об удалении зависимых объектов обратитесь к статье Dependency tracking.
Схема по умолчанию
Если вы создаете таблицу или любой другой объект без указания имени схемы, PostgreSQL помещает этот объект в схему public. Каждая новая база данных содержит эту схему и использует ее по умолчанию. Следующие строки кода эквивалентны:
CREATE TABLE books ( ... );
CREATE TABLE public.books ( ... );Команда ниже возвращает текущую схему:
SELECT current_schema();Результат:
current_schema ---------------- public (1 row)
Чтобы получить список всех доступных схем, используйте метакоманду psql \dn.
Результат:
List of schemas Name | Owner -----------+---------- public | postgres schema1 | postgres
Вы также можете выбрать столбец nspname из каталога pg_namespace:
SELECT nspname FROM pg_namespace;Результат:
nspname -------------------- pg_toast pg_catalog public information_schema
В дополнение к public результат включает системные схемы PostgreSQL. Таблица ниже описывает их.
| Название | Описание |
|---|---|
pg_toast |
Содержит TOAST-таблицы |
Состоит из набора представлений с информацией о текущих объектах базы данных |
|
pg_catalog |
Содержит системные таблицы и все встроенные типы данных, функции и операторы |
Путь поиска схемы
В большинстве случаев мы обращаемся к таблицам по неполным именам (без имен схем) в запросах. Система использует путь поиска (search path), чтобы определить, какая таблица должна быть обработана. Путь поиска представляет собой список схем для поиска. PostgreSQL работает с первой соответствующей таблицей. Если в схемах, указанных в пути поиска, нет совпадений, возникает ошибка, даже если совпадающие имена таблиц существуют в других схемах, не включенных в путь поиска.
Чтобы отобразить текущий путь поиска, используйте следующую команду:
SHOW search_path;Результат:
search_path -------------- "$user", public
Первый элемент "$user" ссылается на схему с тем же именем, что и у текущего пользователя. Если такой схемы не существует, PostgreSQL игнорирует эту запись. Второй элемент — схема public. Первая существующая схема в пути поиска является расположением по умолчанию для новых объектов. Вот почему объекты по умолчанию создаются в схеме public.
Чтобы добавить новую схему в путь поиска, используйте следующую команду:
SET search_path TO schema1, public;Поскольку schema1 является первым элементом пути, новые объекты будут создаваться в ней.
Путь поиска работает таким же образом для типов данных, функций и имен операторов.
Обратите внимание, что упомянутая выше схема pg_catalog является частью пути поиска. Вы можете использовать функцию current_schemas, чтобы проверить это. Эта функция возвращает массив имен схем из фактического пути поиска в порядке их приоритета. Если её аргумент равен true, в результат включаются системные схемы, которые не указываются явно.
SELECT current_schemas(true);Результат:
current_schemas
------------------------------
{pg_catalog,public}
Если схема pg_catalog не указана явно в пути поиска, PostgreSQL выполняет поиск в ней перед проверкой остальных схем пути поиска. Такое поведение гарантирует, что встроенные объекты всегда будут найдены. Однако вы можете явно поместить pg_catalog в конец пути поиска, если хотите, чтобы пользовательские имена объектов переопределяли встроенные.
Права на использование схем
Пользователи не могут получить доступ к объектам в схемах, которыми они не владеют. Чтобы разрешить доступ, владелец схемы должен предоставить привилегию USAGE пользователю на схему. Также может потребоваться предоставить соответствующие привилегии на объекты, чтобы разрешить пользователям операции с объектами. Например, если вы хотите, чтобы пользователи могли создавать объекты, предоставьте им привилегию CREATE на схему.
У всех пользователей есть привилегии CREATE и USAGE на схему public.
Шаблоны использования
Шаблон безопасного использования схем (Secure schema usage pattern) не позволяет ненадежным пользователям влиять на запросы других пользователей. Если база данных не использует этот шаблон, пользователи, которые хотят безопасно выполнять запросы, должны предпринимать защитные меры в начале каждой сессии. Они должны устанавливать путь поиска равным пустой строке или удалять из него схемы, доступные для записи пользователями, не являющимися суперпользователями.
Шаблон безопасного использования схем
Ограничьте обычных пользователей личными схемами и отзовите у них привилегию CREATE на схему public. Для реализации этого подхода выполните следующую команду:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;Создайте для каждого пользователя схему с его именем. Путь поиска по умолчанию начинается с имени $user, вместо которого подставляется имя пользователя. Таким образом, если у всех пользователей будут отдельные схемы, они по умолчанию будут обращаться к собственным схемам. Применяя этот шаблон в базе, к которой уже могли подключаться ненадёжные пользователи, проверьте, не создано ли в схеме public объектов с такими же именами, как у объектов в схеме pg_catalog.
Этот шаблон является шаблоном безопасного использования схем, только если никакой ненадёжный пользователь не является владельцем базы данных и не имеет привилегии CREATEROLE. В противном случае безопасное использование схем невозможно.
Удаление схемы public из пути поиска
Выполните следующую команду, чтобы удалить схему public из пути поиска по умолчанию:
ALTER ROLE ALL SET search_path = "$user";Все пользователи по-прежнему смогут создавать объекты в схеме public, но выбираться эти объекты будут только по полному имени, со схемой. Тогда как обращаться к таблицам по полному имени вполне допустимо, обращения к функциям в схеме public всё же будут небезопасными и ненадёжными. Поэтому если вы создаёте функции или расширения в схеме public, применяйте первый шаблон.
Этот шаблон, как и первый, безопасен при условии, что никакой ненадёжный пользователь не является владельцем базы данных и не имеет привилегии CREATEROLE.