Managing Schemas

Context

Schemas allow multiple users to use the same database without interference. In this way, database objects can be organized into logical groups that are easy to manage, and third-party applications can be added to corresponding schemas without causing conflicts. Schema management involves creating a schema, using a schema, deleting a schema, setting a search path for a schema, and setting schema permissions.

Precautions

• GaussDB contains one or more named databases. Users and user groups are shared within the database, but their data is not shared. Any user who has connected to a server can access only the database specified in the connection request.
• A database can have one or more schemas, and a schema can contain tables and other data objects, such as data types, functions, and operators. One object name can be used in different schemas. For example, both schema1 and schema2 can have a table named mytable.
• Different from databases, schemas are not isolated. You can access the objects in a schema of the connected database if you have schema permissions. To manage schema permissions, you need to have knowledge about database permissions.
• You cannot create a schema prefixed with PG_. Such schemas are reserved for the database system.
• Each time a new user is created, the system creates a schema with the same name for the new user in the current database. In other databases, such a schema needs to be manually created.
• To reference a table that is not modified with a schema name, the system uses search_path to find the schema that the table belongs to. pg_temp and pg_catalog are always the first two schemas to be searched no matter whether or how they are specified in search_path. search_path is a schema name list, and the first table detected in it is the target table. If no target table is found, an error will be reported. (If a table exists but the schema it belongs to is not listed in search_path, the search fails as well.) The first schema in search_path is called "current schema." This schema is the first one to be searched. If no schema name is declared, newly created database objects are saved in this schema by default.
• Each database has a pg_catalog schema, which contains system catalogs and all built-in data types, functions, and operators. pg_catalog is a part of the search path and has the second highest search priority. It is searched after the schema of temporary tables and before other schemas specified in search_path. This search order ensures that database built-in objects can be found. If you need to create a user-defined object with the same name as a built-in object, you can perform operations in the schema to which the object belongs to avoid name conflicts.

Creating a Schema

Using a Schema

If you want to create or access an object in a specified schema, the object name must contain the schema name. To be specific, the name consists of a schema name and an object name, which are separated by a dot (.).

• Create the mytable table in myschema.

  1 2	gaussdb=#CREATE TABLE myschema.mytable(id int, name varchar(20)); CREATE TABLE

• Query all data of the mytable table in myschema.

  1 2 3 4

  gaussdb=#SELECT * FROM myschema.mytable; id | name ----+------ (0 rows)

Schema Search Path

You can set search_path to define the schema search order of objects in a database. The first schema in the search path is called the current default schema. If no schema is explicitly specified when an object is created, the object is automatically created in the default schema.

• View the search path.

  1 2 3 4 5

  gaussdb=#SHOW search_path; search_path ---------------- "$user",public (1 row)

• Set the search path to myschema, public to configure the schema search order in the database. The system searches for objects in the myschema schema first. If no object is found, the system continues to search for objects in the public schema.

  1 2	gaussdb=#SET search_path TO myschema, public; SET

Schema Permission Control

By default, a user can only access database objects in their own schema. To access objects in other schemas, the target schema owner must grant the corresponding USAGE permission.

By granting the CREATE permission for a schema to a user, the user can create objects in this schema. By default, all roles have the USAGE permission in the public schema, but common users do not have the CREATE permission in the public schema. If a common user has the permission to connect to a specified database and create objects in the public schema, security risks may occur. If the common user has the CREATE permission on the public schema, you are advised to run the following statement to revoke the permission:

• Revoke PUBLIC's permission to create objects in the public schema. In the following statement, the first public indicates the schema and the second PUBLIC indicates all predefined user roles:

  1 2	gaussdb=#REVOKE CREATE ON SCHEMA public FROM PUBLIC; REVOKE

• Run the following command to view the current schema:

  1 2 3 4 5

  gaussdb=#SELECT current_schema(); current_schema ---------------- myschema (1 row)

• Create user jack and grant the usage permission for myschema to the user.

  1 2 3 4

  gaussdb=#CREATE USER jack IDENTIFIED BY '********'; CREATE ROLE gaussdb=#GRANT USAGE ON SCHEMA myschema TO jack; GRANT

• Revoke the usage permission for myschema from jack.

  1 2	gaussdb=#REVOKE USAGE ON SCHEMA myschema FROM jack; REVOKE

Dropping a Schema

1
2

gaussdb=#DROP USER jack;
DROP ROLE