PostgreSQL Tutorial (ii): schema schema detailed _postgresql

A database contains one or more named schemas, and the schema contains tables. The pattern also contains other named objects, including data types, functions, and operators. The same object name can be used in a different pattern without causing a conflict; For example, SCHEMA1 and MySchema can contain tables called mytable. Unlike databases, schemas are not strictly separate: a user can access objects in any pattern in the database to which he is connected, as long as he has permission.

There are several main reasons why we need the pattern:

1. Allows multiple users to use a database without interfering with other users.
2. Organize database objects into logical groups to make them easier to manage.
3. Third-party applications can be placed in different modes so that they do not conflict with the names of other objects.

1. Create Pattern:

 code as follows:

CREATE SCHEMA MySchema;

The above command allows you to create a pattern named MySchema that, when created, can have its own set of logical objects, such as tables, views, and functions.

2. Public mode:

Before we go into the next section, we need to explain the public mode here. Whenever we create a new database, PostgreSQL will automatically create the pattern for us. When you log on to the database, if there are no special designations, we will manipulate various data objects in the form of this mode (public), such as:

code as follows:

CREATE TABLE Products (...) are equivalent to create TABLE public.products (...)

3. Permission:
By default, users do not see objects in the pattern that do not belong to them. In order for them to see, the owner of the pattern needs to give usage permissions on the schema. In order for the user to use the object in the pattern, we may need to give additional permissions as long as it is appropriate for the object. PostgreSQL provides different types of permissions based on different objects, such as:

 code as follows:

GRANT all on SCHEMA myschema to public;

The all keyword above will contain the Create and usage two permissions. If the public mode has the Create permission for the MySchema mode, users who log in to the mode will be able to create arbitrary objects in MySchema mode, such as:

 code as follows:

CREATE TABLE myschema.products (
Product_no Integer,
Name text,
Price numeric CHECK (Price > 0),
);

When assigning permissions to all tables in a pattern, you need to split permissions into various table operations, such as:

 code as follows:

ALTER DEFAULT Privileges in SCHEMA MySchema
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER on TABLES to public;

When you assign permissions to all sequence sequence objects in a pattern, you need to split the permissions into various sequence operations, such as:

 code as follows:

ALTER DEFAULT Privileges in SCHEMA MySchema
GRANT SELECT, UPDATE, USAGE on sequences to public;

When you assign permissions to all functions in a pattern, only the execution permissions are considered, such as:

 code as follows:

ALTER DEFAULT Privileges in SCHEMA MySchema
GRANT EXECUTE on functions to public;

As you can see, it is extremely inconvenient to create various objects in public mode for the MySchema mode in the above way. Here's another way to go through the role object, log in directly and correlate to the MySchema object, and then you can create all the objects you want directly in MySchema mode.

 code as follows:

CREATE role MySchema LOGIN PASSWORD ' 123456 '; --Creates a role object that is associated with the pattern.
CREATE SCHEMA MySchema AUTHORIZATION MySchema; --associates the pattern with the specified role, and the schema and role names can be unequal.

Under the Linux shell, log on to the database with the MySchema role, and log on to the database successfully after the password is entered correctly. \ mytest

 code as follows:

/> psql-d mytest-u MySchema
Password:
mytest=> CREATE TABLE Test (I integer);
CREATE TABLE
Mytest=> \d--View this mode, and the list of tables information that the mode has permission to see.
List of relations
Schema | Name |  Type | Owner
------------+---------+------+----------
MySchema | Test | Table | MySchema
(1 rows)

4. Delete mode:

 code as follows:

DROP SCHEMA MySchema;

If you want to delete the schema and all its objects, use cascading deletes:

 code as follows:

DROP SCHEMA MySchema CASCADE;

5. Pattern Search Path:

When we use a database object, we can use its full name to locate the object, but it is often tedious to do so, and we have to type owner_name.object_name each time. PostgreSQL provides a pattern search path, which is similar to the $PATH environment variable in Linux, and when we execute a shell command, only the command is in the directory list of $path, we can execute it directly through the command name, otherwise we need to enter its full pathname. PostgreSQL also determines which table a table is by looking for a search path, which is a list of patterns that need to be looked up. The first table found in the search path will be treated as the selected table. If there is no matching table in the search path, an error is reported, even if the name of the matching table exists in other schemas in the database.
The first pattern in the search path is called the current pattern. In addition to the first pattern of the search, it is the pattern that the new table belongs to when the CREATE table does not declare the schema name. To display the current search path, use the following command:

 code as follows:

Mytest=> show Search_path;
Search_path
----------------
"$user", public
(1 row)

New schemas can be added to the search path, such as:

 code as follows:

SET Search_path to Myschema,public;

Sets the specified pattern for the search path, such as:

 code as follows:

SET Search_path to MySchema; --The current search path will only contain a myschema pattern.