gsql

gsql is a MogDB-provided database connection tool running in the CLI. gsql provides basic and advanced functions of databases to facilitate user operations.

Confirming Connection Information

You can use a client tool to connect 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.

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

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 primary database node obtained in step 2. The command is as follows:

cat /mogdb/data/db1/postgresql.conf | grep port

port = 26000    # (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 primary database node.

Installing the gsql Client

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 any user.
Run the following command to create the /opt/mogdb/tools directory:
```
mkdir /opt/mogdb/tools
```
Obtain the file MogDB-x.x.x-openEuler-64bit-tools.tar.gz from the software installation package and upload it to the /opt/mogdb/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 /opt/mogdb/tools
tar -zxvf MogDB-x.x.x-openEuler-64bit-tools.tar.gz

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
```
Run the following command to make the environment variables take effect:
```
source ~/.bashrc
```

Connecting to a Database Using gsql

NOTE:

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.

Connecting to a Database Locally

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 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 x.x.x build 56189e20) compiled at 2022-01-07 18:47:53 commit 0 last mr  )
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.
Exit the database.
```
postgres=# \q
```

NOTE:

When connecting to the database locally as user omm, no password is required. This is due to the default setting in the pg_hba.conf file that allows the local machine to connect in the trust way.

For details about the client authentication methods, see the Client Access Authentication chapter.

Connecting to a Database Remotely

NOTE:

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

Configuring a Whitelist Using gs_guc (Update pg_hba.conf)

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 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"
```
NOTICE:
- 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.

NOTE:

For details about the client authentication methods, see the Client Access Authentication chapter.
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.

Issue