Как создать схему в postgresql

Как создать схему в postgresql

A PostgreSQL database cluster contains one or more named databases. Roles and a few other object types are shared across the entire cluster. A client connection to the server can only access data in a single database, the one specified in the connection request.

Users of a cluster do not necessarily have the privilege to access every database in the cluster. Sharing of role names means that there cannot be different roles named, say, joe in two databases in the same cluster; but the system can be configured to allow joe access to only some of the databases.

A database contains one or more named schemas, which in turn contain tables. Schemas also contain other kinds of named objects, including data types, functions, and operators. The same object name can be used in different schemas without conflict; for example, both schema1 and myschema can contain tables named mytable . Unlike databases, schemas are not rigidly separated: a user can access objects in any of the schemas in the database they are connected to, if they have privileges to do so.

There are several reasons why one might want to use schemas:

To allow many users to use one database without interfering with each other.

To organize database objects into logical groups to make them more manageable.

Schemas are analogous to directories at the operating system level, except that schemas cannot be nested.

5.8.1. Creating a Schema

To create a schema, use the CREATE SCHEMA command. Give the schema a name of your choice. For example:

To create or access objects in a schema, write a qualified name consisting of the schema name and table name separated by a dot:

This works anywhere a table name is expected, including the table modification commands and the data access commands discussed in the following chapters. (For brevity we will speak of tables only, but the same ideas apply to other kinds of named objects, such as types and functions.)

Actually, the even more general syntax

can be used too, but at present this is just for pro forma compliance with the SQL standard. If you write a database name, it must be the same as the database you are connected to.

So to create a table in the new schema, use:

To drop a schema if it’s empty (all objects in it have been dropped), use:

To drop a schema including all contained objects, use:

See Section 5.13 for a description of the general mechanism behind this.

Often you will want to create a schema owned by someone else (since this is one of the ways to restrict the activities of your users to well-defined namespaces). The syntax for that is:

You can even omit the schema name, in which case the schema name will be the same as the user name. See Section 5.8.6 for how this can be useful.

Schema names beginning with pg_ are reserved for system purposes and cannot be created by users.

5.8.2. The Public Schema

In the previous sections we created tables without specifying any schema names. By default such tables (and other objects) are automatically put into a schema named “ public ” . Every new database contains such a schema. Thus, the following are equivalent:

5.8.3. The Schema Search Path

Qualified names are tedious to write, and it’s often best not to wire a particular schema name into applications anyway. Therefore tables are often referred to by unqualified names, which consist of just the table name. The system determines which table is meant by following a search path, which is a list of schemas to look in. The first matching table in the search path is taken to be the one wanted. If there is no match in the search path, an error is reported, even if matching table names exist in other schemas in the database.

The ability to create like-named objects in different schemas complicates writing a query that references precisely the same objects every time. It also opens up the potential for users to change the behavior of other users’ queries, maliciously or accidentally. Due to the prevalence of unqualified names in queries and their use in PostgreSQL internals, adding a schema to search_path effectively trusts all users having CREATE privilege on that schema. When you run an ordinary query, a malicious user able to create objects in a schema of your search path can take control and execute arbitrary SQL functions as though you executed them.

The first schema named in the search path is called the current schema. Aside from being the first schema searched, it is also the schema in which new tables will be created if the CREATE TABLE command does not specify a schema name.

To show the current search path, use the following command:

In the default setup this returns:

The first element specifies that a schema with the same name as the current user is to be searched. If no such schema exists, the entry is ignored. The second element refers to the public schema that we have seen already.

The first schema in the search path that exists is the default location for creating new objects. That is the reason that by default objects are created in the public schema. When objects are referenced in any other context without schema qualification (table modification, data modification, or query commands) the search path is traversed until a matching object is found. Therefore, in the default configuration, any unqualified access again can only refer to the public schema.

To put our new schema in the path, we use:

(We omit the $user here because we have no immediate need for it.) And then we can access the table without schema qualification:

Also, since myschema is the first element in the path, new objects would by default be created in it.

We could also have written:

Then we no longer have access to the public schema without explicit qualification. There is nothing special about the public schema except that it exists by default. It can be dropped, too.

See also Section 9.25 for other ways to manipulate the schema search path.

The search path works in the same way for data type names, function names, and operator names as it does for table names. Data type and function names can be qualified in exactly the same way as table names. If you need to write a qualified operator name in an expression, there is a special provision: you must write

This is needed to avoid syntactic ambiguity. An example is:

In practice one usually relies on the search path for operators, so as not to have to write anything so ugly as that.

5.8.4. Schemas and Privileges

By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. To allow users to make use of the objects in the schema, additional privileges might need to be granted, as appropriate for the object.

A user can also be allowed to create objects in someone else’s schema. To allow that, the CREATE privilege on the schema needs to be granted. Note that by default, everyone has CREATE and USAGE privileges on the schema public . This allows all users that are able to connect to a given database to create objects in its public schema. Some usage patterns call for revoking that privilege:

(The first “ public ” is the schema, the second “ public ” means “ every user ” . In the first sense it is an identifier, in the second sense it is a key word, hence the different capitalization; recall the guidelines from Section 4.1.1.)

5.8.5. The System Catalog Schema

In addition to public and user-created schemas, each database contains a pg_catalog schema, which contains the system tables and all the built-in data types, functions, and operators. pg_catalog is always effectively part of the search path. If it is not named explicitly in the path then it is implicitly searched before searching the path’s schemas. This ensures that built-in names will always be findable. However, you can explicitly place pg_catalog at the end of your search path if you prefer to have user-defined names override built-in names.

Since system table names begin with pg_ , it is best to avoid such names to ensure that you won’t suffer a conflict if some future version defines a system table named the same as your table. (With the default search path, an unqualified reference to your table name would then be resolved as the system table instead.) System tables will continue to follow the convention of having names beginning with pg_ , so that they will not conflict with unqualified user-table names so long as users avoid the pg_ prefix.

5.8.6. Usage Patterns

Schemas can be used to organize your data in many ways. A secure schema usage pattern prevents untrusted users from changing the behavior of other users’ queries. When a database does not use a secure schema usage pattern, users wishing to securely query that database would take protective action at the beginning of each session. Specifically, they would begin each session by setting search_path to the empty string or otherwise removing non-superuser-writable schemas from search_path . There are a few usage patterns easily supported by the default configuration:

Constrain ordinary users to user-private schemas. To implement this, issue REVOKE CREATE ON SCHEMA public FROM PUBLIC , and create a schema for each user with the same name as that user. Recall that the default search path starts with $user , which resolves to the user name. Therefore, if each user has a separate schema, they access their own schemas by default. After adopting this pattern in a database where untrusted users had already logged in, consider auditing the public schema for objects named like objects in schema pg_catalog . This pattern is a secure schema usage pattern unless an untrusted user is the database owner or holds the CREATEROLE privilege, in which case no secure schema usage pattern exists.

Remove the public schema from the default search path, by modifying postgresql.conf or by issuing ALTER ROLE ALL SET search_path = «$user» . Everyone retains the ability to create objects in the public schema, but only qualified names will choose those objects. While qualified table references are fine, calls to functions in the public schema will be unsafe or unreliable. If you create functions or extensions in the public schema, use the first pattern instead. Otherwise, like the first pattern, this is secure unless an untrusted user is the database owner or holds the CREATEROLE privilege.

For any pattern, to install shared applications (tables to be used by everyone, additional functions provided by third parties, etc.), put them into separate schemas. Remember to grant appropriate privileges to allow the other users to access them. Users can then refer to these additional objects by qualifying the names with a schema name, or they can put the additional schemas into their search path, as they choose.

5.8.7. Portability

In the SQL standard, the notion of objects in the same schema being owned by different users does not exist. Moreover, some implementations do not allow you to create schemas that have a different name than their owner. In fact, the concepts of schema and user are nearly equivalent in a database system that implements only the basic schema support specified in the standard. Therefore, many users consider qualified names to really consist of user_name . table_name . This is how PostgreSQL will effectively behave if you create a per-user schema for every user.

Also, there is no concept of a public schema in the SQL standard. For maximum conformance to the standard, you should not use the public schema.

Of course, some SQL database systems might not implement schemas at all, or provide namespace support by allowing (possibly limited) cross-database access. If you need to work with those systems, then maximum portability would be achieved by not using schemas at all.

Name already in use

postgresql-spot / schemas and priveleges / postgresql_schema.md

• Go to file T
• Go to line L
• Copy path
• Copy permalink

• Open with Desktop
• View raw
• Copy raw contents Copy raw contents

Copy raw contents

Copy raw contents

Схемы и привилегии

