DEV Community: wanglei

Administrators

wanglei — Tue, 18 Apr 2023 07:13:05 +0000

Initial Users
The account automatically generated during openGauss installation is called an initial user. An initial user is the system, monitoring, O&M, and security policy administrator who has the highest-level permissions in the system and can perform all operations. This account has the same name as the OS user used for openGauss installation. You need to manually set the password during the installation. After the first login, change the initial user's password in time.

An initial user bypasses all permission checks. You are advised to use an initial user as a database administrator only for database management other than service running.

System Administrators
A system administrator is an account with the SYSADMIN attribute. By default, a database system administrator has the same permissions as object owners but does not have the object permissions in dbe_perf mode.

To create a system administrator, connect to the database as the initial user or a system administrator and run the CREATE USER or ALTER USER statement with SYSADMIN specified.

CREATE USER sysadmin WITH SYSADMIN password "xxxxxxxxx";

ALTER USER joe SYSADMIN;

To run the ALTER USER statement, the user must exist.

Monitor Administrator
A monitor administrator is an account with the MONADMIN attribute and has the permission to view views and functions in the dbe_perf schema. The monitor administrator can also grant or revoke object permissions in the dbe_perf schema.

To create a monitor administrator, connect to the database as a system administrator and run the CREATE USER or ALTER USER statement with MONADMIN specified.

postgres=# CREATE USER monadmin WITH MONADMIN password "xxxxxxxxx";

postgres=# ALTER USER joe MONADMIN;

To run the ALTER USER statement, the user must exist.

O&M Administrator
An O&M administrator is an account with the OPRADMIN attribute and has the permission to use Roach to perform backup and restoration.

To create an O&M administrator, connect to the database as an initial user and run the CREATE USER or ALTER USER statement with OPRADMIN specified.

postgres=# CREATE USER opradmin WITH OPRADMIN password "xxxxxxxxx";

postgres=# ALTER USER joe OPRADMIN;

To run the ALTER USER statement, the user must exist.

Security Policy Administrator
A security policy administrator is an account with the POLADMIN attribute and has the permission to create resource tags, anonymization policies, and unified audit policies.

To create a security policy administrator, connect to the database as an administrator and run the CREATE USER or ALTER USER statement with POLADMIN specified.

postgres=# CREATE USER poladmin WITH POLADMIN password "xxxxxxxxx";

postgres=# ALTER USER joe POLADMIN;

To run the ALTER USER statement, the user must exist.

Default Permission Mechanism

wanglei — Tue, 18 Apr 2023 07:12:49 +0000

A user who creates an object is the owner of this object. By default, Separation of Duties is disabled after database installation. A database system administrator has the same permissions as object owners. After an object is created, only the object owner or system administrator can query, modify, and delete the object, and grant permissions for the object to other users through GRANT by default.

To enable another user to use the object, grant required permissions to the user or the role that contains the user.

openGauss supports the following permissions: SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, CREATE, CONNECT, EXECUTE, USAGE, ALTER, DROP, COMMENT, INDEX, and VACUUM. Permission types are associated with object types. For permission details, see GRANT.

To remove permissions, run REVOKE. Object owners have implicit permissions (such as ALTER, DROP, COMMENT, INDEX, VACUUM, GRANT, and REVOKE) on objects. That is, once becoming the owner of an object, the owner is immediately granted the implicit permissions on the object. Object owners can remove their own common permissions, for example, making tables read-only to themselves or others, except the system administrator.

System catalogs and views are visible to either system administrators or all users. System catalogs and views that require system administrator permissions can be queried only by system administrators. For details, see System Catalogs and System Views.

The database provides the object isolation feature. If this feature is enabled, users can view only the objects (tables, views, columns, and functions) that they have the permission to access. System administrators are not affected by this feature. For details, see ALTER DATABASE.

Replacing Certificates

wanglei — Tue, 18 Apr 2023 07:06:45 +0000

Scenarios
Default security certificates and private keys required for SSL connection are configured in openGauss. You can change them as needed.

Prerequisites
The formal certificates and keys for the server and client have been obtained from the CA.

