循序渐进丨免密登录 openGauss / MogDB 的几种方式

gsql
gsql ((MogDB 3.1.1.100 build 501a8955) compiled at 2023-07-03 19:59:26 commit 0 last mr 1796 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Last login: 2023-08-16 15:08:17.350982+08 from gs_clean@::1
Type "help" for help.


MogDB=#
2
本地非初始用户免密登录

• 初始用户是 ommxxx

• 其他操作系统用户数testxxx，为了安全，此用户和初始用户在同一操作系统用户组下

• unix_socket目录为 data/xxx/tmp

• 监听端口为 44000

#postgresql.conf
...
unix_socket_directory=/data/xxx/tmp
unix_socket_permision=0770
...

chmod 0770 data/xxx/tmp

2.2 配置pg_hba.conf

去掉local … trust；

增加local … peer map=mymap，当然，这里的mymap是可变的，和下一个修改对应即可：

#pg_hba.conf
...
#local   all             all                                     trust
local   all             all                                     peer map=mymap
...

2.3 配置pg_ident.conf

# pg_ident.conf
# MAPNAME       SYSTEM-USERNAME         PG-USERNAME
mymap   ommxxx ommxxx
mymap   testxxx ommxxx

2.4 重启数据库

gs_ctl restart

2.5 其他操作系统用户即可免密登录

[testxxx@node156 ~]$ gsql -r -h /data/xxx/tmp -p 44000 -Uommxxx -d postgres
gsql ((MogDB 3.1.1.100 build 501a8955) compiled at 2023-07-03 19:59:26 commit 0 last mr 1796 )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Last login: 2023-08-16 15:28:07.131802+08 from gs_clean@::1
Type "help" for help.

• 需免密登录用户为 testssl

• 客户端ip为 172.16.3.250

• 服务器ip为 172.16.3.156

• 数据监听端口为 61234

3.1 生成SSL认证文件

cp $GAUSSHOME/share/sslcert/gsql/openssl.cnf .

openssl req -new -x509 -days 9999 -nodes -config openssl.cnf \
-out CA.crt -keyout CA.key -subj "/CN=CA"

openssl req -new -nodes -text -config openssl.cnf \
-out server.csr -keyout server.key -subj "/CN=server"
openssl x509 -req -text -days 9999 -CA CA.crt -CAkey CA.key -CAcreateserial \
-in server.csr -out server.crt

openssl req -new -nodes -text -config openssl.cnf  \
-out testssl.csr -keyout testssl.key -subj "/CN=testssl"
openssl x509 -req -text -days 9999 -CA CA.crt -CAkey CA.key -CAcreateserial \
-in testssl.csr  -out testssl.crt

3.2 数据库创建对应用户

gsql -r -c "create user testssl password 'test@123';"

3.3 修改数据库参数

# postgresql.conf
ssl=on
ssl_cert_file= 'server.crt'
ssl_key_file= 'server.key'
ssl_ca_file='CA.crt'

# pg_hba.conf
...
hostssl all all 172.16.3.250/32 cert

gs_ctl restart

3.4 客户端可通过SSL认证文件免密登录

gsql "sslrootcert=/tmp/CA.key sslcert=/tmp/testssl.crt sslkey=/tmp/testssl.key" \
-h 172.16.3.156 -p 61234 -U testssl

export PGSSLROOTCERT=/tmp/CA.crt
export PGSSLCERT=/tmp/testssl.crt
export PGSSLKEY=/tmp/testssl.key
gsql  -h172.16.3.156 -p61234 -Utestssl
gsql ((MogDB 3.0.3 build 23ba838d) compiled at 2022-10-22 09:50:39 commit 0 last mr  )
SSL connection (cipher: ECDHE-RSA-AES128-GCM-SHA256, bits: 128)
Type "help" for help.