Схемы в некоторым смысле подобны каталогам в операционной системе, но они не могут быть вложенными.
Схема по сути представляет собой пространство имён: она содержит именованные объекты (таблицы, типы данных, функции и операторы), имена которых могут совпадать с именами других объектов, существующих в других схемах. Для обращения к объекту нужно либо «дополнить» его имя именем схемы в виде префикса, либо установить путь поиска, включающий требуемую схему. Команда CREATE , в которой указывается неполное имя объекта, создаёт объект в текущей схеме (схеме, стоящей первой в пути поиска; узнать её позволяет функция current_schema ).

Есть несколько возможных объяснений, для чего стоит применять схемы:

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

Более подробно про варианты применения схем будет описано ниже (в главе Шаблоны использования).

Команда CREATE SCHEMA может дополнительно включать подкоманды, создающие объекты в новой схеме. Эти подкоманды по сути воспринимаются как отдельные команды, выполняемые после создания схемы, за исключением того, что с предложением AUTHORIZATION все создаваемые объекты будут принадлежать указанному в нём пользователю.

Параметр элемент_схемы
Оператор SQL, определяющий объект, создаваемый в новой схеме. В настоящее время CREATE SCHEMA может содержать только подкоманды CREATE TABLE , CREATE VIEW , CREATE INDEX , CREATE SEQUENCE , CREATE TRIGGER и GRANT . Создать объекты других типов можно отдельными командами после создания схемы.

Чтобы создать схему, пользователь должен иметь право CREATE в текущей базе данных. (Разумеется, на суперпользователей это условие не распространяется.)

Создание и удаление схемы

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

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

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

Обращение к объектам в схеме

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

Путь поиска схем (search_path)

Чтобы каждый раз не указывать схему явно, можно задать путь поиска ( search_path ). Первая существующая схема в пути поиска называется текущей. Эта схема будет использоваться не только при поиске, но и при создании объектов.

Если объекты отсутствуют в первой схеме, поиск продолжается во всех остальных схемах из search_path , пока объект не будет найден.

Если объект не существует или находится в схеме, не добавленной в search_path , мы получим ошибку.

Примечание: Путь поиска можно изменить во время выполнения следующей командой:

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

Если search_path не был изменен и в базе данных нет схемы, совпадающей с именем текущего пользователя, все объекты без явного указания схемы будут создаваться в public .

Схема системного каталога

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

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

Функции получения информации о схеме

• Функция current_schema возвращает имя схемы, которая стоит первой в пути поиска (или NULL, если путь поиска пуст). Эта схема будет задействована при создании таблиц или других именованных объектов, если целевая схема не указана явно.

• Функция current_schemas(boolean) возвращает массив имён всех схем, находящихся в пути поиска. Её логический параметр определяет, будут ли включаться в результат неявно добавляемые в путь поиска системные схемы, такие как pg_catalog.

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

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

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

Установка владельца схемы

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

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

Привелегии на уровне объектов схемы

Можно выдать доступ как к одному, так и всем подобным объектам (таблицам, последовательностям, функциям) в рамках схемы:

Шаблоны использования схем

Ограничить обычных пользователей личными схемами. Для реализации этого подхода выполните REVOKE CREATE ON SCHEMA public FROM PUBLIC (первое слово public обозначает схему, а второе означает «каждый пользователь». В первом случае это идентификатор, а во втором — ключевое слово) и создайте для каждого пользователя схему с его именем. Если затрагиваемые пользователи подключались к базе ранее, проведите аудит схемы на предмет наличия таких же имён, как в схеме pg_catalog . Вспомните, что путь поиска по умолчанию начинается со значения $user , которое разрешается в имя пользователя. Таким образом, если у всех пользователей будет отдельная схема, они по умолчанию будут обращаться к собственным схемам.

Удалить схему public из пути поиска по умолчанию для каждого пользователя, с помощью команды ALTER ROLE пользователь SET search_path = «$user» . Все сохранят возможность создавать объекты в общедоступной схеме, но обращаться к ним будут только по полным именам. Пользователь, имеющий право CREATEROLE , сможет отменить присвоение этого пути и выполнять произвольные запросы от имени пользователей, полагающихся на расширенный путь. Если вы даёте пользователям право CREATEROLE , но не хотите, чтобы они стали практически суперпользователями, вам нужно использовать первый шаблон.

Удалить схему public из пути поиска ( search_path ) в postgresql.conf . Это будет иметь такое же влияние на пользователей, что и предыдущий шаблон. При этом такое же влияние, как обладатели права CREATEROLE , смогут оказывать и владельцы баз данных. Если вы даёте пользователям права CREATEROLE , CREATEDB или делаете их владельцами отдельных баз данных, но не хотите, чтобы они стали практически суперпользователями, вам нужно использовать первый шаблон.

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

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

Как создать схему в postgresql

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

Примечание

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

База данных содержит одну или несколько именованных схем, которые в свою очередь содержат таблицы. Схемы также содержат именованные объекты других видов, включая типы данных, функции и операторы. Одно и то же имя объекта можно свободно использовать в разных схемах, например и schema1 , и myschema могут содержать таблицы с именем mytable . В отличие от баз данных, схемы не ограничивают доступ к данным: пользователи могут обращаться к объектам в любой схеме текущей базы данных, если им назначены соответствующие права.

Есть несколько возможных объяснений, для чего стоит применять схемы:

Чтобы одну базу данных могли использовать несколько пользователей, независимо друг от друга.

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

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

5.9.1. Создание схемы

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

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

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

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

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

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

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

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

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

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

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

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

5.9.2. Схема public

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

5.9.3. Путь поиска схемы

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

Возможность создавать одноимённые объекты в разных схемах усложняет написание запросов, которые должны всегда обращаться к конкретным объектам. Это также потенциально позволяет пользователям влиять на поведение запросов других пользователей, злонамеренно или случайно. Ввиду преобладания неполных имён в запросах и их использования внутри PostgreSQL , добавить схему в search_path — по сути значит доверять всем пользователям, имеющим право CREATE в этой схеме. Когда вы выполняете обычный запрос, злонамеренный пользователь может создать объекты в схеме, включённой в ваш путь поиска, и таким образом перехватывать управление и выполнять произвольные функции SQL как если бы их выполняли вы.

Первая схема в пути поиска называется текущей. Эта схема будет использоваться не только при поиске, но и при создании объектов — она будет включать таблицы, созданные командой CREATE TABLE без указания схемы.

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

В конфигурации по умолчанию она возвращает:

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

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

Чтобы добавить в путь нашу новую схему, мы выполняем:

(Мы опускаем компонент $user , так как здесь в нём нет необходимости.) Теперь мы можем обращаться к таблице без указания схемы:

И так как myschema — первый элемент в пути, новые объекты будут по умолчанию создаваться в этой схеме.

Мы можем также написать:

Тогда мы больше не сможем обращаться к схеме public, не написав полное имя объекта. Единственное, что отличает схему public от других, это то, что она существует по умолчанию, хотя её так же можно удалить.

В Разделе 9.26 вы узнаете, как ещё можно манипулировать путём поиска схем.

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

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

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

5.9.4. Схемы и права

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

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

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

5.9.5. Схема системного каталога

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

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

5.9.6. Шаблоны использования

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

Ограничить обычных пользователей личными схемами. Для реализации этого подхода сначала убедитесь, что ни у одной схемы нет права CREATE . Затем для каждого пользователя, который будет создавать не временные объекты, создайте схему с его именем, например CREATE SCHEMA alice AUTHORIZATION alice . (Как вы знаете, путь поиска по умолчанию начинается с имени $user , вместо которого подставляется имя пользователя. Таким образом, если у всех пользователей будет отдельная схема, они по умолчанию будут обращаться к собственным схемам.) Этот шаблон позволяет безопасно использовать схемы, только если никакой недоверенный пользователь не является владельцем базы данных и не имеет права CREATEROLE . В противном случае безопасное использование схем невозможно.

В PostgreSQL 15 и выше этот подход использования поддерживается конфигурацией по умолчанию. В предыдущих версиях или при использовании базы данных, обновлённой с предыдущей версии, вам необходимо удалить право CREATE из схемы public (выполнить REVOKE CREATE ON SCHEMA public FROM PUBLIC ). Затем проверьте, нет ли в схеме public объектов с такими же именами, как у объектов в схеме pg_catalog .

Удалить схему public из пути поиска по умолчанию, изменив postgresql.conf или выполнив команду ALTER ROLE ALL SET search_path = «$user» . Затем следует предоставить права на создание объектов в схеме public. Выбираться объекты в этой схеме будут только по полному имени. Тогда как обращаться к таблицам по полному имени вполне допустимо, обращения к функциям в общей схеме всё же будут небезопасными или ненадёжными. Поэтому если вы создаёте функции или расширения в схеме public, применяйте первый шаблон. Если же нет, этот шаблон, как и первый, безопасен при условии, что никакой недоверенный пользователь не является владельцем базы данных и не имеет права CREATEROLE .

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

5.9.7. Переносимость

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

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

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

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.