Precautions
Currently, openGauss supports only the X509v3 certificate in PEM format.

Procedure
Prepare for a certificate and a key.

Conventions for configuration file names on the server:

Certificate name: server.crt
Key name: server.key
Key password and encrypted file: server.key.cipher and server.key.rand
Conventions for configuration file names on the client:

Certificate name: client.crt
Key name: client.key
Key password and encrypted file: client.key.cipher and client.key.rand
Certificate name: cacert.pem
CRL file name: sslcrl-file.crl
Create a compressed package.

Package name: db-cert-replacement.zip

Package format: ZIP

Package file list: server.crt, server.key, server.key.cipher, server.key.rand, client.crt, client.key, client.key.cipher, client.key.rand, cacert.pem If you need to configure the CRL, the list must contain sslcrl-file.crl.

Invoke the certificate replacement interface to replace a certificate.

Upload the prepared package db-cert-replacement.zip to any path of an openGauss user.

For example: /home/xxxx/db-cert-replacement.zip

Run the following command to perform the replacement:

gs_om -t cert --cert-file= /home/xxxx/db-cert-replacement.zip

Restart the openGauss.

gs_om -t stop 
gs_om -t start

NOTE:
Certificates can be rolled back to the version before the replacement. You can run the gs_om -t cert –rollback command to remotely invoke the interface or gs_om -t cert –rollback -L to locally invoke the interface. The certificate will be rolled back to the latest version that was successfully replaced.

Generating Certificates

wanglei — Tue, 18 Apr 2023 07:06:27 +0000

Scenarios
In the test environment, users can use either of the following methods to test digital certificates. In a customer's operating environment, only a digital certificate obtained from a CA can be used.

Prerequisites
The OpenSSL component has been installed in the Linux environment.

Generating an Automatic Authentication Certificate
Establish a CA environment.

-- Suppose that user omm exists, and the CA path is test.
-- Log in to the Linux environment as user root and switch to user omm:
mkdir test
cd /etc/pki/tls
-- Copy the configuration file openssl.cnf to test.
cp openssl.cnf ~/test
cd ~/test
--Establish the CA environment under the test folder.
--Create folder demoCA./demoCA/newcerts./demoCA/private.
mkdir ./demoCA ./demoCA/newcerts ./demoCA/private
chmod 700 ./demoCA/private
--Create the serial file and write it to 01.
echo '01'>./demoCA/serial
-- Create the index.txt file.
touch ./demoCA/index.txt
-- Modify parameters in the openssl.cnf configuration file.
dir  = ./demoCA
default_md      = sha256
--The CA environment has been established.

Generate a root private key.

--Generate a CA private key.
openssl genrsa -aes256 -out demoCA/private/cakey.pem 2048
Generating RSA private key, 2048 bit long modulus
.................+++
..................+++
e is 65537 (0x10001)
--Set the protection password of the root private key to at least four characters, for example, Test@123.
Enter pass phrase for demoCA/private/cakey.pem:
--Enter the private key password Test@123 again.
Verifying - Enter pass phrase for demoCA/private/cakey.pem:

Generate a root certificate request file.

--Generate a CA root certificate application file named careq.pem.
openssl req -config openssl.cnf -new -key demoCA/private/cakey.pem -out demoCA/careq.pem
Enter pass phrase for demoCA/private/cakey.pem:
--Enter the root private key password Test@123.
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----

--Note down the following names and use them when entering information in the generated server certificate and client certificate.
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:shanxi
Locality Name (eg, city) []:xian
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Abc
Organizational Unit Name (eg, section) []:hello
--Common Name can be randomly set.
Common Name (eg, YOUR name) []:world
--The email address is optional.
Email Address []:

Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Generate a self-signed root certificate.

