PostgreSQL tutorial of ii: mode Schema in detail

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

      we need patterns for the following main reasons:

1). Allows multiple users to use a database without disturbing other users.
Organize database objects into logical groups to make them more manageable.
      3). Third-party applications can be placed in different schemas so that they do not conflict with the names of other objects.
       
1. Create mode:
 

    CREATE SCHEMA myschema;

 

From the above command,       can create a schema with the name myschema, after which it can have its own set of logical objects, such as tables, views, functions, and so on.
       
      2. public mode:

Before going on to the rest, we need to explain the public pattern here. Whenever we create a new database, PostgreSQL automatically creates the schema for us. When logging into the database, we will manipulate various data objects in this mode (public), such as
, if no special specification is given  

    CREATE TABLE products ( ... )  Is equivalent to  CREATE TABLE public.products ( ... )

    

3. Permission:
By default, users cannot see objects in the schema that do not belong to them. For them to see, the owner of the pattern needs to give USAGE permission on the pattern. In order for the user to use an object in the schema, we may need to grant additional permissions, as long as they are appropriate for that object. PostgreSQL provides different permission types for different objects, such as
 

    GRANT ALL ON SCHEMA myschema TO public; 

 

The ALL keyword above       will contain both CREATE and USAGE permissions. If the public schema has the CREATE permission of myschema schema, then the user logged in to public schema will be able to create any object in myschema schema, such as
 

    CREATE TABLE myschema.products (

        product_no integer,

        name text,

        price numeric CHECK (price > 0),

    );

 

When       grants permissions to all tables in the schema, it is necessary to split the permissions into various table operations, such as
 

    ALTER DEFAULT PRIVILEGES IN SCHEMA myschema

    GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO public;

 

When       grants permissions to all Sequence sequence objects in mode, the permissions need to be split into various Sequence operations, such as
 

    ALTER DEFAULT PRIVILEGES IN SCHEMA myschema

    GRANT SELECT, UPDATE, USAGE ON SEQUENCES TO public;

 

When granting permissions to all functions in mode,       only considers execution permissions, such as
 

    ALTER DEFAULT PRIVILEGES IN SCHEMA myschema

    GRANT EXECUTE ON FUNCTIONS TO public;

 

As you can see, it is extremely inconvenient to create objects for myschema mode in public mode. Next we'll show you another way to use the role object, log in directly to the myschema object, and then create the required objects directly in myschema mode.
     

    CREATE ROLE myschema LOGIN PASSWORD '123456'; -- The role object associated with the pattern is created. 

    CREATE SCHEMA myschema AUTHORIZATION myschema; -- To associate the pattern to the specified role, the pattern name and the role name may not be equal. 

 

      under Linux Shell, login to the database MyTest as myschema, login to the database successfully after the password is entered correctly.
 

    /> psql -d MyTest -U myschema

    Password:

    MyTest=> CREATE TABLE test(i integer);

    CREATE TABLE

    MyTest=> \d   -- View the mode, and the mode has permissions to see tables Information list. 

              List of relations

     Schema     |   Name   | Type  |  Owner

    ------------+---------+------+----------

     myschema |   test     | table  | myschema

    (1 rows)

    

      4. Delete mode:
 

    DROP SCHEMA myschema;

 

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

    DROP SCHEMA myschema CASCADE;

 

       
      5. Mode search path:

      we can use the full name of a database object to locate the object, but doing so is often tedious, requiring us to type owner_name.object_name every time. The pattern search path is provided in PostgreSQL, which is somewhat similar to the $PATH environment variable in Linux. When we execute an Shell command, we can only execute it directly by the command name if the command is in the directory list of $PATH, otherwise we need to enter its full path name. PostgreSQL also determines which table a table is by looking for a search path, which is a list of patterns to look for. 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 matching table's name exists in another schema in the database.
The first mode of       in the search path is called the current mode. In addition to being the first schema to search, it is the schema to which the new table belongs when CREATE TABLE does not declare the schema name. To display the current search path, use the following command:
 

    MyTest=> SHOW search_path;

      search_path

    ----------------

     "$user",public

    (1 row)

 

      can add a new mode to the search path, such as
 

    SET search_path TO myschema,public;

 

      sets the mode specified for the search path, e.g.
 

    SET search_path TO myschema; -- The current search path will contain only myschema A pattern.