Suggestions on GaussDB Security Configuration

Maximum Number of Connections

If the number of GaussDB connections is too large, excessive server resources are consumed and the operation response becomes slow. You can optimize the following parameters. For details, see Connection Settings.

• max_connections: maximum number of concurrent connections to the database. This parameter affects the concurrency capability of the cluster.
• max_inner_tool_connections: maximum number of concurrent connections of tools which are allowed to connect to the database. This parameter affects the concurrent connection capability of GaussDB.
• sysadmin_reserved_connections: minimum number of connections reserved for the administrator. You are advised not to set this parameter to a large value. This parameter is used together with max_connections. The maximum number of connections of the administrator is equal to the value of max_connections + the value of sysadmin_reserved_connections.

  This parameter is a POSTMASTER parameter. Set it based on the instructions provided in Setting Parameters.

Security Authentication

To ensure user experience and prevent accounts from being cracked, you can configure the following parameters to set the maximum number of login retries and the automatic unlocking time:

• failed_login_attempts: maximum number of failed login attempts.
• password_lock_time: number of days after which a locked account is automatically unlocked.

  Once detecting that an account is stolen or the account is used to access the database without being authorized, administrators can manually lock the account. Administrators can manually unlock the account if the account becomes normal again.

  For example, run the following commands to manually lock and unlock the joe user:

  • Manually lock the account.

    1 2	gaussdb=# ALTER USER joe ACCOUNT LOCK; ALTER ROLE

  • Manually unlock the account.

    1 2	gaussdb=# ALTER USER joe ACCOUNT UNLOCK; ALTER ROLE

User Password Security

GaussDB enhances user account security in the following aspects:

• User passwords are stored in the system catalog pg_authid. To prevent password leakage, GaussDB encrypts user passwords before storing them. The encryption algorithm is determined by the configuration parameter password_encryption_type.
• All passwords in GaussDB must have a validity period. You can configure the password_effect_time parameter to set a validity period for each database user password, and configure password_notify_time to remind you to change a password.

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.

Database Audit

• GaussDB can record operations you perform on your DB instances. However, only operations supported by Cloud Trace Service (CTS) can be recorded. View the supported operations before performing operations. For details, see Key Operations Supported by CTS.
• Ensure that the audit function for adding, deleting, and modifying database objects is enabled. For details, see Database Audit.
• To view audit logs in a visualized manner, enable Upload Audit Logs to LTS. For details, see Uploading Audit Logs to LTS.

WAL Archiving

Write Ahead Log (WAL) is also called Xlog. The parameter wal_level specifies the level of information to be written into a WAL. To enable read-only queries on a standby node, you need to set the wal_level parameter to hot_standby on the primary node and hot_standby to on on the standby node. In a distributed environment, hot_standby cannot be set to off and wal_level cannot be set to archive or minimal. Otherwise, a database cannot be started. You are advised to use the default value (hot_standby) of wal_level.

Backup Management

GaussDB lets you back up and restore instances in unencrypted form to ensure data reliability. To prevent data loss caused by misoperations or service exceptions, you can:

• Configure automated backups and create manual backups. For details, see Working with Backups. When you create a GaussDB instance, the instance-level automated backup policy is enabled by default. After your instance is created, you can modify the automated backup policy as needed.
• Configure an automated backup policy to periodically back up databases. For details, see Configuring an Automated Backup Policy.
• Export backup information.