--When generating the root certificate, modify the openssl.cnf file and set basicConstraints to CA:TRUE.
vi openssl.cnf
-- Generate a CA self-signed root certificate.
openssl ca -config openssl.cnf -out demoCA/cacert.pem -keyfile demoCA/private/cakey.pem -selfsign -infiles demoCA/careq.pem
Using configuration from openssl.cnf
Enter pass phrase for demoCA/private/cakey.pem:
--Enter the root private key password Test@123.
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 1 (0x1)
        Validity
            Not Before: Feb 28 02:17:11 2017 GMT
            Not After : Feb 28 02:17:11 2018 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = shanxi
            organizationName          = Abc
            organizationalUnitName    = hello
            commonName                = world
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                F9:91:50:B2:42:8C:A8:D3:41:B0:E4:42:CB:C2:BE:8D:B7:8C:17:1F
            X509v3 Authority Key Identifier: 
                keyid:F9:91:50:B2:42:8C:A8:D3:41:B0:E4:42:CB:C2:BE:8D:B7:8C:17:1F

Certificate is to be certified until Feb 28 02:17:11 2018 GMT (365 days)
Sign the certificate? [y/n]:y


1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated
--A CA root certificate named demoCA/cacert.pem has been issued.

Generate a private key for the server certificate.

--Generate a private key file named server.key.
openssl genrsa -aes256 -out server.key 2048
Generating a 2048 bit RSA private key
.......++++++
..++++++
e is 65537 (0x10001)
Enter pass phrase for server.key:
--The password of the server private key must contain a minimum of four characters, for example, Test@123.
Verifying - Enter pass phrase for server.key:
--Confirm the protection password for the server private key Test@123 again.

Generate a server certificate request file.

--Generate a server certificate request file server.req.
openssl req -config openssl.cnf -new -key server.key -out server.req
Enter pass phrase for server.key:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----

--Set the following information and make sure that it is same as that when CA is created.
Country Name (2 letter code) [AU]:CN
State or Province Name (full name) [Some-State]:shanxi
Locality Name (eg, city) []:xian
Organization Name (eg, company) [Internet Widgits Pty Ltd]:Abc
Organizational Unit Name (eg, section) []:hello
--Common Name can be randomly set.
Common Name (eg, YOUR name) []:world
Email Address []:
--The following information is optional.
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:

Generate a server certificate.


--When generating the server certificate or client certificate, modify the openssl.cnf file and set basicConstraints to CA:FALSE.
vi openssl.cnf
--Change the demoCA/index.txt.attr attribute to no.
vi demoCA/index.txt.attr

--Issue the generated server certificate request file. After it is issued, an official server certificate server.crt is generated.
openssl ca  -config openssl.cnf -in server.req -out server.crt -days 3650 -md sha256
Using configuration from /etc/ssl/openssl.cnf
Enter pass phrase for ./demoCA/private/cakey.pem:
Check that the request matches the signature
Signature ok
Certificate Details:
        Serial Number: 2 (0x2)
        Validity
            Not Before: Feb 27 10:11:12 2017 GMT
            Not After : Feb 25 10:11:12 2027 GMT
        Subject:
            countryName               = CN
            stateOrProvinceName       = shanxi
            organizationName          = Abc
            organizationalUnitName    = hello
            commonName                = world
        X509v3 extensions:
            X509v3 Basic Constraints: 
                CA:FALSE
            Netscape Comment: 
                OpenSSL Generated Certificate
            X509v3 Subject Key Identifier: 
                EB:D9:EE:C0:D2:14:48:AD:EB:BB:AD:B6:29:2C:6C:72:96:5C:38:35
            X509v3 Authority Key Identifier: 
                keyid:84:F6:A1:65:16:1F:28:8A:B7:0D:CB:7E:19:76:2A:8B:F5:2B:5C:6A

Certificate is to be certified until Feb 25 10:11:12 2027 GMT (3650 days)
--Enter y to sign and issue the certificate.
Sign the certificate? [y/n]:y

--Enter y. The certificate singing and issuing is complete.
1 out of 1 certificate requests certified, commit? [y/n]y
Write out database with 1 new entries
Data Base Updated

Disable password protection for the private key.

