Объекты 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
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.
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:
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.
Schema
Schema is a collection of logical structures of data. In PostgreSQL, schema is a named collection of tables, views, functions, constraints, indexes, sequences etc.
PostgreSQL supports having multiple schemas in a single database there by letting you namespace different features into different schemas.
For example, you can have a database named postgres and have multiple schemas based on your application like ecommerce , auth etc.
Here's the hierarchy:
- PostgreSQL can have multiple databases in each instance.
- Each database can have multiple schemas.
- Each schema can have multiple tables.
Creating a Schema in PostgreSQL
The SQL syntax for creating a schema in PostgreSQL looks like:
where you can replace <schema_name> with a name of choice.
Head to the psql terminal window and execute the following to create a new schema.
You should see the following output:
Dropping a Schema in PostgreSQL
The SQL syntax for dropping a schema in PostgreSQL looks like:
and in case you want to cascade delete all referenced objects, you can make use of
and replace <schema_name> with the name of choice of your schema.
Applying this to our newly created schema, this would translate to,
Fundamentally, schemas let users namespace their various application features, especially third-party stuff to have their own space and not interfere with the primary data source.
Especially with role based access, it's easier to restrict access to schemas.
Default Schema in PostgreSQL
By default, the public schema is used in PostgreSQL when you set it up for the first time. Of course, you can create and drop more.
Any SQL queries executed will run against the public schema by default unless explicitly mentioned.