PostgreSQL — Schemas
Кластер баз данных PostgreSQL содержит одну или несколько именованных баз данных. Роли и некоторые другие типы объектов являются общими для всего кластера. Клиентское соединение с сервером может получить доступ только к данным в одной базе данных, указанной в запросе на соединение.
База данных содержит одну или несколько именованных схем , которые, в свою очередь, содержат таблицы. Схемы также содержат другие типы именованных объектов, включая типы данных, функции и операторы. Одно и то же имя объекта может использоваться в разных схемах без конфликтов; например, оба schema1 и myschema могут содержать таблицы с именами mytable . В отличие от баз данных, схемы не разделены жестко: пользователь может получить доступ к объектам в любой из схем в базе данных, к которой он подключен, если у него есть соответствующие привилегии.
Есть несколько причин, по которым можно использовать схемы:
- Чтобы многие пользователи могли использовать одну базу данных, не мешая друг другу.
- Чтобы организовать объекты базы данных в логические группы, чтобы сделать их более управляемыми.
- Сторонние приложения можно поместить в отдельные схемы, чтобы они не конфликтовали с именами других объектов.
Схемы аналогичны каталогам на уровне операционной системы, за исключением того, что схемы не могут быть вложенными.
Создание схемы
Чтобы создать схему, используйте команду CREATE SCHEMA . Дайте схеме имя по вашему выбору. Например:
Чтобы создать или получить доступ к объектам в схеме, напишите полное имя , состоящее из имени схемы и имени таблицы, разделенных точкой:
Это работает везде, где ожидается имя таблицы, включая команды изменения таблицы и команды доступа к данным, обсуждаемые в следующих главах. (Для краткости мы будем говорить только о таблицах, но те же идеи применимы и к другим видам именованных объектов, таким как типы и функции.)
На самом деле, даже более общий синтаксис:
тоже можно использовать, но в настоящее время это только для формального соответствия стандарту SQL. Если вы пишете имя базы данных, оно должно совпадать с именем базы данных, к которой вы подключены.
Итак, чтобы создать таблицу в новой схеме, используйте:
Чтобы удалить схему, если она пуста (все объекты в ней удалены), используйте:
Чтобы удалить схему, включающую все содержащиеся объекты, используйте:
Часто вам потребуется создать схему, принадлежащую кому-то другому (поскольку это один из способов ограничить действия ваших пользователей четко определенными пространствами имен). Синтаксис для этого:
Вы даже можете опустить имя схемы, и в этом случае имя схемы будет таким же, как имя пользователя.
Имена схем, начинающиеся с pg_ , зарезервированы для системных целей и не могут создаваться пользователями.
Публичная схема
В предыдущих разделах мы создавали таблицы без указания имен схем. По умолчанию такие таблицы (и другие объекты) автоматически помещаются в схему с именем « public » . Каждая новая база данных содержит такую схему. Таким образом, следующие условия эквивалентны:
Путь поиска схемы
Уточненные имена утомительны для написания, и зачастую лучше вообще не связывать конкретное имя схемы с приложениями. Поэтому к таблицам часто обращаются по неполным именам , состоящим только из имени таблицы. Система определяет, какая таблица имеется в виду, следуя пути поиска , который представляет собой список схем для поиска. Первая совпадающая таблица в пути поиска считается искомой. Если в пути поиска нет соответствия, сообщается об ошибке, даже если совпадающие имена таблиц существуют в других схемах базы данных.
Возможность создавать объекты с одинаковыми именами в разных схемах усложняет написание запроса, который каждый раз ссылается на одни и те же объекты. Это также открывает для пользователей возможность изменить поведение запросов других пользователей, злонамеренно или случайно. Из-за преобладания неполных имен в запросах и их использования во внутренних компонентах PostgreSQL добавление схемы для search_path эффективного доверия всем пользователям, имеющим CREATE привилегии в этой схеме. Когда вы запускаете обычный запрос, злоумышленник, способный создавать объекты в схеме вашего пути поиска, может взять на себя управление и выполнять произвольные функции SQL, как если бы вы их выполняли.
Первая схема, названная в пути поиска, называется текущей схемой. Помимо того, что это первая искомая схема, это также схема, в которой будут созданы новые таблицы, если в CREATE TABLE команде не указано имя схемы.
Чтобы показать текущий путь поиска, используйте следующую команду:
Первый элемент указывает, что следует искать схему с тем же именем, что и у текущего пользователя. Если такой схемы не существует, запись игнорируется. Второй элемент относится к общедоступной схеме, которую мы уже видели.
Первая существующая схема в пути поиска является расположением по умолчанию для создания новых объектов. По этой причине объекты по умолчанию создаются в общедоступной схеме. Когда на объекты ссылаются в любом другом контексте без квалификации схемы (модификация таблицы, модификация данных или команды запроса), путь поиска проходится до тех пор, пока не будет найден соответствующий объект. Следовательно, в конфигурации по умолчанию любой неквалифицированный доступ снова может ссылаться только на общедоступную схему.
Чтобы поместить нашу новую схему в путь, мы используем:
(Мы опускаем $user здесь, потому что в этом нет непосредственной необходимости.) И тогда мы можем получить доступ к таблице без уточнения схемы:
Кроме того, поскольку myschema это первый элемент пути, в нем по умолчанию будут создаваться новые объекты.
Мы могли бы также написать:
Тогда у нас больше не будет доступа к общедоступной схеме без явного уточнения. В общедоступной схеме нет ничего особенного, за исключением того, что она существует по умолчанию. Его тоже можно сбросить.
Путь поиска работает так же для имен типов данных, имен функций и имен операторов, как и для имен таблиц. Имена типов данных и функций могут быть определены точно так же, как имена таблиц. Если вам нужно написать полное имя оператора в выражении, есть специальное условие: вы должны написать
Это необходимо, чтобы избежать синтаксической двусмысленности. Пример:
На практике обычно полагаются на путь поиска операторов, чтобы не писать ничего столь уродливого.
Схемы и привилегии
По умолчанию пользователи не могут получить доступ ни к каким объектам в схемах, которыми они не владеют. Чтобы разрешить это, владелец схемы должен предоставить USAGE привилегию на схему. Чтобы разрешить пользователям использовать объекты в схеме, может потребоваться предоставить дополнительные привилегии, соответствующие объекту.
Пользователю также может быть разрешено создавать объекты в чужой схеме. Чтобы разрешить это, необходимо предоставить CREATE привилегию на схему. Обратите внимание, что по умолчанию у всех есть права на схему CREATE и . Это позволяет всем пользователям, которые могут подключаться к данной базе данных, создавать объекты в ее схеме. Некоторые шаблоны использования требуют отзыва этой привилегии: USAGEpublicpublic
(Первый « общедоступный » — это схема, второй « общедоступный » означает « каждый пользователь » .
Схема системного каталога
Помимо public схем, созданных пользователем, каждая база данных содержит pg_catalog схему, которая содержит системные таблицы и все встроенные типы данных, функции и операторы. pg_catalog всегда эффективно является частью пути поиска. Если он не указан явно в пути, то он неявно ищется перед поиском в схемах пути. Это гарантирует, что встроенные имена всегда будут доступны для поиска. Однако вы можете явно указать pg_catalog в конце пути поиска, если предпочитаете, чтобы определяемые пользователем имена переопределяли встроенные имена.
Поскольку имена системных таблиц начинаются с pg_ , лучше избегать таких имен, чтобы избежать конфликта, если какая-то будущая версия определит системную таблицу с тем же именем, что и ваша таблица. (При использовании пути поиска по умолчанию неполная ссылка на имя вашей таблицы будет затем разрешаться как системная таблица.) Системные таблицы будут по-прежнему следовать соглашению о том, что имена начинаются с pg_ , чтобы они не конфликтовали с неполной пользовательской таблицей . имена, пока пользователи избегают pg_ префикса.
Шаблоны использования
Схемы можно использовать для организации ваших данных разными способами. Шаблон безопасного использования схемы не позволяет ненадежным пользователям изменять поведение запросов других пользователей. Если база данных не использует безопасный шаблон использования схемы, пользователи, желающие безопасно запрашивать эту базу данных, будут предпринимать защитные действия в начале каждого сеанса. В частности, они будут начинать каждый сеанс, устанавливая search_path пустую строку или иным образом удаляя схемы, не доступные для записи суперпользователем, из файлов search_path . Есть несколько шаблонов использования, которые легко поддерживаются конфигурацией по умолчанию:
- Ограничьте обычных пользователей схемами, приватными для пользователей. Чтобы реализовать это, введите REVOKE CREATE ON SCHEMA public FROM PUBLIC и создайте схему для каждого пользователя с тем же именем, что и у этого пользователя. Напомним, что путь поиска по умолчанию начинается с $user , который разрешается в имя пользователя. Поэтому, если у каждого пользователя есть отдельная схема, по умолчанию они получают доступ к своим собственным схемам. После применения этого шаблона в базе данных, в которую уже вошли ненадежные пользователи, рассмотрите возможность аудита общедоступной схемы для объектов с именами, подобными объектам в схеме pg_catalog . Этот шаблон является безопасным шаблоном использования схемы, если только ненадежный пользователь не является владельцем базы данных или не имеет CREATEROLE привилегии, и в этом случае не существует безопасного шаблона использования схемы.
- Удалите общедоступную схему из пути поиска по умолчанию, изменив postgresql.confили создав ALTER ROLE ALL SET search_path = «$user» . Все сохраняют возможность создавать объекты в общедоступной схеме, но выбирать эти объекты будут только полные имена. Хотя квалифицированные ссылки на таблицы допустимы, вызовы функций в общедоступной схеме будут небезопасными или ненадежными . Если вы создаете функции или расширения в общедоступной схеме, вместо этого используйте первый шаблон. В противном случае, как и в первом шаблоне, это безопасно, если ненадежный пользователь не является владельцем базы данных или не имеет CREATEROLE привилегии.
- Оставьте значение по умолчанию. Все пользователи неявно обращаются к общедоступной схеме. Это моделирует ситуацию, когда схемы вообще недоступны, обеспечивая плавный переход из мира, не поддерживающего схемы. Однако это никогда не является безопасным шаблоном. Это приемлемо только тогда, когда в базе данных есть один пользователь или несколько взаимно доверяющих пользователей.
Для любого шаблона, чтобы установить общие приложения (таблицы для всех, дополнительные функции, предоставляемые третьими лицами и т. д.), поместите их в отдельные схемы. Не забудьте предоставить соответствующие привилегии, чтобы другие пользователи могли получить к ним доступ. Затем пользователи могут обращаться к этим дополнительным объектам, уточняя имена с помощью имени схемы, или они могут по своему выбору поместить дополнительные схемы в свой путь поиска.
Портативность
В стандарте SQL не существует понятия объектов одной и той же схемы, принадлежащих разным пользователям. Более того, некоторые реализации не позволяют создавать схемы с именем, отличным от имени их владельца. На самом деле концепции схемы и пользователя почти эквивалентны в системе баз данных, которая реализует только базовую поддержку схемы, указанную в стандарте. Поэтому многие пользователи считают, что полные имена на самом деле состоят из . Вот как будет эффективно вести себя PostgreSQL , если вы создадите схему для каждого пользователя. user_name.table_name
public Кроме того, в стандарте SQL отсутствует понятие схемы. Для максимального соответствия стандарту не следует использовать public схему.
Конечно, некоторые системы баз данных SQL могут вообще не реализовывать схемы или предоставлять поддержку пространств имен, разрешая (возможно, ограниченный) доступ между базами данных. Если вам нужно работать с этими системами, то максимальной переносимости можно добиться, вообще не используя схемы.
Create Schema, Database
Схема Schema с точки зрения базы данных представляет собой контейнер объектов типа таблиц, триггеров, хранимых процедур и т.п. В данной статье будут рассмотрены вопросы создания и удаления схемы БД следующих СУБД :
- Oracle : Schema привязывается к пользователю, т.е. наименование схемы, как правило, является учетной записью пользователя. Схема создается при создании пользователем первого объекта, и все последующие объекты созданные этим пользователем становятся частью этой схемы. Кроме этого Oracle позволяет создавать схему как контейнер одновременно с объектами базы данных.
- MSSQL : в Microsoft SQL Server начиная с версии 2005 жесткая связь между пользователями и схемами была отменена. Пользователи могут получить доступ на выполнение определенных операций с объектами схемы : чтение, запись, обновление или выполнение.
- PostgreSQL : Schema создается внутри объекта базы данных. Сервер может управлять несколькими базами данных, каждая из которых может включать несколько схем. То есть, как и в MSSQL, схема не связана с учетной записью пользователя.
- MySQL : понятие Schema имеет тождественный смысл с Database. База данных Database является контейнером объектов, к которым пользователь получает доступ.
- Derby : Schema не имеет жесткой связи с пользователем и является контейнером объектов, для доступа к которым пользователь должен иметь соответствующие привилегии.
Создание схемы, CREATE SCHEMA
Для создания схемы необходимо использовать SQL скрипт CREATE SCHEMA. Разные схемы могут включать одноименные объекты. При обращении к объектам разных схем необходимо указывать наименование схемы как префикс. Для создания схемы пользователь должен иметь соответствующие привилегии. Конечно же, superuser’ы данной привилегией владеют.
Создание схемы Oracle
Oracle относится к тем платформам СУБД, которые не имеют явной поддержки команды CREATE SCHEMA. Однако он все же неявно создаёт схему, когда пользователь создаёт свой первый объект базы данных. Данная СУБД использует команду "CREATE SCHEMA" для создания за одну транзакцию таблиц и представлений вместе с предоставлением доступа к ним.
Необходимо отметить, что Oracle разрешает дополнительно использовать в инструкции CREATE SCHEMA стандартные скрипты CREATE TABLE, CREATE VIEW и GRANT. Нельзя использовать любые расширения этих команд, имеющиеся в Oracle, если эти команды включены в инструкцию CREATE SCHEMA. Синтаксис создания объектов со схемой.
В следующем примере для схемы "painter"" создаются таблица и представление. Коме этого в инструкции CREATE SCHEMA определен доступ к объектам.
Порядок команд создания объектов и предоставления прав доступа в инструкции CREATE SCHEMA не критичен, но все же следует соблюдать синтаксис. Oracle выполняет инструкцию CREATE SCHEMA только в том случае, если все входящие в нее инструкции CREATE и GRANT были выполнены успешно.
Создание схемы MS SQL
В СУБД MS SQL при помощи транзакции CREATE SCHEMA можно создать схему одновременно с созданием в ней таблиц, представлений и предоставить или запретить доступ на эти объекты с использованием операторов GRANT, DENY или REVOKE.
- schema_name
Идентификационного наименование схемы. - AUTHORIZATION owner_name
Учетная запись пользователя базы данных, который является владельцем схемы. - table_definition
SQL скрипт создания таблицы внутри схемы. Пользователь должен иметь права CREATE TABLE в текущей базе данных. - view_definition
SQL скрипт создания представления внутри схемы. Пользователь должен иметь права CREATE VIEW в текущей базе данных. - grant_statement
Предоставление разрешения на любой объект, за исключением новой схемы. - revoke_statement
Отмена разрешения на любой объект, за исключением новой схемы. - deny_statement
Запрещение разрешения на любой объект, за исключением новой схемы.
Транзакция CREATE SCHEMA являются атомарной. Если в процессе выполнения инструкции CREATE SCHEMA возникают ошибки, то ни один из указанных объектов не создается и ни одно разрешение не предоставляется.
Объекты, которые необходимо создать при помощи инструкции CREATE SCHEMA, могут быть перечислены в любом порядке, за исключением представлений, ссылающихся на другие представления. В этом случае ссылающееся представление должно быть создано после того представления, на которое оно ссылается.
При помощи инструкции GRANT можно предоставлять разрешения на объект еще до того, как он будет создан, а инструкция CREATE VIEW может появляться раньше инструкций CREATE TABLE, создающих таблицы, на которые ссылается представление. Кроме того, инструкции CREATE TABLE могут декларировать внешние ключи к таблицам, определенным позже в инструкции CREATE SCHEMA.
Создание схемы PostgreSQL
Новая схема создается в текущей базе данных сервера, с которым установлено соединение. Наименование схемы должно быть уникально для данной Database.
- username
имя пользователя (role name) владельца схемы. Если username отсутствует, то будет использоваться имя пользователя, исполняющего данный скрипт. Чтобы создать схему для владельца с другой ролью (role) необходимо иметь права данной роли или быть superuser’ом. - schema_element
определяет SQL скрипт одновременного создания внутри схемы объектов. Вместе со схемой можно создать TABLE, VIEW, INDEX, SEQUENCE, TRIGGER с получением на них соответствующих прав. Если в скрипте присутствует AUTHORIZATION username то все права будут принадлежать username. После создания схемы можно также создать в ней другие объекты.
Примеры создания схемы в PostgreSQL :
Примечание : Согласно SQL стандарту, владелец схемы всегда является "хозяином" всех находящихся внутри объектов. PostgreSQL, также как и MSSQL, разрешает создание внутри схем объектов, "хозяином" которых может быть не владелец схемы, но имеющий соответствующие привилегии данной схемы.
Создание базы данных MySQL
В MySQL понятие базы данных database равносильно понятию schema в других СУБД типа MSSQL, PostgreSQL, Derby и имеет тот же смысл — контейнер не привязанных к пользователю объектов. Создание базы данных выполняется с помощью оператора CREATE DATABASE.
- db_name
Имя создаваемой базы данных. - IF NOT EXISTS
Если данный паарметр не указать, то при создании базы данных с уже существующим именем, возникнет ошибка выполнения команды. - CHARACTER SET
Определение кодировки таблиц базы данных. - COLLATE
Определение порядка сортировки данных.
Если при создании таблицы эти параметры CHARACTER SET и COLLATE не указываются, то кодировка и порядок сортировки вновь создаваемой таблицы берутся из значений, указанных для текущей базы данных.
Примеры использования CREATE DATABASE
Создание схемы Derby
Наименование схемы не должно содержать более 128 символов и быть уникальным внутри базы данных. Также наименование не должно начинаться с префикса SYS.
Только владелец базы данных может создавать схему с наименованием, отличным от имени/логина пользователя, и только владелец базы данных может определять AUTHORIZATION username с именем/логином пользователя, отличным от текущего логина.
Примечание : username может принадлежать только пользователю, а не role.
Удаление схемы, DROP SCHEMA
Для удаления схемы необходимо использовать SQL скрипт drop schema.
Удаление схемы Oracle
Для удаление схемы СУБД Oracle необходимо удалить пользователя; объекты схемы удаляются автоматически :
Ключевое слово CASCADE означает удалить все связанное со схемой (пользователем) объекты.
Удаление схемы MSSQL
Удаляемая схема не должна содержать никаких объектов. Если схема содержит объекты, выполнение инструкции DROP заканчивается сбоем. Сведения о схемах можно увидеть в представлении каталога sys.schemas.
Удаление схемы PostgreSQL
Схема может быть удалена только её владельцем или superuser’ом. Необходимо помнить, что владелец owner может удалить схему и все содержащиеся в ней объекты даже если они ему не принадлежат.
При удалении схемы в PostgreSQL можно дополнительно включить параметры :
- IF EXISTS
Проверка наличия схемы. Если схемы нет, то исключительная ситуация не возникнет. - CASCADE
Автоматически удалять объекты, содержащиеся в схеме. - RESTRICT
Не удалять схему, если она содержит объекты. Этот параметр используется по умолчанию.
Пример удаления схемы orders вместе с содержащимися в ней объектами :
Удаление базы данных MySQL
В СУБД MySQL удалить можно не только пустую базу данных.
Если не указать параметр IF EXISTS, то при попытке удаления не существующей базы данных, возникнет ошибка выполнения команды. Данный параметр доступен в MySQL 3.22 и более поздних версиях. При выполнении команды DROP DATABASE удаляется как сама база данных, так и все объекты, которые в ней находятся.
В следующем примере удаляется база данных "forum" :
Удаление схемы Derby
В СУБД Derby удалить можно только пустую схему. Схемы SYS и APP (схема пользователя по умолчанию) не могут быть удалены.
Ключевое слово RESTRICT является обязательным и обязывает выполнение проверки наличия объектов в удаляемой схеме.
Обновление схемы, ALTER SCHEMA
В SQL стандарте скрипт ALTER SCHEMA не определен.
В PostgreSQL владельца или наименование схемы можно изменить скриптом ALTER SCHEMA.
Чтобы использовать ALTER SCHEMA необходимо быть владельцем схемы и иметь соответствующие привилегии. При изменении наименования схемы нужно иметь привилегии CREATE для текущей базы данных. Чтобы сменить владельца, необходимо быть членом соответствующей роли и иметь в ней привилегии CREATE.
В СУБД MSSQL с помощью скрипта ALTER SCHEMA можно перенести объекты из одной схемы в другую.
Пользователи и схемы в MSSQL полностью разделены. Инструкция ALTER SCHEMA применяется только для перемещения объектов между схемами в пределах одной базы данных. В следующем примере схема Customers изменяется путем перемещения в нее таблицы Cities из схемы Persons.
Как создать схему в postgresql
Кластер баз данных PostgreSQL содержит одну или более баз данных. Пользователи и группы пользователей являются общими внутри этого кластера, но прочие данные являются общими только внутри базы данных. Все клиентские подключения к серверу могут иметь доступ только к данным в одной базе данных: той, которая задана в параметрах подключения.
Note: Пользователям кластера нет необходимости иметь привилегию для доступа к любой базе данных кластера. Общие имена пользователей для кластера означают, что в двух базах данных в одном кластетере не может быть другого пользователя, скажем с именем joe , но СУБД можно настроить так, что разрешить пользователю joe доступ только к некоторым базам данных кластера.
База данных содержит одну или более именованных схем , которые содержат таблицы. Схемы также содержат и други виды именованных объектов, включая типы данных, функции и операторы. То же самое имя объекта может без какого-либо конфликта использоваться в других схемах, например, и схема schema1 и myschema могут содержат таблицы с именем mytable . В отличие от баз данных, схемы жёстко не разделяются: пользователь может получить доступ к объектам в любой схеме в пределах базы данных, к которой он подключён, если у него есть соответствующие привилегии.
Существует несколько причин, почему вы можете захотеть использовать схемы:
Чтобы разрешить нескольким пользователям использовать одну базу данных, не смешивая их данные.
Чтобы организовать объекты базы данных в логические группы, для того чтобы сделать их более прозрачными в управлении.
Разные приложения могут быть размещены в отдельных схемах, чтобы не было конфликтов в использовании имён или других объектов.
Схемы являются аналогами каталогов в операционной системе, за исключением того, что схемы не могут быть вложенными.
CREATE SCHEMA myschema;
Чтобы создать объект в схеме или получить к нему доступ, необходимо указать полное имя , которое состоит из имени схемы и имени таблицы, которые отделяются друг от друга точкой:
Это работает везде, где необходимо указывать имя таблицы, включая команды модификации таблиц и команды доступа к данными, о которых рассказывалось в предыдущих главах. (Для краткости мы будем говорить только о таблицах, хотя те же самые идеи применимы и к другим видам объектов, таким как типы данных и функции.)
Фактически, можно также использовать более общий синтаксис:
база . схема . таблица
но его наличие необходимо только для проформы , для соответствия стандарту SQL. Если вы указываете имя базы данных, оно должно совпадать с той базой данных, к которой вы подключились.
Так, для создания таблицы в новой схеме, используйте
CREATE TABLE myschema.mytable ( . );
Чтобы удалить схему, если она пуста (все объекты внутри схемы удалены) используйте
DROP SCHEMA myschema;
Чтобы удалить схему, включая все содержащиеся внутри неё объекты, используйте
DROP SCHEMA myschema CASCADE;
Подробности о том как это работает см. в Section 5.12 .
Часто вы будете хотеть создать схему, владелецем которой является кто-то другой (поскольку это один из способов ограничить активность ваших пользователей в пределах назначенных им пространств имён). Вот синтакс, который позволит вам сделать это:
CREATE SCHEMA имя_схемы AUTHORIZATION имя_пользователя ;
Вы даже можете опустить имя схемы и в этом случае имя схемы будет установлено такое же как и имя пользователя. О том как этим можно воспользоваться см. в Section 5.7.6 .
Имена схем, которые начинаются на pg_ являются зарезервированными для системных целей и не могут быть созданы пользователями.
В предыдущих секциях мы создавали таблицы без указания имён схем. По умолчанию, такие таблицы (и другие объекты) автоматически помещаются в схему с именем «public» . Каждая новая база данных содержит такую схему. Таким образом, следующие примеры эквивалентны:
CREATE TABLE products ( . );
CREATE TABLE public.products ( . );
Полные имена довольно утомительно писать и часто в приложении лучше бывает нигде не привязываться к отдельному имени схемы. Таким образом на таблицы ссылаются через неполные имена , которые содержат только имя таблицы. СУБД определяет какая таблица имеется в виду с помощью пути поиска , который является списком схем, в которых осуществляется поиск. Первая совпавшая таблица в пути поиска считается той, которая запрашивалась. Если совпадающих имён в пути поиска не найдено, будет выдана ошибка, даже если совпадающее имя таблицы существует в других схемах текущей базы данных.
Первая схема в пути поиска называется текущей схемой. Кроме того, что текущая схема будет находится первой, она также является схемой, где будут создаваться новые таблицы командой CREATE TABLE , в которой не указано имя схемы.
Чтобы посмотреть текущий путь поиска, используйте команду:
В случае настроек по умолчанию, она возвращает:
Первый элемент говорит, что поиск должен производится в схеме с таким же именем как и у текущего пользователя. Если такой схемы не существует, данный элемент игнорируется. Второй элемент указывает на схему public, которую мы уже рассматривали.
Первая существующая схема в пути поиска является местоположением по умолчанию, для создания новых объектов. Именно по этой причине, по умолчанию объекты создаются в схеме public. Когда на объекты ссылаются из любых других контекстов без указания схемы (модификация таблиц, данных или команды запросов) происходит перебор схем в пути поиска пока не будет найден совпавший объект. Следовательно, в конфигурации по умолчанию, все операции без указания имени схемы снова указывают только на схему public.
Чтобы поместить новую схему в путь поисках, используйте команду:
SET search_path TO myschema,public;
(Здесь мы опустили $user , потому что эта схема в данный момент нам не нужна.) И после этого, мы получаем доступ к таблице без указания схемы:
DROP TABLE mytable;
Также, поскольку myschema является первым элементом в пути поиска, именно в ней по умолчанию будут создаваться новые объекты.
Мы также можем написать:
SET search_path TO myschema;
Тогда мы не сможем больше обращаться к схеме public без явного указания имени. В схеме public не существует ничего особенного, кроме того, что она существует по умолчанию. Её можно даже удалить.
Другие способы манипулирования путём поиска схем см. в Section 9.23 .
Путь поиска, точно также как и для таблиц, работает и для имён типов данных, имён функций и операторов. Имена типов данных и функций могут быть указаны и в полном виде, точно также как и имена таблиц. Если вам необходимо написать полное имя оператора в выражении, существует специальный синтаксис: вы должны написать
OPERATOR( схема . оператор )
Это необходимо для избежания неоднозначностей в синтаксе. Вот пример:
SELECT 3 OPERATOR(pg_catalog.+) 4;
На практике обычно для операторов используется путь поиска, так что вам не придётся писать что-либо так некрасиво как в данном примере.
По умолчанию, пользователи не имеют доступа к любым объектам в тех схемах, для которых они не являются владельцами. Чтобы у них был доступ, владелец схемы должен предоставить им привилегию USAGE на схему. Чтобы разрешить пользователям использование объектов в схеме, им необходимо предоставить дополнительные привилегии, соответственно для каждого объекта.
Пользователю также можно разрешить создавать объекты в какой-либо другой схеме. Для этого ему нужно предоставить привилегию CREATE на схему. Заметим, что по умолчанию, для схемы public привилегии CREATE и USAGE имеет любой пользователь. Это позволяет всем подключенным к базе данных пользователям, создавать объекты в схеме public . Если вы не хотите разрешать это, вы можете отобрать данную привилегию:
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
(В этой команде при первом появлении «public» означает схему, а при втором означает «каждый пользователь» . При первом появлении — это идентификатор, а при втором — ключевое слово, поэтому они и записываются в разных регистрах; согласно рекомендациям из Section 4.1.1 .)
В дополнение к схеме public и схемам, которые определяются пользователем, каждая база данных содержит схему pg_catalog , которая содержит системные таблицы и все встроенные типы данных, функции и операторы. pg_catalog всегда является эффективной частью пути поиска. Если эта схема не указывается явно в пути поиска, то она всё-равно неявно используется при поиске перед схемами, указанными в пути поиска. Однако, вы можете явно поместить схему pg_catalog в конец вашего пути поиска, если хотите, чтобы имена, определённые пользователем перекрывали встроенные имена.
В PostgreSQL до версии 7.3, имена таблиц, начинавшиеся с pg_ были зарезервированы. Теперь этого больше нет: если хотите, вы можете создавать такие имена таблиц, в любой несистемной схеме. Однако, лучше всё-таки продолжать избегать таких имён, чтобы быть уверенным в невозможности конфликтов по именам, если в будущем, в какой-либо версии появится системная таблица с таким же именем как и ваша. (С путём поиска по умолчанию, неполное имя вашей таблицы будет распознано как системная таблица.) Системные таблицы будут продолжать следовать соглашению по именам, начинающимся с pg_ , так что они не будут конфликтовать с неполными именами пользовательских таблиц, если пользователи будут избегать использования приставки pg_ .
Схемы могут быть использованы для организации ваших данных во многих случаях. Существует несколько рекомендуемых вариантов использования схем, которые поддерживаются в конфигурации по умолчанию:
Если вы не создаёте никаких схем, то все пользователи неявно получают доступ к схеме public. Это симулирует ситуацию, когда схемы недоступны никому. Такая стратегия рекомендуется в основном, когда в базе данных работает только один пользователь или группа скооперированных пользователей. Также данная стратегия позволяет смягчить переход из СУБД, где нет схем.
Вы можете создавать схему для каждого пользователя с именем, таким же как и у пользователя. Вспомните, что по умолчанию путь поиска начинается с элемента $user , который означает имя текущего пользоватля. Следовательно, если каждый пользователь имеет отдельную схему, то он по умолчанию получает доступ к своей схеме.
Если вы используете такую стратегию, то вы можете также захотеть отобрать доступ к схеме public (или вообще удалить её), так что пользователи будут ограничены только своими схемами.
Чтобы установить разделяемые приложения (где таблицы должны использоваться любым пользователем или в случае дополнительных функций от сторонних разработчиков и т.д.), поместите их в отдельные схемы. Помните, что необходимо предоставить соответствующие привилегии, чтобы разрешить доступ к ним другим пользователям. Пользователи могут затем ссылаться на дополнительные объекты через указания полных имен (со схемой) или они могут поместить нужные схемы в свой путь поиска, как им больше понравится.
В стандарте SQL, не существует такого понятия, что у объектов в одной и той же схеме может быть разный владелец. Кроме того, некоторые реализации не разрешают создавать схемы, с именем отличным от имени их владельца. Фактически, концептуальные понятия схемы и пользователя, в тех СУБД, которые реализуют только базовую поддержку схем, обозначенную в стандарте, являются почти эквивалентыми. Следовательно, многие пользователи рассматривают полные имена, как реально состоящие из имя_пользователя . имя_таблицы . Это к вопросу, что PostgreSQL будет работать эффективно, если вы создадите пользовательскую схему для каждого пользователя.
Также, в стандарте SQL не существует концепции схемы public . Для максимального соответствия стандарту, вы не должны использовать (возможно даже удалить) схему public .
Разумеется, некоторые SQL СУБД могут вообще не иметь реализации схем или предоставлять поддержку пространств имён с помощью разрешения (возможно ограниченного) кросс-доступа между базами данных. Если вам необходимо работать с такими СУБД, то для достижения максимальной переностимости, вы не должны вообще использовать схемы.