--Disable the password protection for the server private key.
openssl rsa -in server.key -out server.key
--If the password protection for the server private key is not disabled, you need to use the gs_guc tool to encrypt the password.
gs_guc encrypt -M server -K Test@123 -D ./
--After the password is encrypted using gs_guc, two private key password protection files server.key.cipher and server.key.rand are generated.

Generate the client certificate and private key.

Methods and requirements for generating client certificates and private keys are the same as that for server certificates and private keys.

--Generate a client private key.
openssl genrsa -aes256 -out client.key 2048
--Generate a certificate request file for a client.
openssl req -config openssl.cnf -new -key client.key -out client.req 
--After the generated certificate request file for client is signed and issued, a formal client certificate client.crt is generated.
openssl ca -config openssl.cnf -in client.req -out client.crt -days 3650 -md sha256

Disable password protection for the private key:

--Disable the protection for a client private key password.
openssl rsa -in client.key -out client.key
--If password protection for a client private key is not removed, you need to use the gs_guc tool to encrypt the password.
gs_guc encrypt -M client -K Test@123 -D ./  
After the password is encrypted using gs_guc, two private key password protection files client.key.cipher and client.key.rand are generated.

Convert the client key to the DER format.

openssl pkcs8 -topk8 -outform DER -in client.key -out client.key.pk8 -nocrypt

Generate a CRL.

If the CRL is required, you can generate it by following the following procedure:

--Create a crlnumber file.
echo '00'>./demoCA/crlnumber
--Revoke a server certificate.
openssl ca -config openssl.cnf -revoke server.crt
--Generate the CRL sslcrl-file.crl

openssl ca -config openssl.cnf -gencrl -out sslcrl-file.crl

Checking the Number of Database Connections

wanglei — Tue, 18 Apr 2023 07:02:48 +0000

Background
If the number of connections reaches its upper limit, new connections cannot be created. Therefore, if a user fails to connect a database, the administrator must check whether the number of connections has reached the upper limit. The following are details about database connections:

The maximum number of global connections is specified by the max_connections parameter. Its default value is 5000.
The number of a user's connections is specified by CONNECTION LIMIT connlimit in the CREATE ROLE statement and can be changed using CONNECTION LIMIT connlimit in the ALTER ROLE statement.
The number of a database's connections is specified by the CONNECTION LIMIT connlimit parameter in the CREATE DATABASE statement.
Procedure
Log in as the OS user omm to the primary node of the database.

Run the following command to connect to the database:

gsql -d postgres -p 8000

postgres is the name of the database to be connected, and 8000 is the port number of the database primary node.

If information similar to the following is displayed, the connection succeeds:

gsql ((openGauss 1.0 build 290d125f) compiled at 2020-05-08 02:59:43 commit 2143 last mr 131)
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=#

View the upper limit of the number of global connections.

postgres=# SHOW max_connections;
 max_connections
-----------------
 800
(1 row)

800 is the maximum number of session connections.

View the number of connections that have been used.

For details, see Table 1.

