“This is the second day of my participation in the Gwen Challenge in November. See details: The Last Gwen Challenge in 2021”

preface

Recently the database experiment class is using openGauss for experiments. Here we summarize and record and share some basic operations as well as some problems encountered and solutions. This article shows you how to use Navicat for remote connections.

OpenGauss service Settings

  1. Switch to user omm

  1. Adding a Release IP Address

Modify pg_hba.conf. The path is as shown in the following screenshot (ECS-F937 is the machine name and may be different).

If security is not considered, you can also use 0.0.0.0 to open all IP addresses and replace trust with MD5 encryption.

  1. Changing the Encryption Mode

Example Modify the postgresql.conf configuration file.

Delete the comment on this line and replace password_encryption_type = 2 with password_encryption_type = 0(using MD5 encryption).

  1. Restart the openGauss service

Gs_om -t restart is the official method to restart the system, but it seems that this method has been removed in the version update, so it is implemented in two steps, respectively stop and start operation:

gs_om -t stop
gs_om -t start
Copy the code

If an error occurs, the service cannot be started. Check whether the preceding configuration file is correct.

  1. Create a remote connection role

Since there is no change between the official demo and the current version when creating a user, the screenshot below uses the screenshot from the official demo video directly.

First enter the primary database with GSQL -d postgres -p 26000 -r, then create a user jack (or any other custom name) with SQL command:

ALTER USER your_name identified by 'your_password';
Copy the code

Then you need to change the password again to use MD5 encryption:

ALTER USER your_name identified by 'your_new_password';
Copy the code

Finally, there is an additional step to do: grant permissions to the new user:

GRANT ALL PRIVILEGES TO cj;
Copy the code

Where cj is the user name I created, corresponding to jack above. I created a new database for remote connection, so there is a slight difference in the database name.

An unauthorized operation may return: Permission denied for schema public.

Port clearance

If you are using a local VIRTUAL machine, you can skip this step.

If you use a cloud server like I do, you need to open port 26000.

First, find the change security group in the cloud server console.

Then, create a security group and permit 26000 ports in the inbound direction.

Finally, add the security group:

Navicat connection

Upper-left corner File -> New Connection ->PostgreSQL.

And other database connection is not much different, as required to fill in the IP address, port number, account secret:

The connection effect is as follows: