PostgreSQL Mode Introduction

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 Joe user in the same cluster, the system can be configured to allow only Joe to access one of 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,schema1 and MySchema can contain a table named mytable . 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.

1. Create a pattern

To create a pattern, use the CREATE Schema command. Give the pattern name of your choice. Like what:

CREATE SCHEMA MySchema;

To create or access an object in the schema, write a decorated name that contains the schema name and the table name, separated by a period:

Schema . Table

This method is available wherever the table name is required, including table modification commands and data access commands discussed in the following sections. For simplicity, we will only discuss the table, which applies to all other named object types, such as data types and functions.

In fact, the more general syntax

Database . Schema . Table

can also be used, but currently it is only intended to be compatible with SQL standard forms . If you write a database name, it must have the same name as the database you are currently connected to.

To create a table in the new schema, use the

CREATE TABLE myschema.mytable (...);

If a pattern is empty (all objects inside it have been deleted), then the command to delete a pattern is as follows:

DROP SCHEMA MySchema;

To delete a pattern and all of its contained objects, you can use:

DROP SCHEMA MySchema CASCADE;

Usually you want to create a pattern that someone else owns (because this is a way to limit the activity of the user in well-defined patterns). Its syntax is as follows:

SchemaName username;

You can even omit the schema name, at which point the schema will have the same name as the user name.

Schema names that begin with Pg_ are reserved for use by the system, and users cannot create such names.

2. Public mode

In the previous section, we created the table without declaring any schema names. By default, such tables (and other objects) are automatically placed in a pattern called "public". Each new database contains one such pattern. Therefore, the following command is equivalent:

CREATE TABLE Products (...);

And:

CREATE TABLE public.products (...);

3. Mode search Path

The full name is very laborious to write, and we had better not write a specific schema name directly in the application. As a result, tables are usually referenced with an unmodified name , with only the name of the table. The system determines which table is a table by finding a search path , which is a list of schema names that need to be looked up. The first table found in the search path will be used. If the table is not found in the search path, an error is reported (even if the table exists in other schemas in the database).

The first pattern in the search path is called "current mode". In addition to being the first mode of the search, it is the default location of the new table when the create table does not have a schema name declared.

To display the current search path, use the following command:

SHOW Search_path;

In the default settings, the following things are returned:

Search_path--------------"$user", public

The first element declares a pattern that searches for the same name as the current user. Because no such pattern exists, this record is ignored. The second element points to the public pattern we've seen.

The first pattern that exists in the search path is the default location for creating new objects. This is why the default object will be created in public mode. If the object is referenced in another environment and there is no schema decoration, the system iterates through the search path until a matching object is found. Therefore, in the default configuration, any unmodified access can only refer to the public mode.

To set a search path for a pattern, you can use (omit $user because it is not needed immediately)

SET Search_path to Myschema,public;

Then we can access the table without using schema adornments:

DROP TABLE mytable;

Again, because MySchema is the first element in the path, the new object is created here by default.

We can also write:

SET Search_path to MySchema;

Then we can no longer access the public mode if we don't explicitly modify it. There is nothing special about the public mode, except that it exists by default. We can also delete it.

The search path is exactly the same as the data type name, function name, operator name, and table name. Data types and function names can be decorated like table names. If you need to write a pattern-modified operator in an expression, you must write:

OPERATOR (schema. operator )

This is to avoid grammatical ambiguity. Here is an example:

SELECT 3 OPERATOR (pg_catalog.+) 4;

In practice we usually rely on search paths to look for operators, so we don't have to write such ugly things.

4. Modes and Permissions

By default, users cannot access objects that are not owned by them in the schema. In order for them to be accessible, the owner of the pattern needs to give them USAGE privileges in the schema. In order for the user to use the object in the schema, we may need to give additional permissions appropriate to that object.

Users can also create objects in other people's schemas. To allow this, you need to be given the CREATE permission on that schema. Note that by default everyone has CREATE and USAGE permissions in public mode. This allows all users who can connect to the specified database to create objects here. If you do not intend to do so, you can revoke this permission:

REVOKE CREATE on the SCHEMA public from public;

The first "public" is a pattern, and the second "public" means "all users". In the first sentence it is an identifier, and the second sentence is a keyword, so there is a different case.

5. system table Mode

In addition to public and user-created schemas, each database contains a Pg_catalog pattern that contains system tables and all built-in data types, functions, and operators. Pg_catalog is always a part of the search path. If it does not appear explicitly in the path, it is implicitly searched before all paths. This ensures that the built-in name can always be searched. However, you can explicitly put pg_catalog behind the search path if you want to overwrite the built-in name with a user-defined name.

Since the system table name starts with Pg_ , it is best to avoid using such names to ensure that you will not conflict with the new version in the future: those versions may define tables with the same name as your table (in the default search path, a non-decorated reference to your table will resolve to the system table). The system table will continue to follow the tradition that begins with Pg_ , so as long as your table does not start with Pg_ , it will not conflict with the unmodified user table name.

6. How to use

Patterns can organize data in a variety of ways. Here are some recommended patterns that are also easily supported in the default configuration:

• If no schema is created, all users implicitly access the public mode. This simulates the situation when there is no pattern. This setting is recommended primarily for situations where only one user or only a few trusted users are in the database. Such a setting also allows us to smoothly transition from a modeless environment.

• You can create a pattern for each user, with the same name as the user. Remember that the default search path starts with the $user and resolves to the user name. Therefore, if each user has a separate mode, they will access their own mode by default.

  If you use this setting, you may also want to revoke access to the public mode (or delete it) so that users are really restricted to their own patterns.

• To install shared apps (tables used by everyone, extra functions provided by third parties, etc.), we can put them into separate schemas. Just remember to give the right permissions to the users who need to access them. Users can then use these extra objects by decorating them with a pattern name, or they can put additional patterns into their search paths.

7. Portability

In the SQL standard, the notion that objects in the same schema are owned by different users does not exist. Also, some implementations do not allow you to create patterns with different names for their owners. In fact, the concept of a pattern is almost the same as the user in a database system that only implements the basic schema supported by the standard. Therefore, many users consider decorating the names so that they are really made by username. TableName composition. If you create a schema for each user, this is actually the behavior of PostgreSQL.

Similarly, there is no concept of public mode in the SQL standard. In order to maximize compliance, you should not use the public mode (perhaps even should be deleted).

Of course, some database systems may not have a schema at all, or they can provide the functionality of a pattern by allowing cross-database access. If you need to work on these systems, then for maximum portability, you should not use patterns at all.

PostgreSQL Mode Introduction