Create and manage schemas
Background Information
Schema is also called mode. By managing the schema, multiple users are allowed to use the same database without interfering with each other, database objects can be organized into easy-to-manage logical groups, and at the same time, it is convenient to add third-party applications to the corresponding schema without causing conflicts. Schema management includes: creating a schema, using a schema, deleting a schema, setting a search path for a schema, and controlling permission for a schema.
Precautions
openGauss contains one or more named databases. Users and user groups are shared within the scope of openGauss, but their data is not shared. Any user connected to the server can only access the database specified in the connection request.
A database can contain one or more named schemas, which in turn contain tables and other database objects, including data types, functions, operators, and so on. The same object name can be used in different schemas without causing conflicts. For example, both schema1 and schema2 can contain a table called mytable.
Unlike databases, schemas are not strictly separated. Users can access objects in the schema of the connected database according to their permissions on the schema. To manage schema rights, you first need to understand the rights control of the database.
You cannot create a schema name prefixed with PG_, which is reserved for the database system.
Every time a new user is created, the system will create a Schema with the same name for the new user in the currently logged-in database. For other databases, if a schema with the same name is required, the user needs to create it manually.
When a table is referenced by an unmodified table name (the name only contains the table name, without the "schema name"), the system will use the search_path (search path) to determine which schema the table belongs to. pg_temp and pg_catalog are always the first two characters in the search path order, regardless of whether they appear in search_path or appear anywhere in search_path. search_path (search path) is a list of schema names, the first table found in it is the target table, and an error will be reported if it is not found. (Even if a table exists, if its schema is not in the search_path, the search will still fail.) The first schema in the search path is called the "current schema". It is the first schema queried when searching, and when no schema name is declared, newly created database objects will be stored under this schema by default.
Every database contains a pg_catalog schema, which contains system tables and all built-in data types, functions, and operators. pg_catalog is part of the search path, always behind the schema to which the temporary table belongs, and in front of all schemas in the search_path, that is, it has the second search priority. This ensures that database built-in objects can be searched. If the user needs to use a custom object with the same name as the system's built-in object, he can bring his own mode when operating the custom object.
Steps
Create management user and permission schema
Execute the following command to create a schema.
CREATE SCHEMA myschema;
When the result is displayed as the following information, it means that a schema named myschema has been successfully created.
CREATE SCHEMA
If an object needs to be created or accessed in a schema, its full object name consists of the schema name and the specific object name. The middle is separated by the symbol ".". For example: myschema.table.
Execute the following command to specify the owner when creating the schema.
CREATE SCHEMA myschema AUTHORIZATION omm;
When the result displays the following information, it means that a schema named myschema belonging to the omm user has been successfully created.
CREATE SCHEMA
use schema
To create an object under a specific schema or access an object under a specific schema, you need to use a schema-modified object name. The name includes the schema name and the object name, separated by ".".
Run the following command to create the mytable table under myschema.
openGauss=# CREATE TABLE myschema.mytable(id int, name varchar(20));
CREATE TABLE
If you specify the location of an object in the database, you need to use the schema-qualified object name.
Execute the following command to query all the data in the mytable table under myschema.
openGauss=# SELECT * FROM myschema.mytable;
id | name
----+------
(0 rows)
schema search path
You can set the search_path configuration parameter to specify the order in which to find available schemas for objects. The first schema listed in the search path becomes the default schema. If you do not specify a schema when creating an object, it will be created in the default schema.
Execute the following command to check the search path.
openGauss=# SHOW SEARCH_PATH;
search_path
"$user",public
(1 row)
Execute the following command to set the search path to myschema and public, and first search for myschema.
openGauss=# SET SEARCH_PATH TO myschema, public;
SET
Schema permission control
By default, users can only access database objects in their own schema. If you need to access objects of other schemas, the owner of the schema should grant him the usage permission on the schema.
By granting the CREATE permission of the mode to a user, the authorized user can create objects in this mode. Note that by default, all roles have USAGE permission in public mode, but ordinary users do not have CREATE permission in public mode. It is not safe for ordinary users to be able to connect to a specified database and create objects in its public mode. If ordinary users have the CREATE authority in public mode, it is recommended to revoke the authority through the following statement.
Revoke PUBLIC's permission to create objects in public mode. In the following statement, the first "public" is the mode, and the second "PUBLIC" refers to all roles.
openGauss=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
Use the following command to view the existing schema.
openGauss=# SELECT current_schema();
current_schema
myschema
(1 row)
Run the following commands to create user jack and assign the usage permission of myschema to user jack.
openGauss=# CREATE USER jack IDENTIFIED BY 'xxxxxxxxxx';
CREATE ROLE
openGauss=# GRANT USAGE ON schema myschema TO jack;
GRANT
Take back the usage permission of user jack for myschema.
openGauss=# REVOKE USAGE ON schema myschema FROM jack;
REVOKE
delete schema
When the schema is empty, that is, there is no database object under the schema, use the DROP SCHEMA command to delete it. For example to delete an empty schema named nullschema.
openGauss=# DROP SCHEMA IF EXISTS nullschema;
DROP SCHEMA
When the schema is not empty, if you want to delete a schema and all the objects it contains, you need to use the CASCADE keyword. For example, delete myschema and all objects under the schema.
openGauss=# DROP SCHEMA myschema CASCADE;
DROP SCHEMA
Run the following command to delete user jack.
openGauss=# DROP USER jack;
DROP ROLE
Top comments (0)