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

Run the gs_om -t status --detail command to query instances in the MogDB cluster.

gs_om -t status --detail

[ DBnode State ]

node        node_ip         instance                                  state
-----------------------------------------------------------------------------
1  plat1 192.168.0.11  5001 /srv/BigData/gaussdb/data1/dbnode Normal
2  plat2 192.168.0.12  5002 /srv/BigData/gaussdb/data1/dbnode Normal
3  plat3 192.168.0.13  5003 /srv/BigData/gaussdb/data1/dbnode Normal

For example, the server IP addresses where the primary node of the database is deployed are 192.168.10.11, 192.168.10.12, and 192.168.0.13. The data path of the primary node of the database is /srv/BigData/gaussdb/data1/dbnode.

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 2. The command is as follows:

cat /srv/BigData/gaussdb/data1/dbnode/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

8000 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

Log in as the OS user omm to the primary node of the database.
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 8000
```
postgres is the name of the database to be connected, and 8000 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 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.

postgres=#
```
User omm is the administrator, and DBNAME=# is displayed. If you log in to and connect to the database as a common user, DBNAME=> 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.
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 Mypwd123. You can use the following command:
```
postgres=# ALTER ROLE omm IDENTIFIED BY 'Mypwd123' REPLACE 'XuanYuan@2012';
```
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

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 reload -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:
```
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.
- 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.
```
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.
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.

Log in to the host where the client resides as user root.
Run the following command to create the /tmp/tools directory:
```
mkdir /tmp/tools
```
Obtain the file MogDB-1.0.0-openEuler-64bit-Libpq.tar.gz from the software installation package and upload it to the /tmp/tools directory.

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.

Run the following commands to decompress the package:

cd /tmp/tools
tar -zxvf MogDB-1.0.0-openEuler-64bit-Libpq.tar.gz

Log in to the server where the primary database node is located, copy the /bin directory in the database installation directory to the /tmp/tools directory of the client server, and then log in to the client server.
```
scp -r /opt/huawei/install/app/bin root@10.10.0.30:/tmp/tools
```
/opt/huawei/install/app is the directory of {gaussdbAppPath}, which is configured in the clusterconfig.xml file. 10.10.0.30 indicates the IP address of the client server.
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=/tmp/tools/bin:$PATH
export LD_LIBRARY_PATH=/tmp/tools/lib:$LD_LIBRARY_PATH
```
Run the following command to make the environment variables take effect:
```
source ~/.bashrc
```
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 10.10.0.11 -U jack -p 8000 -W Test@123
```
postgres is the name of the database, 10.10.0.11 is the IP address of the server where the primary node of the database resides, jack is the user of the database, 8000 is the port number of the CN, and Test@123 is the password of user jack.

NOTE:
- If a machine connected to MogDB is not in the same network segment as MogDB, the IP address specified by -h should be the value of coo.cooListenIp2 (application access IP address) set in Manager.
- Do not remotely connect to the database as user omm.