Navicat连接opengauss数据库
一、修改配置文件,设置远程连接
①修改放行ip
listen_addresses修改为*
password_encryption_type打开注释并修改为0(代表MD5加密)
[root@opengauss ~]# vim /opt/huawei/install/data/dn/postgresql.conf
listen_addresses = '*'
password_encryption_type = 0
#行号大概在68和110
[root@opengauss ~]# cat -n /opt/huawei/install/data/dn/postgresql.conf|grep listen_add
68 listen_addresses = '*' # what IP address(es) to listen on;
[root@opengauss ~]# cat -n /opt/huawei/install/data/dn/postgresql.conf|grep password_encr
110 password_encryption_type = 0 #Password storage type, 0 is md5 for PG, 1 is sha256 + md5, 2 is sha256 only
这一步为添加放行IP地址
[root@opengauss ~]# vim /opt/huawei/install/data/dn/pg_hba.conf
[root@opengauss ~]# cat /opt/huawei/install/data/dn/pg_hba.conf|grep 0.0.0.
host all all 0.0.0.0/0 md5
第一个all表示允许连接到任意数据库,第二个all表示任意用户都可以连, 0.0.0.0/0表示任意IP地址,MD5为认证方式。
如果有指定ip地址,可以根据需求修改
重启数据库使修改配置生效
[root@opengauss ~]# su omm
[omm@opengauss root]$ gs_om -t restart
②创建远程链接数据库用户
注:本次创建用户在修改配置之前所以在重启数据库后,需要把之前创建的用户密码修改一遍,使其密码采用MD5认证
[omm@opengauss root]$ gsql -d postgres -p 15400
openGauss=# create user test with password "OUYE@123";
CREATE ROLE
openGauss=# GRANT ALL PRIVILEGES to test;
ALTER ROLE
openGauss=# create database db_tpcc owner test;
CREATE DATABASE
openGauss=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access
privileges
-----------+-------+-----------+---------+-------+--------
-----------
db_tpcc | test | SQL_ASCII | C | C |
postgres | omm | SQL_ASCII | C | C |
template0 | omm | SQL_ASCII | C | C | =c/omm
+
| | | | | omm=CTc
/omm
template1 | omm | SQL_ASCII | C | C | =c/omm
+
| | | | | omm=CTc
/omm
(4 rows)
[omm@opengauss root]$ gsql -d db_tpcc -p 15400 -U test
Password for user test:
db_tpcc=> create table mytable(firstcol int);
CREATE TABLE
db_tpcc=> insert into mytable values (100);
INSERT 0 1
db_tpcc=> select * from mytable;
firstcol
----------
100
(1 row)
db_tpcc=> \q
修改密码
openGauss=# alter role test identified by 'ouye@123' replace 'OUYE@123';
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
ALTER ROLE
二、Navicat链接opengauss
连接方式选择postgres
用户选择刚才创建的远程连接用户
验证