MogDB
Ecological Tools
Doc Menu

Client Access Authentication

Configuring Client Access Authentication

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

  1. Log in as the OS user omm to the primary node of the database.
  2. 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"

    img 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.

img 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.

img 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 MogDB 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](#Table 1.1.1) lists common error messages and solutions to these errors.

Table 1 Error messages

Symptom Solution
The username or password is incorrect.
FATAL: invalid username/password,login denied
Retry the authentication with the correct username and password.
The database to connect does not exist.
FATAL: database "TESTDB" does not exist
Retry the authentication with the correct database name.
No matched client record is found.
FATAL: no pg_hba.conf entry for host "10.10.0.60", user "ANDYM", database "TESTDB"
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.

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
#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

Configuration File Reference

Table 1 Parameter description

Parameter Description Value Range
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 (,)
NOTE:
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.
NOTE:
+ 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
NOTE:
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).
- trust
- reject
- md5 (not recommended and not supported by default. This authentication mode can be configured using the password_encryption_type parameter.)
- sha256
- cert
- gss (only for authentication within MogDB)

Table 2 Authentication modes

Authentication Mode Remarks
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.
NOTICE:
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.
NOTICE:
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.
NOTICE:
This authentication mode supports only hostssl rules.
gss Uses the GSSAPI-based Kerberos authentication.
NOTICE:
- 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.

Establishing Secure TCP/IP Connections in SSL Mode

Background

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.

Prerequisites

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.

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 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

img 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

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:
- PGSSLCERT
- PGSSLKEY
- PGSSLROOTCERT
- PGSSLMODE
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:
- PGSSLROOTCERT
- PGSSLMODE
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.

Reference

In the postgresql.conf file on the server, set the related parameters. For details, see [Table 2](#Table 1.3.2).

Table 2 Server parameters

Parameter Description Value Range
ssl Specifies whether to enable the SSL function. - on: indicates that SSL is enabled.
- off: indicates that SSL is disabled.
Default value:on
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.
Default value:off
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.
Default value:server.crt
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.
Default value:server.key
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.
Default value:cacert.pem
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).

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

Table 3 Client parameters

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:
export PGSSLCERT='/home/omm/client.crt'
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:
export PGSSLKEY='/home/omm/client.key'
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.
Default value
:prefer
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:
export PGSSLROOTCERT='/home/omm/certca.pem'
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:
export PGSSLCRL='/home/omm/sslcrl-file.crl'
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.

Table 4 Encryption algorithms

Encryption Strength Security Encryption Algorithm
stronger high DHE-RSA-AES256-GCM-SHA384
stronger high DHE-RSA-AES128-GCM-SHA256
stronger high DHE-DSS-AES256-GCM-SHA384
stronger high DHE-DSS-AES128-GCM-SHA256
stronger medium DHE-RSA-AES256-SHA256
stronger medium DHE-RSA-AES128-SHA256
stronger medium DHE-DSS-AES256-SHA256
stronger medium DHE-DSS-AES128-SHA256
stronger high DHE-RSA-AES256-CCM
stronger high DHE-RSA-AES128-CCM
stronger medium DHE-RSA-AES256-SHA
stronger medium DHE-RSA-AES128-SHA
stronger medium DHE-DSS-AES256-SHA
stronger medium DHE-DSS-AES128-SHA

img NOTE:

  • 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.

Establishing Secure TCP/IP Connections in SSH Tunnel Mode

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

img 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.

Checking the Number of Database Connections

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

  1. Log in as the OS user omm to the primary node of the database.
  2. 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=#
  3. 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.

  4. View the number of connections that have been used.

    For details, see [Table 1](#Table 1.5.1).

    img 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

    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
    (1 row)
    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
    SELECT
    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';
    count
    -——————–
    1
    (1 row)
    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
    (1 row)
    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';
    count
    -——————–
    1
    (1 row)
    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
    SELECT
    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;
    count
    -——————–
    10
    (1 row)

Managing SSL Certificates

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.

Generating Certificates

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

  1. 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.
  2. 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:
  3. 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 []:
  4. 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.
  5. 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.
  6. 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 []:
  7. 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.
  8. 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
  9. 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

Replacing Certificates

Scenarios

Default security certificates and private keys required for SSL connection are configured in MogDB. 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, MogDB supports only the X509v3 certificate in PEM format.

Procedure

  1. 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
  2. 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.

  3. Invoke the certificate replacement interface to replace a certificate.

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

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

    2. Run the following command to perform the replacement:

      gs_om -t cert --cert-file= /home/xxxx/db-cert-replacement.zip
  4. Restart the MogDB.

    gs_om -t stop
    gs_om -t start

    img 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.