PostgreSQL mode operation

A PostgreSQL database cluster contains one or more named databases. Users and user groups are shared across the cluster, but other data is not shared. Any client connected to the server can only access the database declared in the connection request.

Note: users in a cluster do not necessarily have permission to access all databases within the cluster. Sharing a user name means that you cannot have duplicate users. Assuming that there are two databases and one user in the same clusterjoe, the system can be configured to allow access only to onejoeof the databases.

A database contains one or more named patterns , and the schema contains the tables. Schemas can also contain other objects, including data types, functions, operators, and so on. The same object name can be used in different modes without causing conflicts; For example,schema1andmyschemaboth can contain amytabletable named. Unlike databases, schemas are not strictly separated: as long as there is permission, a user can access objects in any schema in the database to which he is connected.

We need patterns for a lot of reasons:

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

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

Create a pattern

CREATE SCHEMA schema_name [AUTHORIZATION user_name] [schema_element [...]]
CREATE SCHEMA AUTHORIZATION user_name [schema_element [...]]
CREATE SCHEMA IF NOT EXISTS schema_name [AUTHORIZATION user_name]
CREATE SCHEMA IF NOT EXISTS AUTHORIZATION user_name
example
Create a pattern:

CREATE SCHEMA myschema;
Create a pattern for the user joe. The pattern name is also joe:

CREATE SCHEMA AUTHORIZATION joe;
Create a pattern named test, which is owned by user joe, unless there is already a pattern named test. (It has nothing to do with whether joe has an existing model.)

CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;
Create a schema and create a table in it:

CREATE SCHEMA hollywood
    CREATE TABLE films (title text, release date, awards text [])
    CREATE VIEW winners AS
        SELECT title, release FROM films WHERE awards IS NOT NULL;
Please note that the independent subcommands above do not end with a semicolon

The following command is the equivalent statement to achieve the same result:

CREATE SCHEMA hollywood;
CREATE TABLE hollywood.films (title text, release date, awards text []);
CREATE VIEW hollywood.winners AS
    SELECT title, release FROM hollywood.films WHERE awards IS NOT NULL;
public mode
When creating a new database, PostgreSQL automatically creates the schema. When logging into the database, if there is no special designation, various data objects will be operated in the form of this mode (public), such as:

CREATE TABLE products (...) is equivalent to CREATE TABLE public.products (...)
compatibility
The SQL standard allows a DEFAULT CHARACTER SET clause in CREATE SCHEMA and more subcommands than currently accepted by PostgreSQL.

The SQL standard states that subcommands in CREATE SCHEMA can appear in any order. The current implementation in PostgreSQL cannot handle forward references in all subcommands; sometimes it may be necessary to rearrange the order of subcommands to avoid forward references.

In the SQL standard, the owner of a schema always owns all the objects in it. PostgreSQL allows schemas to contain objects owned by non-schema owners. This can only occur if the owner of the schema has given CREATE permissions to others, or the superuser has chosen to create objects in that schema. The IF NOT EXISTS option is a PostgreSQL extension.

Modify mode
ALTER SCHEMA name RENAME TO new_name
ALTER SCHEMA name OWNER TO new_owner
ALTER SCHEMA changes the definition of a pattern.

To use ALTER SCHEMA, you must be the owner of the schema. To rename a schema, you must have CREATE permission on the database. To modify the owner, you must also be a direct or indirect member of all new roles, and the member must have CREATE permission on this database. (Superuser automatically has full permissions.)

parameter
name
The name of an existing pattern.

new_name
The new name of the pattern. The new name cannot begin with pg_, because these names are reserved for system patterns.

new_owner
The new owner of the pattern.

compatibility
There is no ALTER SCHEMA statement in the SQL standard.

Delete mode
DROP SCHEMA [IF EXISTS] name [, ...] [CASCADE | RESTRICT]
DROP SCHEMA removes the schema from the database.

A pattern can only be deleted by its owner or superuser. Note that the owner can delete the schema (and all objects in the schema) even if he does not own any objects in the schema.

parameter
IF EXISTS
If the specified pattern does not exist, issue a notice instead of throwing an error.

* name *
The name of the pattern.

CASCADE
Automatically delete objects (tables, functions, and so on) contained in the schema.

RESTRICT
If the schema contains any objects, refuse to delete it. This is the default.

example
Remove the schema mystuff and everything it contains from the database:

DROP SCHEMA mystuff CASCADE;
Mode permissions
By default, users do not see objects that are not owned by them in the schema. In order for them to be visible, the owner of the schema needs to grant USAGE permissions on the schema. In order for the user to use the object in the schema, we may need to grant additional permissions as long as it is suitable for the object. PostgreSQL provides different types of permissions based on different objects, such as:

GRANT ALL ON SCHEMA myschema TO public;
The ALL keyword above will include both CREATE and USAGE permissions. If the public mode has the CREATE permission in the myschema mode, then users logged in to the mode can create arbitrary objects in the myschema mode, such as:

CREATE TABLE myschema.products (
        product_no integer,
        name text,
        price numeric CHECK (price> 0),
);
When granting permissions to all tables in the schema, permissions need to be split 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 granting permissions to all functions in the mode, only execute permissions are considered, such as:

ALTER DEFAULT PRIVILEGES IN SCHEMA myschema
    GRANT EXECUTE ON FUNCTIONS TO public;
Revoke mode permissions can do this

REVOKE [CREATE ...] ON SCHEMA public FROM PUBLIC;
It can be seen that it is extremely inconvenient to create various objects for the myschema mode in the public mode in the above manner. Below we will introduce another way, that is, to directly log in to the myschema object through the role object, and then you can directly create various required objects in the myschema mode.

-The role object associated with the pattern has been created.
CREATE ROLE myschema LOGIN PASSWORD '123456';
--Associating the pattern with the specified role, the pattern name and role name can be unequal.
CREATE SCHEMA myschema AUTHORIZATION myschema;
In the Linux Shell, log in to the database MyTest as the role of myschema. After entering the password correctly, you will successfully log in to the database.

 /> psql -d MyTest -U myschema
    Password:
    MyTest => CREATE TABLE test (i integer);
    CREATE TABLE
    MyTest => \ d --View the list of tables in this mode and the permissions that the mode has permission to see.
              List of relations
     Schema | Name | Type | Owner
    ------------ + --------- + ------ + ----------
     myschema | test | table | myschema
    (1 rows)
Pattern search path
When using a database object, you can use its full name to locate the object, but this is often very tedious, you have to type owner_name.object_name every time. PostgreSQL provides a pattern search path, which is somewhat similar to the $ PATH environment variable in Linux. When a shell command is executed, it can be directly executed by command name only if the command is in the directory list of $ PATH, otherwise you need to enter Its full path name. PostgreSQL also determines which table a table is by looking up a search path, which is a list of patterns to look up. The first table found in the search path is 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 another schema in the database.
The first pattern in the search path is called the current pattern. 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 a schema name. To display the current search path, use the following command:

MyTest => SHOW search_path;
      search_path
    ----------------
     "$ user", public
    (1 row)
New patterns can be added to the search path, such as:

SET search_path TO myschema, public;
Set the specified pattern for the search path, such as:

SET search_path TO myschema; --The current search path will only contain a schema of myschema.
PostgreSQL mode operation