GaussDB Database Review

Overview

GaussDB 100 is an enterprise-level relational database engine developed by Huawei. It features high performance, high availability, high scalability, and easy O&M. Some traits of GaussDB are listed below:

• GaussDB supports SQL standards.
• GaussDB is relational database.
• GaussDB is not an artificial intelligent – It’s reverse engineered from “MSSQL & PostgreSQL.

Default Configurations

1. By default Gaussdb installation path set to/opt/Gaussdb/data
2. You can easily find the Gaussdb configuration path using “env” in the bash shell and look for “GSDB_HOME” variable
3. An example of customised Gaussdb configuration path looks like this: GSDB_HOME=/opt/zenith/appdefault Gaussdb configuration file which can control how Gaussdb configuration is setup /opt/gaussdb/data/cfg/zengine.ini
4. Gaussdb users/whitelist can be set in the following configuration file such as who and from which source a user can access the database in /opt/gaussdb/data/cfg/zhba.conf
5. Gaussdb Log files are located in the following path $GSDB_DATA/log/longsql/zengine.lsql

Default accounts to Guassdb:
SYS # Highest privilege account
SYSDB # second highest privilege account.
Public # just a read based user account.
Default Password: “database_123”

Connecting to GaussDB

There are two ways to connect Gaussdb via Python script or zsql binary which sits in the Gaussdb

• IP: LOCALHOST
• Default port 1161 # This is often changed to 32080

You first need to SSH to the server then:

• Switch to the {dbuser} the reason is the /bin/zsql is under the dbuser only. Of course you can change that but best to follow the customer setup as opposed to export the binary to your own environment
• su dbuser # switch user
  • Zsql pentest/Password123@127.0.0.1:32080

Security Build Reviews

OS Level

• Remote user should not have permission from Database OS account. An example of “dbuser” should not be able to SSH to the Gaussdb OS. To check:

grep -P “^ dbuser :.*?:/sbin/nologin$” /etc/passwd

The above should return an empty result

You can also check /etc/passwd and check if the user has “/bin/bash”

Home Directory Restriction

The GSDB_HOME home directory permission should be restricted. To check:

Stat -c ‘%a’ ${GSDB_HOME}
Stat -c ‘%a’ f${GSDB_HOME}/bin
Stat -c ‘%a’ ${GSDB_DATA}/data

The above should return a result of 700.

Stat -c “%a” ${GSDB_DATA}/cfg/zengine.ini

The above should return a result of 600.

Stat -c “%a” ${GSDB_HOME}/cacert.pem

The above should return a result of 400.

Listening Address

Gaussdb should not listen on all network adapters only on local IP address. The security concern with listening on all interfaces it may allow connection from all address. To check, review the zengine.ini configuration file and check local IP address if you see 0.0.0.0 or :: which is IPv6 this will be a bad security practice. Use the command below:

SELECT NAME, VALUE FROM DV_PARAMETERS WHERE NAME = 'LSNR_ADDR' AND VALUE IN ('0.0.0.0', '::');

The query result should be empty.

Change the Default Listening Port

GaussDB default port listens on 1611. It is recommended to change the default port to any other except 1611.

To check the default port, check the value of the ‘LSNR_PORT’ in the zengine.ini configuration file. Use the command below:

SELECT NAME, VALUE FROM DV_PARAMETERS WHERE NAME = 'LSNR_PORT' AND VALUE = '1611';

The query result should be empty.

User Whitelisting

Users with whitelist and user client IP address to allow database connection. When a user authenticate zhba.conf file is checked if the user/user client IP is in whitelist. e.g “host superuser 127.0.0.1” allow superuser to connect 127.0.0.1 to connect to the Gaussdb. 

To check, review  the zhba.conf file and look for the entry “hostssl user x.x.x.x” or “host user x.x.x.x”. Note: “hostssl” indicates SSL connection, while “host” indicates cleartext connection.

To check with a command, use:

SELECT * FROM DV_HBA

The result should return “hostssl user x.x.x.x” or “host user x.x.x.x”.

IP Whitelisting

The IP address whitelist is configured setting the TCP_INVITED_NODES parameter. Whitelist allows for IPv4 and IPv6 clients to access the Gaussdb. You can also check and set Blocklist IPs by setting TCP_EXCLUDE_NODES. To check:

SELECT NAME, VALUE FROM DV_PARAMETERS WHERE NAME = 'TCP_INVITED_NODES';

The query result should have the value “TCP_INVITED_NODES” set with a list of IP values.

Ensure Secure Socket Layer (SSL) is Enabled

In order to ensure the Gaussdb server client encrypted ensure SSL in Guassdb enabled. By default is uses TLSv1.2.

The easiest way to check If the SSL enabled is by looking at the HAVE_SSL parameter, using a SQL Query:

SHOW PARAMETER HAVE_SSL;

Alternatively, you can check the DV_PARAMETERS and look for SSL_CA, SSL_CERT, SSL_KEY and if they have been set or not, using the below:

SELECT * FROM DV_PARAMTERS;

Checking the DBA Role

Check users to see if they have the DBA role. If the DBA role is not in use, ensure that the DBA role permission is removed. 

To check, use the below command, and ensure “user” is replaced with the user name you want to check if the user is DBA:

SELECT GRANTEE, GRANTED_ROLE FROM ADM_ROLE_PRIVS WHERE GRANTEE = ‘user’ AND GRANTEE != ‘SYS’ AND GRANTED_ROLE = ‘DBA’;

To instead check all users in GaussDB, you can use the following query:

 SELECT USERNAME FROM DB_USERS;

CREATE_USER Permission

​​​​​​​A user with CREATE_USER permission can create users. If permission not required remove the permission. Check with the following query:

SELECT USERNAME, PRIVILEGE FROM DB_USER_SYS_PRIVS WHERE USERNAME = ‘user’ AND PRIVILEGE = ‘CREATE USER’;

The above query result should be empty.

ALTER USER Permission

A user with ALTER USER Permission can change edit other users. If permission not required remove the permission, and investigate users with this permission enabled (note that the SYS user is excluded from this). Use the below query:

SELECT USERNAME, PRIVILEGE FROM DB_USER_SYS_PRIVS WHERE USERNAME = ‘user’ AND PRIVILEGE = ‘ALTER USER’;

The above query result should be empty. If there are users with this permission, investigate further.

CREATE DATABASE Permission

A user with CREATE DATABASE rights can create new databases. Use the below query to check:

SELECT USERNAME, PRIVILEGE FROM DB_USER_SYS_PRIVS WHERE USERNAME = ‘user’ AND PRIVILEGE = ‘CREATE DATABASE’;

The above query result should be empty. If there are users with this permission, investigate further (note that the SYS user is excluded from this).

GRANT ANY ROLE Permission

Users with GRANT ANY ROLE permission can grant any role including DBA role. Use the below query to check:

SELECT USERNAME, PRIVILEGE FROM DB_USER_SYS_PRIVS WHERE USERNAME = 'user' AND PRIVILEGE = ' GRANT ANY ROLE’;

The above query result should be empty. If there are users with this permission, investigate further (note that the SYS user is excluded from this).

User Password Security Policies

Check for number of days before the password can not be used by setting the PASSWORD_REUSE_TIME. This is means user must change password after 30 days. This setting “mitigates” password reuse. Use the below query to check:

SELECT RESOURCE_NAME, THRESHOLD FROM ADM_PROFILES WHERE RESOURCE_NAME='PASSWORD_REUSE_TIME';

By default, this has been set to 60 days. Recommendation is 30 days.

FAILURE_LOGIN_ATTEMPTS

The FAILED_LOGIN_ATTEMPTS parameter checks maximum number of login attempts before the account is locked. Use the below query to check:

SELECT RESOURCE_NAME, THRESHOLD FROM ADM_PROFILES WHERE RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS';

The default is 10. Recommendation should be 3 considering this is a sensitive database.

PASSWORD_LOCK_TIME

The PASSWORD_LOCK_TIME parameter specifies number of days an account will be blocked.

SELECT RESOURCE_NAME, THRESHOLD FROM ADM_PROFILE WHERE RESOURCE_NAME=’PASSWORD_LOCK_TIME’;

Recommendation should be 1-day lockout time.

PASSWORD_LIFE_TIME

The PASSWORD_LIFE_TIME parameter specifies the number of days the same password can be used.

SELECT RESOURCE_NAME, THRESHOLD FROM ADM_PROFILES WHERE RESOURCE_NAME='PASSWORD_LIFE_TIME';

The default is set to 180. Recommendation is to set it 60 days before the password expire.

PASSWORD_GRACE_TIME

The PASSWORD_GRACE_TIME parameter specifies grace period between the password expiration warning password expire.

SELECT RESOURCE_NAME, THRESHOLD FROM ADM_PROFILES WHERE RESOURCE_NAME='PASSWORD_GRACE_TIME';

The recommendation is set to 7 days.

Check Database Auditing (Audit Level)

Certain operations in DB and DB users should be audited.

The AUDIT_LEVEL parameter specify the database audit is enabled or disabled.

SELECT NAME, VALUE, FROM DV_PARAMETERS WHERE NAME = ‘AUDIT_LEVEL’;

The default audit level audits DML and DCL audited. Recommended AUDIT_LEVEL is set to value 3. In order to audit every user/database operations set the audit level to 15 which includes DDL,DCL,DML,PL operations.

Check Database Auditing (Audit File Path)

The LOG_HOME parameter specifies the size of the audit file. Recommendation is 10MB at minimum however this depends on how busy is the DB.

SELECT NAME, VALE FROM DV_PARAMETERS WHERE NAME = ‘LOG_HOME’;

The result should be 10MB at minimum.

Check Database Auditing (Log File Permission)

The _LOG_FILE_PERMISSIONS specifies the log file permission.

SELECT NAME,VALUE FROM DV_PARAMETERS WHERE NAME = ' _LOG_FILE_PERMISSIONS ';

The recommended value of the permission should be 600. See above on permissions with stat binary.

Check Database Auditing (Server Log Level)

The _LOG_LEVEL parameter specifies the run and debug logs written into the server.

SELECT NAME,VALUE FROM DV_PARAMETERS WHERE NAME = '_LOG_LEVEL ';

The default value is 7 which logs RUN ERROR(1) RUN WARNING (2) RUN INFORMATION (4) 1+2+4 = 7