- About MogDB
- MogDB Introduction
- Comparison Between MogDB and openGauss
- MogDB New Features
- High Availability and Performance
- Open Source Components
- Usage Limitations
- Quick Start
- Installation Guide
- Container Installation
- Standard Installation
- Installation Overview
- Preparing for Installation
- Installing the MogDB
- Verifying the Installation
- Uninstalling the MogDB
- Administrator Guide
- Routine Maintenance
- Primary and Standby Management
- MogHA Management
- MOT Engine
- Introducing MOT
- Using MOT
- Concepts of MOT
- Column-store Tables Management
- Backup and Restoration
- Importing and Exporting Data
- Importing Data
- Exporting Data
- Upgrade Guide
- Security Guide
- Performance Tuning
- System Optimization
- SQL Optimization
- WDR Snapshot Schema
- Developer Guide
- Application Development Guide
- Development Specifications
- Development Based on JDBC
- JDBC Package, Driver Class, and Environment Class
- Development Process
- Loading the Driver
- Connecting to a Database
- Connecting to the Database (Using SSL)
- Running SQL Statements
- Processing Data in a Result Set
- Closing a Connection
- Example: Common Operations
- Example: Retrying SQL Queries for Applications
- Example: Importing and Exporting Data Through Local Files
- Example 2: Migrating Data from a MY Database to MogDB
- Example: Logic Replication Code
- JDBC Interface Reference
- Development Based on ODBC
- Development Based on libpq
- Stored Procedure
- User Defined Functions
- Application Development Guide
- Tool Reference
- System Catalogs and System Views
- Overview of System Catalogs and System Views
- System Catalogs
- System Views
- Functions and Operators
- Logical Operators
- Comparison Operators
- Character Processing Functions and Operators
- Binary String Functions and Operators
- Bit String Functions and Operators
- Mode Matching Operators
- Mathematical Functions and Operators
- Date and Time Processing Functions and Operators
- Type Conversion Functions
- Geometric Functions and Operators
- Network Address Functions and Operators
- Text Search Functions and Operators
- JSON Functions
- SEQUENCE Functions
- Array Functions and Operators
- Range Functions and Operators
- Aggregate Functions
- Window Functions
- Security Functions
- Encrypted Equality Functions
- Set Returning Functions
- Conditional Expression Functions
- System Information Functions
- System Administration Functions
- Statistics Information Functions
- Trigger Functions
- Global Temporary Table Functions
- AI Feature Functions
- Other System Functions
- Supported Data Types
- SQL Syntax
- ALTER DATABASE
- ALTER DATA SOURCE
- ALTER DEFAULT PRIVILEGES
- ALTER DIRECTORY
- ALTER FOREIGN TABLE
- ALTER FUNCTION
- ALTER GROUP
- ALTER INDEX
- ALTER LARGE OBJECT
- ALTER MATERIALIZED VIEW
- ALTER ROLE
- ALTER ROW LEVEL SECURITY POLICY
- ALTER RULE
- ALTER SCHEMA
- ALTER SEQUENCE
- ALTER SERVER
- ALTER SESSION
- ALTER SYNONYM
- ALTER SYSTEM KILL SESSION
- ALTER SYSTEM SET
- ALTER TABLE
- ALTER TABLE PARTITION
- ALTER TABLESPACE
- ALTER TEXT SEARCH CONFIGURATION
- ALTER TEXT SEARCH DICTIONARY
- ALTER TRIGGER
- ALTER TYPE
- ALTER USER
- ALTER USER MAPPING
- ALTER VIEW
- ANALYZE | ANALYSE
- COMMIT | END
- COMMIT PREPARED
- CREATE CLIENT MASTER KEY
- CREATE COLUMN ENCRYPTION KEY
- CREATE DATABASE
- CREATE DATA SOURCE
- CREATE DIRECTORY
- CREATE FOREIGN TABLE
- CREATE FUNCTION
- CREATE GROUP
- CREATE INDEX
- CREATE MATERIALIZED VIEW
- CREATE ROW LEVEL SECURITY POLICY
- CREATE PROCEDURE
- CREATE ROLE
- CREATE RULE
- CREATE SCHEMA
- CREATE SEQUENCE
- CREATE SERVER
- CREATE SYNONYM
- CREATE TABLE
- CREATE TABLE AS
- CREATE TABLE PARTITION
- CREATE TABLESPACE
- CREATE TEXT SEARCH CONFIGURATION
- CREATE TEXT SEARCH DICTIONARY
- CREATE TRIGGER
- CREATE TYPE
- CREATE USER
- CREATE USER MAPPING
- CREATE VIEW
- DROP CLIENT MASTER KEY
- DROP COLUMN ENCRYPTION KEY
- DROP DATABASE
- DROP DATA SOURCE
- DROP DIRECTORY
- DROP FOREIGN TABLE
- DROP FUNCTION
- DROP GROUP
- DROP INDEX
- DROP MATERIALIZED VIEW
- DROP OWNED
- DROP ROW LEVEL SECURITY POLICY
- DROP PROCEDURE
- DROP ROLE
- DROP RULE
- DROP SCHEMA
- DROP SEQUENCE
- DROP SERVER
- DROP SYNONYM
- DROP TABLE
- DROP TABLESPACE
- DROP TEXT SEARCH CONFIGURATION
- DROP TEXT SEARCH DICTIONARY
- DROP TRIGGER
- DROP TYPE
- DROP USER
- DROP USER MAPPING
- DROP VIEW
- EXPLAIN PLAN
- MERGE INTO
- PREPARE TRANSACTION
- REASSIGN OWNED
- REFRESH MATERIALIZED VIEW
- RELEASE SAVEPOINT
- ROLLBACK PREPARED
- ROLLBACK TO SAVEPOINT
- SELECT INTO
- SET CONSTRAINTS
- SET ROLE
- SET SESSION AUTHORIZATION
- SET TRANSACTION
- START TRANSACTION
- GUC Parameters
- GUC Parameter Usage
- File Location
- Connection and Authentication
- Resource Consumption
- Parallel Import
- Write Ahead Log
- HA Replication
- Memory Table
- Query Planning
- Error Reporting and Logging
- Alarm Detection
- Statistics During the Database Running
- Load Management
- Automatic Vacuuming
- Default Settings of Client Connection
- Lock Management
- Version and Platform Compatibility
- Faut Tolerance
- Connection Pool Parameters
- MogDB Transaction
- Developer Options
- Upgrade Parameters
- Miscellaneous Parameters
- Wait Events
- System Performance Snapshot
- Equality Query in a Fully-encrypted Database
- Global Temporary Table
- DBE_PERF Overview
- Wait Events
- Workload Manager
- Global Plancache
- Error Code Reference
- Description of SQL Error Codes
- Third-Party Library Error Codes
- GAUSS-00001 – GAUSS-00100
- GAUSS-00101 – GAUSS-00200
- GAUSS 00201 – GAUSS 00300
- GAUSS 00301 – GAUSS 00400
- GAUSS 00401 – GAUSS 00500
- GAUSS 00501 – GAUSS 00600
- GAUSS 00601 – GAUSS 00700
- GAUSS 00701 – GAUSS 00800
- GAUSS 00801 – GAUSS 00900
- GAUSS 00901 – GAUSS 01000
- GAUSS 01001 – GAUSS 01100
- GAUSS 01101 – GAUSS 01200
- GAUSS 01201 – GAUSS 01300
- GAUSS 01301 – GAUSS 01400
- GAUSS 01401 – GAUSS 01500
- GAUSS 01501 – GAUSS 01600
- GAUSS 01601 – GAUSS 01700
- GAUSS 01701 – GAUSS 01800
- GAUSS 01801 – GAUSS 01900
- GAUSS 01901 – GAUSS 02000
- GAUSS 02001 – GAUSS 02100
- GAUSS 02101 – GAUSS 02200
- GAUSS 02201 – GAUSS 02300
- GAUSS 02301 – GAUSS 02400
- GAUSS 02401 – GAUSS 02500
- GAUSS 02501 – GAUSS 02600
- GAUSS 02601 – GAUSS 02700
- GAUSS 02701 – GAUSS 02800
- GAUSS 02801 – GAUSS 02900
- GAUSS 02901 – GAUSS 03000
- GAUSS 03001 – GAUSS 03100
- GAUSS 03101 – GAUSS 03200
- GAUSS 03201 – GAUSS 03300
- GAUSS 03301 – GAUSS 03400
- GAUSS 03401 – GAUSS 03500
- GAUSS 03501 – GAUSS 03600
- GAUSS 03601 – GAUSS 03700
- GAUSS 03701 – GAUSS 03800
- GAUSS 03801 – GAUSS 03900
- GAUSS 03901 – GAUSS 04000
- GAUSS 04001 – GAUSS 04100
- GAUSS 04101 – GAUSS 04200
- GAUSS 04201 – GAUSS 04300
- GAUSS 04301 – GAUSS 04400
- GAUSS 04401 – GAUSS 04500
- GAUSS 04501 – GAUSS 04600
- GAUSS 04601 – GAUSS 04700
- GAUSS 04701 – GAUSS 04800
- GAUSS 04801 – GAUSS 04900
- GAUSS 04901 – GAUSS 05000
- GAUSS 05001 – GAUSS 05100
- GAUSS 05101 – GAUSS 05200
- GAUSS 05201 – GAUSS 05300
- GAUSS 05301 – GAUSS 05400
- GAUSS 05401 – GAUSS 05500
- GAUSS 05501 – GAUSS 05600
- GAUSS 05601 – GAUSS 05700
- GAUSS 05701 – GAUSS 05800
- GAUSS 05801 – GAUSS 05900
- GAUSS 05901 – GAUSS 06000
- GAUSS 06001 – GAUSS 06100
- GAUSS 06101 – GAUSS 06200
- GAUSS 06201 – GAUSS 06300
- GAUSS 06301 – GAUSS 06400
- GAUSS 06401 – GAUSS 06500
- GAUSS 06501 – GAUSS 06600
- GAUSS 06601 – GAUSS 06700
- GAUSS 06701 – GAUSS 06800
- GAUSS 06801 – GAUSS 06900
- GAUSS 06901 – GAUSS 07000
- GAUSS 07001 – GAUSS 07100
- GAUSS 07101 – GAUSS 07200
- GAUSS 07201 – GAUSS 07300
- GAUSS 07301 – GAUSS 07400
- GAUSS 07401 – GAUSS 07480
- GAUSS 50000 – GAUSS 50999
- GAUSS 51000 – GAUSS 51999
- GAUSS 52000 – GAUSS 52999
- GAUSS 53000 – GAUSS 53699
- System Catalogs and System Views
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.
- 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:
mogdb=# CREATE USER jack PASSWORD 'Test@123';
- -N all indicates all hosts in MogDB.
- -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 MogDB. 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 MogDB 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.
- 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 MogDB and a client outside the cluster. In this case, set the authentication mode to sha256.
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](#Table 1.1.1) lists common error messages and solutions to these errors.
|The username or password is incorrect.
||Retry the authentication with the correct username and password.|
|The database to connect does not exist.
||Retry the authentication with the correct database name.|
|No matched client record is found.
||This message indicates that the server is connected but denies the connection request, because it does not find a matched record in pg_hba.conf. Contact the database administrator to add user information to the pg_hba.conf file.|
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 #Any user is allowed to connect to any database from a host on the 10.10.0.0/24 network segment. The Kerberos authentication is used. In the current version, Kerberos authentication cannot be used to connect to external clients. host all all 10.10.0.0/24 gss include_realm=1 krb_realm=HADOOP.COM
Table 1 Parameter description
|local||Indicates that this record accepts only the Unix-domain-socket connection. If no such type of record exists, Unix-domain-socket connections are not allowed.
When gsql is used to initiate a connection from a local server and the -U parameter is not specified, a Unix-domain-socket connection is established.
|host||Indicates that this record accepts either a common TCP/IP-socket connection or a TCP/IP-socket connection encrypted through SSL.||-|
|hostssl||Indicates that this record accepts only a TCP/IP socket connection encrypted through SSL.||For the connection encrypted through SSL, you need to apply for a digital certificate and configure related parameters. For details, see Establishing Secure TCP/IP Connections in SSL Mode.|
|hostnossl||Indicates that this record accepts only a common TCP/IP socket connection.||-|
|DATABASEGUC||Database that a record matches and can access||- all: indicates that this record matches all databases.
- sameuser: indicates that the database must have the same name as the user who requests database access.
- samerole: indicates that this record matches a database if the user who requests the database is a member of a role having the same name as the database.
- samegroup: is the same as that of samerole and indicates that this record matches a database if the user who requests the database is a member of a role having the same name as the database.
- A file containing database names with an at sign (@) added before the file name, or a database list in a file using commas (,) or line feeds to separate databases.
- A specific database name or a list of databases separated by commas (,)
replication indicates that if a replication link is requested, the records match the link. But this does not mean the record matches any specific database. To use a database named replication, specify it in the database column.
|USER||Users who match the record and are allowed to access databases||- all: indicates that this record matches all users.
- +User role: indicates that this record matches all members that directly or indirectly belong to the role.
+ is a prefix character.
- A file containing usernames, with an at sign (@) added before the file name. Users in the file are separated by commas (,) or line feeds.
- A specific database username or a list of users separated by commas (,)
|ADDRESS||Range of IP addresses that match the record and can be visited||IPv4 and IPv6 are supported. The IP address range can be expressed in the following two formats:
- IP address/mask length Example: 10.10.0.0/24
- IP address Subnet mask Example: 10.10.0.0 255.255.255.0
An IPv4 address matches the IPv6 connection with the corresponding address. For example, 127.0.0.1 matches IPv6 address ::ffff:127.0.0.1.
|METHOD||Authentication method used for connection||The following authentication modes are supported. For details, see [Table 2](#Table 1.2.2).
- md5 (not recommended and not supported by default. This authentication mode can be configured using the password_encryption_type parameter.)
- gss (only for authentication within MogDB)
|trust||In trust mode, only the connection initiated from the local server using gsql with the -U parameter not specified is trusted. In this case, no password is required.
The trust authentication mode applies to local connection of a single-user workstation, but not of a multi-user workstation. To use the trust authentication, you can use the file system permissions to control the access to the Unix-domain socket file on the server. You can use either of the following methods to control the access:
- Set the unix_socket_permissions and unix_socket_group parameters.
- Set the unix_socket_directory parameter to place Unix-domain socket files into a directory requiring certain access permissions.
Setting the file system permission imposes restrictions on only Unix-domain socket connections, and does not affect local TCP/IP connections. To ensure local TCP/IP security, MogDB does not allow the trust authentication for remote connection.
|reject||Rejects connection unconditionally. This authentication mode is usually used for filtering certain hosts.|
|md5||Requires that the client must provide an MD5-encrypted password for authentication.
This authentication method is not recommended because MD5 is not a secure encryption algorithm and may cause network risks. MogDB retains MD5 authentication and password storage to facilitate use of third-party tools (such as the TPCC test tool).
|sha256||Requires that the client must provide a sha256-encrypted password for authentication. The password is encrypted based on the unidirectional sha256 of salt (a random number sent from the server to the client) when being transmitted, enhancing the security.|
|cert||Client certificate authentication mode. In this mode, the SSL connection must be configured and the client must provide a valid SSL certificate. The user password is not required.
This authentication mode supports only hostssl rules.
|gss||Uses the GSSAPI-based Kerberos authentication.
- This authentication mode depends on components such as the Kerberos server. It supports only authentication for communication within MogDB. In the current version, Kerberos authentication cannot be used to connect to external clients.
- Enabling Kerberos authentication within MogDB slows down the connection setup among nodes in MogDB. The performance of SQL operations during the setup is affected, but later operations are not.
MogDB 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.
Obtain formal certificates and keys for servers and clients 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.
- 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.
After a cluster is deployed, MogDB 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](#Table 1.3.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 MogDB 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.
|Authentication Mode||Description||Client Environment Variable Setting||Maintenance Suggestion|
|Bidirectional authentication (recommended)||The client verifies the server's certificate and the server verifies the client's certificate. Connection can be set up after the verification is successful.||Set the following environment variables:
|This authentication mode is applicable to scenarios that require high data security. When using this method, you are advised to set the PGSSLMODE client variable to verify-ca for network data security purposes.|
|Unidirectional authentication||The client verifies the server's certificate, whereas the server does not verify the client's certificate. The server loads the certificate information and sends it to the client. The client verifies the server's certificate according to the root certificate.||Set the following environment variables:
|To prevent TCP-based link spoofing, you are advised to use the SSL certificate authentication. In addition to configuring client root certificate, you are advised to set the PGSSLMODE variable to verify-ca on the client.|
In the postgresql.conf file on the server, set the related parameters. For details, see [Table 2](#Table 1.3.2).
|ssl||Specifies whether to enable the SSL function.||- on: indicates that SSL is enabled.
- off: indicates that SSL is disabled.
|require_ssl||Specifies whether the server requires the SSL connection. This parameter is valid only when ssl is set to on.||- on: The server requires the SSL connection.
- off: The server does not require the SSL connection.
|ssl_cert_file||Server certificate file, including the server public key. The certificate proves the legal identity of the server and the public key is sent to the peer end for data encryption.||Refer to the actual certificate name. A relative path must be used. The path depends on the data directory.
|ssl_key_file||Private key file of the server, used to decrypt data encrypted using the public key.||Refer to the name of the actual private key. A relative path must be used. The path depends on the data directory.
|ssl_ca_file||Root certificate of the CA server. This parameter is optional and needs to be set only when the certificate of a client must be verified.||Refer to the name of the actual root certificate.
|ssl_crl_file||Certificate revocation list (CRL). If the certificate of a client is in the list, the certificate is invalid.||Refer to the actual name of the certificate revocation list.
Default value: empty, indicating that there is no certificate revocation list.
|ssl_ciphers||Encryption algorithm used for SSL communication||For details about the supported encryption algorithms, see [Table 4](#Table 1.3.4).
Default value:ALL, indicating that all supported encryption algorithms, excluding ADH, LOW, EXP, and MD5
Configure environment variables related to SSL authentication on the client. For details, see [Table 3](#Table 1.3.3).
NOTE: The path of environment variables is set to /home/omm as an example. Replace it with the actual path.
|Environment Variable||Description||Value Range|
|PGSSLCERT||Client certificate file, including the client public key. The certificate proves the legal identity of the client and the public key is sent to the peer end for data encryption.||Absolute path of a certificate file, for example:
Default value: empty
|PGSSLKEY||Private key file of the client, used to decrypt data encrypted using the public key||Absolute path of a certificate file, for example:
Default value: empty
|PGSSLMODE||Specifies whether to negotiate with the server about SSL connection and specifies the priority of the SSL connection.||Values and description:
- disable: only tries to establish a non-SSL connection.
- allow: tries establishing a non-SSL connection first, and then an SSL connection if the attempt fails.
- prefer: tries establishing an SSL connection first, and then a non-SSL connection if the attempt fails.
- require: only tries establishing an SSL connection. If there is a CA file, perform the verification according to the scenario in which the parameter is set to verify-ca.
- verify-ca: tries setting up an SSL connection, checking whether the server certificate is issued by a trusted CA.
- verify-full: tries setting up an SSL connection, checking whether the server certificate is issued by a trusted CA, and checking whether the host name of the server is the same as that in the certificate.
|PGSSLROOTCERT||Root certificate file for issuing client certificates. The root certificate is used to verify the server certificate.||Absolute path of a certificate file, for example:
Default value: empty
|PGSSLCRL||CRL file for checking whether the server certificate is in the CRL. If it is, the certificate is invalid.||Absolute path of a certificate file, for example:
Default value: empty
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|
|on||disable||on||The connection fails, because the server requires SSL but the client has disabled it.||disable||off||The connection is not encrypted.|
|allow||on||The connection is encrypted.||allow||off||The connection is not encrypted.|
|prefer||on||The connection is encrypted.||prefer||off||The connection is encrypted.|
|require||on||The connection is encrypted.||require||off||The connection is encrypted.|
|verify-ca||on||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||on||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||on||The connection is not encrypted.||disable||off||The connection is not encrypted.|
|allow||on||The connection is not encrypted.||allow||off||The connection is not encrypted.|
|prefer||on||The connection is not encrypted.||prefer||off||The connection is not encrypted.|
|require||on||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||on||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||on||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](#Table 1.3.4) lists the encryption algorithms supported by the SSL.
|Encryption Strength||Security||Encryption Algorithm|
- Currently, SSL transmission supports encryption algorithms with the encryption strength higher than strong.
- The default value of ssl_ciphers is ALL, indicating that all encryption algorithms listed in the table are supported. You are advised to retain the default value, unless there are other special requirements on the encryption algorithm.
- If multiple encryption algorithms are specified, use semicolons (;) to separate them. For example, set ssl_ciphers='DHE-RSA-AES256-GCM-SHA384;DHE-RSA-AES256-SHA256;DHE-RSA-AES256-CCM' in postgresql.conf.
- If a DSS-related algorithm is to be used (such as DHE-DSS-AES256-GCM-SHA384, DHE-DSS-AES256-SHA256, and DHE-DSS-AES256-SHA), a certificate file with the DSA algorithm signature must be loaded. For details about how to use OpenSSL to generate a certificate file with the DSA algorithm signature, see the official OpenSSL documents.
- 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 will expire in seven days, an alarm is generated in logs during connection.
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.
The SSH service and the database must run on the same server.
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
- 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.
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.
- 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 mogdb -p 8000
mogdb 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 ((MogDB 1.1.0 build 5be05d82) 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. mogdb=#
View the upper limit of the number of global connections.
mogdb=# 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](#Table 1.5.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.
Description Command View the maximum number of sessions connected to a specific user. Run the following commands to view the upper limit of the number of omm's session connections. -1 indicates that no upper limit is set for the number of omm's session connections.
mogdb=# SELECT ROLNAME,ROLCONNLIMIT FROM PG_ROLES WHERE ROLNAME='omm';
rolname | rolconnlimit
omm | -1
View the number of session connections that have been used by a user. Run the following commands to view the number of session connections that have been used by omm. 1 indicates the number of session connections that have been used by omm.
mogdb=# CREATE OR REPLACE VIEW DV_SESSIONS AS
sa.sessionid AS SID,
0::integer AS SERIAL#,
sa.usesysid AS USER#,
ad.rolname AS USERNAME
FROM pg_stat_get_activity(NULL) AS sa
LEFT JOIN pg_authid ad ON(sa.usesysid = ad.oid)
WHERE sa.application_name <> 'JobScheduler';
mogdb=# SELECT COUNT() FROM DV_SESSIONS WHERE USERNAME='omm';
View the maximum number of sessions connected to a specific database. Run the following commands to view the upper limit of the number of mogdb's session connections. -1 indicates that no upper limit is set for the number of mogdb's session connections.
mogdb=# SELECT DATNAME,DATCONNLIMIT FROM PG_DATABASE WHERE DATNAME='mogdb'; datname | datconnlimit
mogdb | -1
View the number of session connections that have been used by a specific database. Run the following commands to view the number of session connections that have been used by mogdb. 1 indicates the number of session connections that have been used by mogdb.
mogdb=# SELECT COUNT() FROM PG_STAT_ACTIVITY WHERE DATNAME='mogdb';
View the number of session connections that have been used by all users. Run the following commands to view the number of session connections that have been used by all users:
mogdb=# CREATE OR REPLACE VIEW DV_SESSIONS AS
sa.sessionid AS SID,
0::integer AS SERIAL#,
sa.usesysid AS USER#,
ad.rolname AS USERNAME
FROM pg_stat_get_activity(NULL) AS sa
LEFT JOIN pg_authid ad ON(sa.usesysid = ad.oid)
WHERE sa.application_name <>'JobScheduler';
mogdb=# SELECT COUNT(*) FROM DV_SESSIONS;
Security certificates and keys generated using OpenSSL are configured in MogDB by default. In addition, MogDB provides certificate replacement interfaces to allow users to replace their certificates.
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.
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 777 ./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
Default security certificates and private keys required for SSL connection are configured in MogDB. You can change them as needed.
The formal certificates and keys for the server and client have been obtained from the CA.
Currently, MogDB supports only the X509v3 certificate in PEM format.
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 MogDB 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 MogDB.
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.