NOTICE:
Except for database and usernames that are enclosed in double quotation marks (") during creation, uppercase letters are not allowed in the database and usernames in the commands in the following table.

Table 1 Viewing the number of session connections

Establishing Secure TCP/IP Connections in SSH Tunnel Mode

wanglei — Tue, 18 Apr 2023 07:00:57 +0000

Background
To ensure secure communication between the database server and its clients, secure SSH tunnels can be established between the database server and clients. SSH is a reliable security protocol dedicated to remote login session and other network services.

Regarding the SSH client, the SSH provides the following two security authentication levels:

Password-based security authentication: Use an account and a password to log in to a remote host. All transmitted data is encrypted. However, the connected server may not be the target server. Another server may pretend to be the real server and perform the man-in-the-middle attack.
Key-based security authentication: A user must create a pair of keys and put the public key on the target server. This mode prevents man-in-the-middle attacks while encrypting all transmitted data. However, the entire login process may last 10s.
Prerequisites
The SSH service and the database must run on the same server.

Procedure
OpenSSH is used as an example to describe how to configure SSH tunnels. The process of configuring key-based security authentication is not described here. OpenSSH provides multiple configurations to adapt to different networks. For more details, see documents related to OpenSSH.

Establish the SSH tunnel from a local host to the database server.

ssh -L 63333:localhost:8000 username@hostIP

NOTE:

The first digit string (63333) of the -L parameter indicates the local port ID of the tunnel and can be randomly selected.
The second digit string (8000) indicates the remote port ID of the tunnel, which is the port ID on the server.
localhost is the IP address of the local host, username is the username on the database server to be connected, and hostIP is the IP address of the database server to be connected.

Establishing Secure TCP/IP Connections in SSL Mode

wanglei — Tue, 18 Apr 2023 06:53:44 +0000

Background
openGauss supports the standard SSL (TLS 1.2). As a highly secure protocol, SSL authenticates bidirectional identification between the server and client using digital signatures and digital certificates to ensure secure data transmission.

Prerequisites
Formal certificates and keys for servers and clients have been obtained from the Certificate Authority (CA). Assume the private key and certificate for the server are server.key and server.crt, the private key and certificate for the client are client.key and client.crt, and the CA root certificate is cacert.pem.

Precautions
When a user remotely accesses the primary node of the database, the SHA-256 authentication method is used.
If internal servers are connected with each other, the trust authentication mode must be used. IP address whitelist authentication is supported.
Procedure
After a database is deployed, openGauss enables the SSL authentication mode by default. The server certificate, key, and root certificates have been configured. You need to set client parameters.

Set digital certificate parameters related to SSL authentication. For details, see Table 1.

Configure client parameters.

The default client certificate, key, root certificate, and key encrypted file have been obtained from the CA authentication center. Assume that the certificate, key, and root certificate are stored in the /home/omm directory.

For bidirectional authentication, set the following parameters:

export PGSSLCERT="/home/omm/client.crt"
export PGSSLKEY="/home/omm/client.key"
export PGSSLMODE="verify-ca"
export PGSSLROOTCERT="/home/omm/cacert.pem"

For unidirectional authentication, set the following parameters:

export PGSSLMODE="verify-ca"
export PGSSLROOTCERT="/home/omm/cacert.pem"

Change the client key permission.

The permission of the client root certificate, key, certificate, and encrypted key file should be 600. Otherwise, the client cannot connect to openGauss through SSL.

chmod 600 client.key
chmod 600 client.crt
chmod 600 client.key.cipher
chmod 600 client.key.rand
chmod 600 cacert.pem

NOTICE: You are advised to use bidirectional authentication for security purposes. The environment variables configured for a client must contain the absolute file paths.

Table 1 Authentication modes

Reference
In the postgresql.conf file on the server, set the related parameters. For details, see Table 2.

Table 2 Server parameters

Configure environment variables related to SSL authentication on the client. For details, see Table 3.

NOTE: The path of environment variables is set to /home/omm as an example. Replace it with the actual path.

Table 3 Client parameters

The following table describes the connection results based on the settings of the server parameters ssl and require_ssl and the client parameter sslmode.

ssl (Server)

sslmode (Client)

require_ssl (Client)

Result

disable

The connection fails, because the server requires SSL but the client has disabled it.

disable

off

The connection is not encrypted.

allow

The connection is encrypted.

allow

off

The connection is not encrypted.

prefer

The connection is encrypted.

prefer

off

The connection is encrypted.

require

The connection is encrypted.

require

off

The connection is encrypted.

verify-ca

The connection is encrypted and the server certificate is verified.

verify-ca

off

The connection is encrypted and the server certificate is verified.

verify-full

The connection is encrypted and the server certificate and host name are verified.

verify-full

off

The connection is encrypted and the server certificate and host name are verified.

off

disable

The connection is not encrypted.

disable

off

The connection is not encrypted.

allow

The connection is not encrypted.

allow

off

The connection is not encrypted.

prefer

The connection is not encrypted.

prefer

off

The connection is not encrypted.

require

The connection fails, because the client requires SSL but the server has disabled it.

require

off

The connection fails, because the client requires SSL but the server has disabled it.

verify-ca

The connection fails, because the client requires SSL but the server has disabled it.

verify-ca

off

The connection fails, because the client requires SSL but the server has disabled it.

verify-full

The connection fails, because the client requires SSL but the server has disabled it.

verify-full

off

The connection fails, because the client requires SSL but the server has disabled it.

A series of encryption and authentication algorithms with different strength are supported for SSL transmission. You can modify ssl_ciphers in postgresql.conf to specify the encryption algorithm used by the database server. Table 4 lists the encryption algorithms supported by the SSL.

Table 4 Encryption algorithm suites

NOTE:

Currently, only the six encryption algorithm suites listed in the preceding table are supported.
The default value of ssl_ciphers is ALL, indicating that all encryption algorithms listed in the table are supported. 为保持前向兼容保留了DHE算法套件，即DHE-RSA-AES128-GCM-SHA256和DHE-RSA-AES256-GCM-SHA384，根据CVE-2002-20001漏洞披露DHE算法存在一定安全风险，非兼容场景不建议使用，可将ssl_ciphers参数配置为仅支持ECDHE类型算法套件。
To specify the preceding cipher suites, set** ssl_ciphers** to the OpenSSL suite names in the preceding table. Use semicolons (;) to separate cipher suites. For example, set ssl_ciphers in postgresql.conf as follows: ssl_ciphers='ECDHE-RSA-AES128-GCM-SHA256;ECDHE-ECDSA-AES128-GCM-SHA256'
SSL authentication increases the time spent for login (creating the SSL environment) and logout processes (clearing the SSL environment), and requires extra time for encrypting the data to be transferred. It affects performance especially in frequent login, logout, and short-time query scenarios.
If the certificate validity period is less than seven days, an alarm is generated in the log when a user logs in to the system.

Configuration File Reference

wanglei — Tue, 18 Apr 2023 06:48:04 +0000

Table 1 Parameter description

Table 2 Authentication modes

Configuring Client Access Authentication

wanglei — Tue, 18 Apr 2023 06:42:38 +0000

Background
If a host needs to connect to a database remotely, you need to add information about the host in configuration file of the database system and perform client access authentication. The configuration file (pg_hba.conf by default) is stored in the data directory of the database. HBA is short for host-based authentication.

The system supports the following three authentication methods, which all require the pg_hba.conf file.

Host-based authentication: A server checks the configuration file based on the IP address, username, and target database of the client to determine whether the user can be authenticated.
Password authentication: A password can be an encrypted password for remote connection or a non-encrypted password for local connection.
SSL encryption: The OpenSSL is used to provide a secure connection between the server and the client.
In the pg_hba.conf file, each record occupies one row and specifies an authentication rule. An empty row or a row started with a number sign (#) is neglected.

Each authentication rule consists of multiple columns separated by spaces and forward slashes (/), or spaces and tab characters. If a field is enclosed with quotation marks ("), it can contain spaces. One record cannot span different rows.

Procedure
Log in as the OS user omm to the primary node of the database.

Configure the client authentication mode and enable the client to connect to the host as user jack. User omm cannot be used for remote connection.

Assume you are to allow the client whose IP address is 10.10.0.30 to access the current host.

gs_guc set -N all -I all -h "host all jack 10.10.0.30/32 sha256"

NOTE:

Before using user jack, connect to the database locally and run the following command in the database to create user jack:

CREATE USER jack PASSWORD 'Test@123';

-N all indicates all hosts in openGauss.
-I all indicates all instances on the host.
-h specifies statements that need to be added in the pg_hba.conf file.
all indicates that a client can connect to any database.
jack indicates the user that accesses the database.
10.10.0.30/32 indicates that only the client whose IP address is 10.10.0.30 can connect to the host. The specified IP address must be different from those used in openGauss. 32 indicates that there are 32 bits whose value is 1 in the subnet mask. That is, the subnet mask is 255.255.255.255.
sha256 indicates that the password of user jack is encrypted using the SHA-256 algorithm.
This command adds a rule to the pg_hba.conf file corresponds to the primary node of the database. The rule is used to authenticate clients that access primary node.

Each record in the pg_hba.conf file can be in one of the following four formats. For parameter description of the four formats, see Configuration File Reference.

local     DATABASE USER METHOD [OPTIONS]
host      DATABASE USER ADDRESS METHOD [OPTIONS]
hostssl   DATABASE USER ADDRESS METHOD [OPTIONS]
hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
During authentication, the system checks records in the pg_hba.conf file in sequence for connection requests, so the record sequence is vital.

NOTE:
Configure records in the pg_hba.conf file from top to bottom based on communication and format requirements in the descending order of priorities. The IP addresses of the openGauss cluster and added hosts are of the highest priority and should be configured prior to those manually configured by users. If the IP addresses manually configured by users and those of added hosts are in the same network segment, delete the manually configured IP addresses before the scale-out and configure them after the scale-out.

The suggestions on configuring authentication rules are as follows:

Records placed at the front have strict connection parameters but weak authentication methods.
Records placed at the end have weak connection parameters but strict authentication methods.
NOTE:

If a user wants to connect to a specified database, the user must be authenticated by the rules in the pg_hba.conf file and have the CONNECT permission for the database. If you want to restrict a user from connecting to certain databases, you can grant or revoke the user's CONNECT permission, which is easier than setting rules in the pg_hba.conf file.
The trust authentication mode is insecure for a connection between the openGauss and a client outside the cluster. In this case, set the authentication mode to sha256.
Exception Handling
There are many reasons for a user authentication failure. You can view an error message returned from a server to a client to determine the exact cause. Table 1 lists common error messages and solutions to these errors.

Table 1 Error messages

Example

TYPE  DATABASE        USER            ADDRESS                 METHOD

"local" is for Unix domain socket connections only
#Allow only the user specified by the -U parameter during installation to establish a connection from the local server.
local   all             all                                     trust
IPv4 local connections:
#User  jack  is allowed to connect to any database from the 10.10.0.50 host. The SHA-256 algorithm is used to encrypt the password.
host    all           jack             10.10.0.50/32            sha256
#Any user is allowed to connect to any database from a host on the 10.10.0.0/24 network segment. The SHA-256 algorithm is used to encrypt the password and SSL transmission is used.
hostssl    all             all             10.10.0.0/24            sha256

Comparison – Disk vs. MOT

wanglei — Tue, 18 Apr 2023 06:37:25 +0000

The following table briefly compares the various features of the openGauss disk-based storage engine and the MOT storage engine.

Table 1 Comparison – Disk-based vs. MOT

Appendices
References
[1] Y. Mao, E. Kohler, and R. T. Morris. Cache craftiness for fast multicore key-value storage. In Proc. 7th ACM European Conference on Computer Systems (EuroSys), Apr. 2012.

[2] K. Ren, T. Diamond, D. J. Abadi, and A. Thomson. Low-overhead asynchronous checkpointing in main-memory database systems. In Proceedings of the 2016 ACM SIGMOD International Conference on Management of Data, 2016.

[3] https://e.huawei.com/en/products/servers/taishan-server/taishan-2280-v2.

[4] https://e.huawei.com/en/products/servers/taishan-server/taishan-2480-v2.

[5] Tu, S., Zheng, W., Kohler, E., Liskov, B., and Madden, S. Speedy transactions in multicore in-memory databases. In Proceedings of the Twenty-Fourth ACM Symposium on Operating Systems Principles (New York, NY, USA, 2013), SOSP ’13, ACM, pp. 18–32.

[6] H. Avni at al. Industrial-Strength OLTP Using Main Memory and Many-cores, VLDB 2020.

[7] Bernstein, P. A., and Goodman, N. Concurrency control in distributed database systems. ACM Comput. Surv. 13, 2 (1981), 185–221.

[8] Felber, P., Fetzer, C., and Riegel, T. Dynamic performance tuning of word-based software transactional memory. In Proceedings of the 13th ACM SIGPLAN Symposium on Principles and Practice of Parallel Programming, PPOPP 2008, Salt Lake City, UT, USA, February 20-23, 2008 (2008),

pp. 237–246.

[9] Appuswamy, R., Anadiotis, A., Porobic, D., Iman, M., and Ailamaki, A. Analyzing the impact of system architecture on the scalability of OLTP engines for high-contention workloads. PVLDB 11, 2 (2017),

121–134.

[10] R. Sherkat, C. Florendo, M. Andrei, R. Blanco, A. Dragusanu, A. Pathak, P. Khadilkar, N. Kulkarni, C. Lemke, S. Seifert, S. Iyer, S. Gottapu, R. Schulze, C. Gottipati, N. Basak, Y. Wang, V. Kandiyanallur, S. Pendap, D. Gala, R. Almeida, and P. Ghosh. Native store extension for SAP HANA. PVLDB, 12(12):

2047–2058, 2019.

[11] X. Yu, A. Pavlo, D. Sanchez, and S. Devadas. Tictoc: Time traveling optimistic concurrency control. In Proceedings of the 2016 International Conference on Management of Data, SIGMOD Conference 2016, San Francisco, CA, USA, June 26 - July 01, 2016, pages 1629–1642, 2016.

[12] V. Leis, A. Kemper, and T. Neumann. The adaptive radix tree: Artful indexing for main-memory databases. In C. S. Jensen, C. M. Jermaine, and X. Zhou, editors, 29th IEEE International Conference on Data Engineering, ICDE 2013, Brisbane, Australia, April 8-12, 2013, pages 38–49. IEEE Computer Society, 2013.

[13] S. K. Cha, S. Hwang, K. Kim, and K. Kwon. Cache-conscious concurrency control of main-memory indexes on shared-memory multiprocessor systems. In P. M. G. Apers, P. Atzeni, S. Ceri, S. Paraboschi, K. Ramamohanarao, and R. T. Snodgrass, editors, VLDB 2001, Proceedings of 27th International Conference on Very Large Data Bases, September 11-14, 2001, Roma, Italy, pages 181–190. Morga Kaufmann, 2001.

Glossary
Table 2 Glossary

MOT JIT Diagnostics

wanglei — Tue, 18 Apr 2023 06:35:20 +0000

mot_jit_detail
This built-in function is used to query the details about JIT compilation (code generation).

Usage Examples

select * from mot_jit_detail();

select proc_oid, substr(query, 0, 50), namespace, jittable_status, valid_status, last_updated, plan_type, codegen_time from mot_jit_detail();

Output Description

mot_jit_profile
This built-in function is used to query the profiling data (performance data) of the query or stored procedure execution.

Usage Examples

select * from mot_jit_profile();
select proc_oid, id, parent_id, substr(query, 0, 50), namespace, weight, total, self, child_gross, child_net from mot_jit_profile();

Output Description

Miscellaneous
Another useful system table to get information about stored procedures and functions is pg_proc.

For example, body of a stored procedure can be queried using the following query:

select proname,prosrc from pg_proc where proname='sp_call_filter_rules_100_1';

JIT for Stored procedures

wanglei — Tue, 18 Apr 2023 06:33:01 +0000

JIT for Stored Procedures (JIT SP) is supported by the openGauss MOT engine (starting from 5.0 version), and its goal is deliver even higher performance and lower latency.

JIT SP refers to code generation, compiling and execution of stored procedures (SP) by LLVM runtime code generation and execution library. JIT SP is available to SPs accessing MOT tables (only) and is completely transparent to users. SPs with Cross-Tx usage will be executed by standard PLSQL. Acceleration level depends on the SP logic complexity. For example, a real customer application achieved acceleration of 20%, 44%, 300% and 500% for different SPs, shaving microseconds to tens of milliseconds of the SP latency.

During the PREPARE phase of a query invoking an SP, or the first SP execution, the JIT module performs an attempt to translate the SP SQL into a C-based function and compile it in runtime (using LLVM). If successful, the consecutive SP invocations the MOT will execute a compiled function, leading to performance gains. In case of failure to produce a compiled function, the SP will be executed by standard PLSQL. Both scenarios are fully transparent to users.

You may refer to JIT Diagnostics for useful diagnostics information.