MogDB
Ecological Tools
Doc Menu

Using the gsql Client for Connection

Confirming Connection Information

You can use a client tool to connect to a database through the primary node of the database. Before the connection, obtain the IP address of the primary node of the database and the port number of the server where the primary node of the database is deployed.

Procedure

  1. Log in as the OS user omm to the primary node of the database.
  2. Run the gs_om -t status --detail command to query instances in the MogDB cluster.

    gs_om -t status --detail
    [  Datanode State   ]
    
    node                 node_ip         instance                state
    ---------------------------------------------------------------------------------
    1  mogdb-kernel-0005 172.16.0.176    6001 /mogdb/data/db1 P Primary Normal

    For example, the server IP addresses where the primary node of the database is deployed is 172.16.0.176. The data path of the primary node of the database is /mogdb/data/db1.

  3. Confirm the port number of the primary node of the database.

    View the port number in the postgresql.conf file in the data path of the database primary node obtained in step 2. The command is as follows:

    cat /mogdb/data/db1/postgresql.conf | grep port
    port = 8000    # (change requires restart)
    #comm_sctp_port = 1024   # Assigned by installation (change requires restart)
    #comm_control_port = 10001  # Assigned by installation (change requires restart)
          # supported by the operating system:
          # e.g. 'localhost=10.145.130.2 localport=12211 remotehost=10.145.130.3 remoteport=12212, localhost=10.145.133.2 localport=12213 remotehost=10.145.133.3 remoteport=12214'
          # e.g. 'localhost=10.145.130.2 localport=12311 remotehost=10.145.130.4 remoteport=12312, localhost=10.145.133.2 localport=12313 remotehost=10.145.133.4 remoteport=12314'
          #   %r = remote host and port
    alarm_report_interval = 10
    support_extended_features=true

    26000 in the first line is the port number of the database primary node.

Connecting to a Database Locally

gsql is an MogDB-provided database connection tool running in the CLI. gsql provides basic and advanced functions of databases to facilitate user operations. This section describes how to use gsql to connect to a database. For details about its usage, see the related chapter in the Tool Reference.

Precautions

By default, if a client is idle state after connecting to a database, the client automatically disconnects from the database in the duration specified by session_timeout. To disable the timeout setting, set session_timeout to 0.

Prerequisites

Connection information has been confirmed.

Procedure

  1. Log in as the OS user omm to the primary node of the database.
  2. Connect to a database.

    After the database is installed, a database named postgres is generated by default. When connecting to a database for the first time, you can connect to this database.

    Run the following command to connect to the postgres database:

    gsql -d postgres -p 26000

    postgres is the name of the database to be connected, and 26000 is the port number of the database primary node. Replace the values as required.

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

    gsql ((MogDB 2.0.0 build b75b585a) 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=# 

    User omm is the administrator, and postgres=# is displayed. If you log in to and connect to the database as a common user, postgres=> is displayed.

    Non-SSL connection indicates that the database is not connected in SSL mode. If high security is required, connect to the database in SSL mode.

  3. Change the password after your first login. The initial password is set manually during MogDB database installation. For details, see the Installation Guide. You need to change the initial password. Suppose you want to change the initial password to Enmo@321. You can use the following command:

    postgres=# ALTER ROLE omm IDENTIFIED BY 'Enmo@321' REPLACE 'Enmo@123';
  4. Exit the database.

    postgres=# \q

Connecting to a Database Remotely

gsql is an MogDB-provided database connection tool running in the CLI. gsql provides basic and advanced functions of databases to facilitate user operations. This section describes how to install the gsql client and use it to connect to a database. For more configurations, see the MogDB product documentation.

Prerequisites

Connection information has been confirmed.

Configuring a Whitelist Using gs_guc

  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 172.16.0.245 to access the current host.

    gs_guc set -N all -I all -h "host all jack 172.16.0.245/24 sha256"

    img NOTE:

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

      postgres=# 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.
    • 172.16.0.245/24 indicates that only the client whose IP address is 172.16.0.245 can connect to the host. The specified IP address must be different from those used in MogDB. 24 indicates that there are 24 bits whose value is 1 in the subnet mask. That is, the subnet mask is 255.255.255.0.
    • 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.

    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.

Installing the gsql Client and Connecting to a Database

On the host, upload the client tool package and configure environment variables for the gsql client.

  1. Log in to the host where the client resides as any user.
  2. Run the following command to create the /opt/mogdb/tools directory:

    mkdir /opt/mogdb/tools
  3. Obtain the file MogDB-2.0.0-openEuler-64bit-tools.tar.gz from the software installation package and upload it to the /opt/mogdb/tools directory.

    img NOTE:

    • The software package is located where you put it before installation. Set it based on site requirements.
    • The tool package name may vary in different OSs. Select the tool package suitable for your OS.
  4. Run the following commands to decompress the package:

    cd /opt/mogdb/tools
    tar -zxvf MogDB-2.0.0-openEuler-64bit-tools.tar.gz
  5. Set environment variables.

    Run the following command to open the ~/.bashrc file:

    vi ~/.bashrc

    Enter the following content and run :wq! to save and exit.

    export PATH=/opt/mogdb/tools/bin:$PATH
    export LD_LIBRARY_PATH=/opt/mogdb/tools/lib:$LD_LIBRARY_PATH
  6. Run the following command to make the environment variables take effect:

    source ~/.bashrc
  7. Connect to a database.

    After the database is installed, a database named postgres is generated by default. When connecting to a database for the first time, you can connect to this database.

    gsql -d postgres -h 172.16.0.176 -U jack -p 26000 -W Test@123

    postgres is the name of the database, 172.16.0.176 is the IP address of the server where the primary node of the database resides, jack is the user of the database, 26000 is the port number of the CN, and Test@123 is the password of user jack.

    img NOTE:

    • Due to security restrictions, you can not remotely connect to the database as user omm.