Updated on 2025-09-22 GMT+08:00

Database Objects

A database object is a collection of logical structures of a database. Common database objects include tables, views, indexes, sequences, stored procedures, packages, and users.

Databases

Databases manage various data objects and are isolated from each other. While creating a database, you can specify a tablespace. If you do not specify it, the object will be saved to the PG_DEFAULT tablespace by default. Objects managed by a database can be distributed to multiple tablespaces.

Tablespaces

In GaussDB, a tablespace is a directory storing physical files of the databases. An instance may contain multiple tablespaces. Files are physically isolated using tablespaces and managed by a file system.

Tables

A table is a set of two-dimensional arrays. It is the basic unit of data organization and consists of rows and columns. A row of data in a database table is called a record, which consists of information of the same type.

  • Tables are classified into ordinary tables and temporary tables.
    • A temporary table only exists during a session and is automatically deleted once the session ends.
    • The name of a temporary table can be the same as that of an ordinary table. If both a temporary table and an ordinary table share the same name, operations such as viewing the table structure and DML operations are performed on the temporary table.
  • Columns

    A column is used to record the value of an attribute in a table. The name defined for each attribute is called a column name. In addition to the column name, the column also contains information such as the data type and the maximum length (precision) of the data type.

  • Rows

    A row is a collection of all columns in a single record in a table. Each row in the table represents a group of related data, and all rows have the same structure.

Views

A view is a virtual table that does not store data. It is essentially a predefined query that allows you to retrieve data by accessing it. The table involved in the SELECT statement is referred to as the base table. Any changes to the data in the base table will also affect the view. Deleting a view does not affect the base table.

  • Advantages
    • Simplicity: Views simplify data understanding and operations. Common queries can be defined as views, eliminating the need to specify all conditions each time.
    • Security: Views restrict user access to only visible data, making other columns in the table invisible to users. This approach enables more precise permission control.
  • Disadvantages
    • Performance issues: Do not use nested views in views because this may deteriorate query performance.
    • Maintenance complexity: Consider whether a view remains functional after the base table structure is changed.

Indexes

An index is a data structure that contains reference pointers to all records in a data table, similar to a book catalog. It is used to enhance the speed and performance of database queries.
  • Advantages
    • The query speed is accelerated and the database I/O cost is reduced.
    • The sorting cost of the database and the grouping and sorting time are reduced.
    • A unique index ensures the uniqueness of data in a table.
  • Disadvantages
    • Indexes require extra storage space.
    • It takes more time to insert and modify data.
  • Index availability and visibility
    • Index availability

      In the partition deletion scenario, if the column index to be rebuilt is not specified, the index is marked as UNUSABLE. In DML operations, indexes are not maintained, and the optimizer ignores the indexes.

    • Index visibility

      Index visibility specifies whether the optimizer ignores the index. If an index is invisible, the optimizer ignores it, but the index still needs maintenance in DML operations. Generally, before deleting an index, you can set the index as invisible to assess its impact on services. If there is no impact, delete the index.

Users and Roles

  • A user is the one who accesses a database. Each user has its own username and password. Users can perform database operations, and their permissions usually come from the roles granted to them.
    • Initial user

      The account automatically generated during database installation is called an initial user. The initial user has the highest permissions (including SYSADMIN, MONADMIN, OPRADMIN, and POLADMIN) to perform all operations. If the initial username is not set during installation, the account will be named after the OS user who installs the database. If the initial user password is not set during installation, the password is empty upon completion. In this case, update the initial user password on the gsql client before performing other operations. If the initial user password is empty, no SQL operations, upgrades, scaling, node replacements, or other actions can be performed except for password update.

      • The initial user's OID is 10, which can be queried from the gs_roles view.
      • The initial user bypasses all permission checks. It is recommended that the initial user be used only for database administration purposes rather than for service applications.
    • SYSADMIN

      By default, an account with the SYSADMIN attribute has the same permissions as the object owner, excluding object permissions in the dbe_perf schema.

      To create a SYSADMIN account, connect to the database as the initial user or a SYSADMIN and use the CREATE USER or ALTER USER statement with the SYSADMIN option.

      -- Specify the user as SYSADMIN during creation.
      gaussdb=# CREATE USER u1 WITH SYSADMIN PASSWORD '********';
      
      -- After creating a user, run the ALTER statement to specify the user as SYSADMIN.
      gaussdb=# CREATE USER u2 PASSWORD '********';
      gaussdb=# ALTER USER u2 SYSADMIN;
      
      -- Drop.
      gaussdb=# DROP USER u1;
      gaussdb=# DROP USER u2;
    • CREATEROLE

      An account with the CREATEROLE attribute has the permissions to create, modify, and delete users or roles, and grant or revoke the permissions of any non-SYSADMIN, built-in roles, permanent users, or OPRADMIN.

      To create a CREATEROLE account when separation of duties is disabled, connect to the database as a SYSADMIN or CREATEROLE. If separation of duties is enabled, connect to the database as a CREATEROLE and use the CREATE USER or ALTER USER statement with the CREATEROLE option.

      -- Specify the user as CREATEROLE during creation.
      gaussdb=# CREATE USER u1 WITH CREATEROLE PASSWORD '********'; 
      
      -- After creating a user, run the ALTER statement to specify the user as CREATEROLE.
      gaussdb=# CREATE USER u2 PASSWORD '********';
      gaussdb=# ALTER USER u2 CREATEROLE;
      
      -- Drop.
      gaussdb=# DROP USER u1;
      gaussdb=# DROP USER u2;
    • AUDITADMIN

      An account with the AUDITADMIN attribute has the permissions to view and delete audit logs.

      To create an AUDITADMIN account when separation of duties is disabled, connect to the database as a SYSADMIN or CREATEROLE. If separation of duties is enabled, connect to the database only as the initial user and use the CREATE USER or ALTER USER statement with the AUDITADMIN option.

      -- Specify the user as AUDITADMIN during creation.
      gaussdb=# CREATE USER u1 WITH CREATEROLE password '********';
      
      -- After creating a user, run the ALTER statement to specify the user as AUDITADMIN.
      gaussdb=# CREATE USER u2 PASSWORD '********';
      gaussdb=# ALTER USER u2 CREATEROLE;
      
      -- Drop.
      gaussdb=# DROP USER u1;
      gaussdb=# DROP USER u2;
    • MONADMIN

      An account with the MONADMIN attribute has the permissions to view views and functions in the dbe_perf schema, and grant or revoke object permissions in the dbe_perf schema.

      To create a MONADMIN account, connect to the database as a SYSADMIN and use the CREATE USER or ALTER USER statement with the MONADMIN option.

      -- Specify the user as MONADMIN during creation.
      gaussdb=# CREATE USER u1 WITH MONADMIN password '********';
      
      -- After creating a user, run the ALTER statement to specify the user as MONADMIN.
      gaussdb=# CREATE USER u2 PASSWORD '********';
      gaussdb=# ALTER USER u2 MONADMIN;
      
      -- Drop.
      gaussdb=# DROP USER u1;
      gaussdb=# DROP USER u2;
    • OPRADMIN

      An account with the OPRADMIN attribute has the permissions to use Roach to perform backup and restoration.

      To create an OPRADMIN account, connect to the database as the initial user and use the CREATE USER or ALTER USER statement with the OPRADMIN option.

      -- Specify the user as OPRADMIN during creation.
      gaussdb=# CREATE USER u1 WITH OPRADMIN password '********';
      
      -- After creating a user, run the ALTER statement to specify the user as OPRADMIN.
      gaussdb=# CREATE USER u2 PASSWORD '********';
      gaussdb=# ALTER USER u2 OPRADMIN;
      
      -- Drop.
      gaussdb=# DROP USER u1;
      gaussdb=# DROP USER u2;
    • POLADMIN

      An account with the POLADMIN attribute has the permissions to create resource labels, masking policies, and unified audit policies.

      To create a POLADMIN account, connect to the database as a SYSADMIN and use the CREATE USER or ALTER USER statement with the POLADMIN option.

      -- Specify the user as POLADMIN during creation.
      gaussdb=# CREATE USER u1 WITH POLADMIN password '********';
      
      -- After creating a user, run the ALTER statement to specify the user as POLADMIN.
      gaussdb=# CREATE USER u2 PASSWORD '********';
      gaussdb=# ALTER USER u2 POLADMIN;
      
      -- Drop.
      gaussdb=# DROP USER u1;
      gaussdb=# DROP USER u2;
    • Common users: These users are usually granted the permissions required for performing specific service operations. You can run the CREATE USER statement to create a common user and specify the password, validity period, and connection restrictions.
      -- Create user u1.
      gaussdb=# CREATE USER u1 PASSWORD '********';
      
      -- Drop.
      gaussdb=# DROP USER u1;
  • A role is a set of permissions used to organize and manage permissions. Once a user is granted a role, the user has all the permissions of the role. Permissions can be inherited between roles as well.
    • Public role: GaussDB provides an implicitly defined public role. All created users and roles belong to the public role by default. You can run the GRANT and REVOKE statements to modify the PUBLIC permission.
    • Built-in roles: GaussDB provides a group of default roles named with the prefix "gs_role_" that grant access to specific high-privilege operations. For example, the gs_role_signal_backend role allows you to call functions to cancel or terminate other sessions, while the gs_role_tablespace role allows you to create tablespaces.
  • Relationship between users and roles: A user's permissions are derived from roles. A user can be regarded as a role with the login permission, and a role can be regarded as a user without the login permission. Roles makes it easier to manage and grant permissions, especially when multiple users require the same permissions. With these mechanisms, GaussDB implements flexible and secure permission management, enabling database administrators to properly allocate and manage user permissions based on service and security requirements.
    -- Create role r1.
    gaussdb=# CREATE ROLE r1 PASSWORD '********';
    
    -- Create user u1.
    gaussdb=# CREATE USER u1 PASSWORD '********';
    
    -- Grant the permissions of role r1 to user u1.
    gaussdb=# GRANT r1 TO u1;
    
    -- Query the information about the preceding role and user.
    gaussdb=# \du r1|u1;
                List of roles
     Role name |  Attributes  | Member of 
    -----------+--------------+-----------
     r1        | Cannot login | {}
     u1        |              | {r1}
    
    -- Drop the user and role.
    gaussdb=# DROP USER u1;
    gaussdb=# DROP ROLE r1;