Главная страница » Что такое схема в postgresql

Что такое схема в 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.

Third-party applications can be put into separate schemas so they do not collide with the names of other objects.

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

5.9.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.14 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.9.6 for how this can be useful.

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

5.9.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.9.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.26 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.9.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. By default, everyone has that privilege on the schema public . To allow users to make use of the objects in a 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. In databases upgraded from PostgreSQL 14 or earlier, everyone has that privilege on the schema public . 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.9.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.9.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 schemas that are writable by non-superusers 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 pattern, first ensure that no schemas have public CREATE privileges. Then, for every user needing to create non-temporary objects, create a schema with the same name as that user, for example CREATE SCHEMA alice AUTHORIZATION alice . (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.) 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.

In PostgreSQL 15 and later, the default configuration supports this usage pattern. In prior versions, or when using a database that has been upgraded from a prior version, you will need to remove the public CREATE privilege from the public schema (issue REVOKE CREATE ON SCHEMA public FROM PUBLIC ). Then consider auditing the public schema for objects named like objects in schema pg_catalog .

Remove the public schema from the default search path, by modifying postgresql.conf or by issuing ALTER ROLE ALL SET search_path = «$user» . Then, grant privileges to create in the public schema. Only qualified names will choose public schema 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.

Keep the default search path, and grant privileges to create in the public schema. All users access the public schema implicitly. This simulates the situation where schemas are not available at all, giving a smooth transition from the non-schema-aware world. However, this is never a secure pattern. It is acceptable only when the database has a single user or a few mutually-trusting users. In databases upgraded from PostgreSQL 14 or earlier, this is the default.

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.9.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.

Prev Up Next
5.8. Row Security Policies Home 5.10. Inheritance

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

Что такое схема в 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 не следует.

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

Объекты PostgreSQL сервера и базы данных

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

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

Давайте познакомимся с этими объектами PostgreSQL.

Серверная служба#

При установке PostgreSQL у вас будет соответствующая служба сервера. Она также известна как сервер PostgreSQL. Вы можете установить несколько серверов PostgreSQL на физический сервер, используя разные порты и расположения мест для хранения данных.

Базы данных#

База данных — это контейнер для других объектов, таких как таблицы, представления, функции и индексы. На сервере PostgreSQL вы можете создать столько баз данных, сколько захотите.

Таблицы#

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

Схемы#

Схема — это логический контейнер таблиц и других объектов внутри базы данных. Каждая база данных PostgreSQL может иметь несколько схем. Важно отметить, что схемы являются частью стандарта ANSI-SQL.

Табличные пространства#

Табличное пространство — это место, где PostgreSQL хранит данные. Табличное пространство PostgreSQL позволяет легко перемещать данные в разные физически места с помощью простых команд. По умолчанию PostgreSQL предоставляет два табличных пространства: pg_default для хранения пользовательских данных и pg_global для хранения системных данных.

Представления#

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

Функции#

Функция — это многократно используемый блок кода SQL, который возвращает скалярное значение списка записей. В PostgreSQL функции также могут возвращать составные объекты.

Операторы#

Операторы являются символическими функциями. PostgreSQL также позволяет вам определять пользовательские операторы.

Приведения#

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

Последовательности#

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

Расширения#

Начиная с версии 9.1, PostgreSQL ввел концепцию расширений для объединения в один модуль других объектов, включая типы, приведения, индексы, функции и т.д. Цель расширений — облегчить поддержку.

В этом руководстве мы познакомили вас с наиболее распространенными объектами баз данных и серверов PostgreSQL. Уделите время изучению этих объектов, чтобы иметь представление о них, прежде чем перейти к следующему уроку, в которым вы узнаете, как использовать базовый оператор PostgreSQL SELECT для запроса данных из таблицы.

PostgreSQL Schema

Summary: in this tutorial, you will learn about PostgreSQL schema and how to use the schema search path to resolve objects in schemas.

What is a PostgreSQL schema

In PostgreSQL, a schema is a namespace that contains named database objects such as tables, views, indexes, data types, functions, stored procedures and operators.

To access an object in a schema, you need to qualify the object by using the following syntax:

A database can contain one or multiple schemas and each schema belongs to only one database. Two schemas can have different objects that share the same name.

For example, you may have sales schema that has staff table and the public schema which also has the staff table. When you refer to the staff table you must qualify it as follows:

Why do you need to use schemas

There are some scenarios that you want to use schemas:

  • Schemas allow you to organize database objects e.g., tables into logical groups to make them more manageable.
  • Schemas enable multiple users to use one database without interfering with each other.

The public schema

PostgreSQL automatically creates a schema called public for every new database. Whatever object you create without specifying the schema name, PostgreSQL will place it into this public schema. Therefore, the following statements are equivalent:

The schema search path

In practice, you will refer to a table without its schema name e.g., staff table instead of a fully qualified name such as sales.staff table.

When you reference a table using its name only, PostgreSQL searches for the table by using the schema search path, which is a list of schemas to look in.

PostgreSQL will access the first matching table in the schema search path. If there is no match, it will return an error, even the name exists in another schema in the database.

The first schema in the search path is called the current schema. Note that when you create a new object without explicitly specifying a schema name, PostgreSQL will also use the current schema for the new object.

The current_schema() function returns the current schema:

Here is the output:

This is why PostgreSQL uses public for every new object that you create.

To view the current search path, you use the SHOW command in psql tool:

The output is as follows:

  • The «$user» specifies that the first schema that PostgreSQL will use to search for the object, which has the same name as the current user. For example, if you use the postgres user to login and access the staff table. PostgreSQL will search for the staff table in the postgres schema. If it cannot find any object like that, it continues to look for the object in the public schema.
  • The second element refers to the public schema as we have seen before.

To create a new schema, you use the CREATE SCHEMA statement:

To add the new schema to the search path, you use the following command:

Now, if you create a new table named staff without specifying the schema name, PostgreSQL will put this staff table into the sales schema:

The following picture shows the new schema sales and the staff table that belongs to the sales schema:

PostgreSQL Schema Example

To access the staff table in the sales schema you can use one of the following statement:

The public schema is the second element in the search path, so to access the staff table in the public schema, you must qualify the table name as follows:

If you use the following command, you will need to explicitly refer to objects in the public schema using a fully qualified name:

The public schema is not a special schema, therefore, you can drop it too.

PostgreSQL schemas and privileges

Users can only access objects in the schemas that they own. It means they cannot access any objects in the schemas that do not belong to them.

To allow users to access the objects in the schema that they do not own, you must grant the USAGE privilege of the schema to the users:

To allow users to create objects in the schema that they do not own, you need to grant them the CREATE privilege of the schema to the users:

Note that, by default, every user has the CREATE and USAGE on the public schema.

PostgreSQL schema operations

  • To create a new schema, you use the CREATE SCHEMA statement.
  • To rename a schema or change its owner, you use the ALTER SCHEMA statement.
  • To drop a schema, you use the DROP SCHEMA statement.

In this tutorial, you have learned about the PostgreSQL schema and how PostgreSQL uses the search path to resolve object names.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *