Help Center/ GaussDB/ Best Practices/ Permissions Management
Updated on 2023-11-10 GMT+08:00
View PDF
Share

Permissions Management

  • A VPC provides an isolated virtual network for GaussDB instances. You can configure and manage the network as required. A subnet provides dedicated network resources that are logically isolated from other networks for security. If you need to assign different permissions to different employees in your enterprise to access your DB instance resources, IAM is a good choice. For details, see Permissions Management.
  • To ensure database security and reliability, configure security groups before using a DB instance. For details, see Configuring Security Group Rules.
  • Run the following SQL statement to check whether the PUBLIC role has the CREATE permission in public schema. If so, any user can create and modify tables or database objects in public schema.

    SELECT CAST(has_schema_privilege('public','public','CREATE') AS TEXT);

    • If TRUE is returned, run the following SQL statement to revoke the permission:

      REVOKE CREATE ON SCHEMA public FROM PUBLIC;

  • All users are attached to the PUBLIC role. If all permissions of an object are granted to the PUBLIC role, any user can inherit all the permissions of the object, which violates the principle of least privilege. For this reason, this role should have the fewest permissions for database security purposes. Run the following SQL statement to check whether all permissions are granted to the PUBLIC role:

    SELECT relname,relacl FROM pg_class WHERE (CAST(relacl AS TEXT) LIKE '%,=arwdDxt/%}' OR CAST(relacl AS TEXT) LIKE '{=arwdDxt/%}') AND (CAST(relacl AS TEXT) LIKE '%,=APmiv/%}' OR CAST(relacl AS TEXT) LIKE '{=APmiv/%}');

    • If the returned value is empty, all permissions have been granted. In this case, run the following SQL statement to revoke the permissions:

      REVOKE ALL ON <OBJECT_NAME> FROM PUBLIC;

  • The pg_authid system catalog in the pg_catalog schema contains information about all roles in a database. To prevent sensitive information from being disclosed or modified, the PUBLIC role is not allowed to have any permission on this system catalog. Run the following SQL statement to check whether permissions on the pg_authid system catalog have been granted:

    SELECT relname,relacl FROM pg_class WHERE relname = 'pg_authid' AND CAST(relacl AS TEXT) LIKE '%,=%}';

    • If the returned value is not empty, the permissions have been granted. In this case, run the following SQL statement to revoke the permissions:

      REVOKE ALL ON pg_authid FROM PUBLIC;

  • Common users are non-administrator users who perform common service operations. Common users should not have management permissions beyond their normal permission scope, such as permissions for creating roles, permissions for creating databases, audit permissions, monitoring permissions, O&M permissions, and security policy permissions. To minimize common user permissions while meeting normal service requirements, unnecessary management permissions of common users should be revoked.
  • The SECURITY DEFINER function is executed with the permissions of the creator. Improper use of SECURITY DEFINER may cause the function executor to perform unauthorized operations with the permissions of the creator. For this reason, ensure that this function is not misused. For security purposes, the PUBLIC role is not allowed to execute functions of the SECURITY DEFINER type. Run the following SQL statement to check whether the PUBLIC role has functions of the SECURITY DEFINER type:

    SELECT a.proname, b.nspname FROM pg_proc a, pg_namespace b where a.pronamespace=b.oid and b.nspname <> 'pg_catalog' and a.prosecdef='t';

    • If the returned value is not empty, run the following SQL statement to check whether a user has the EXECUTE permission:

      SELECT CAST(has_function_privilege('public', 'function_name([arg_type][, ...])', 'EXECUTE') AS TEXT);

      • If TRUE is returned, the user has the permission. In this case, run the following SQL statement to revoke the permission:

        REVOKE EXECUTE ON FUNCTION function_name([arg_type][, ...]) FROM PUBLIC;

  • The SECURITY INVOKER function is executed with the permissions of the invoker. Improper use of SECURITY INVOKER may cause the function creator to perform unauthorized operations with the permissions of the executor. Before invoking a function not created by yourself, check the function content to prevent the function creator from performing unauthorized operations with your permissions.
Parent topic: Security