磐维和sharding之用户管理
概念描述
架构sharding+zookeeper+（3主6备）的数据库，业务需要新建用户,通过连接sharding进行增删改查。
测试验证
一、实验环境
| 节点类别 | 主机名 | IP | 端口 | 工具 |
|---|---|---|---|---|
| 主节点a | testa1 | 192.168.3.13 | 3307 | sharding和zookeeper |
| 备节点a | testa2 | 192.168.3.14 | 3307 | - |
| 主节点b | testb1 | 192.168.3.15 | 3307 | sharding和zookeeper |
| 备节点b | testb2 | 192.168.3.16 | 3307 | - |
| 主节点c | testc1 | 192.168.3.17 | 3307 | sharding和zookeeper |
| 备节点c | testc2 | 192.168.3.18 | 3307 | - |
二、实验认证
1、新建个人用户在sharding层还是数据库层
1.1 sharding和数据库都创建
1.1.1 修改sharding的参数文件
authority:
users:
- user: dsg
password: dsg_2023!
- user: dbmt
password: Bcv_1308
privilege:
type: ALL_PERMITTED
1.1.2 在数据库层创建
PanWeiDB=# create user dsg identified by "dsg_2023!";
NOTICE: The encrypted password contains MD5 ciphertext, which is not secure.
CREATE ROLE
PanWeiDB=# alter user dsg Sysadmin;
ALTER ROLE
PanWeiDB=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
dbmt | Sysadmin | {}
dsg | Sysadmin | {}
jiangyue | | {}
ljc | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
ssp | | {}
1.1.3 用dsg用户连接数据库
[omm@testa1 conf]$ gsql -h 192.168.3.17 -p 3307 -U dsg-W Bcv_1308 -d sharding_db
gsql: ERROR: FATAL: unknown username: dsg
[omm@testa1 conf]$
注：经过查询相关文档 ，发现必须重启！！！！！！
1.1.4 重启sharding+zookeeper
注： sharding和 zk 所有节点一起停
1、删除zk的version-2
[omm@bin]$ cd /enmo/zookeeper/
[omm@zookeeper]$ ll
total 12356
-rw-r--r-- 1 omm omm 12649765 Oct 21 12:07 apache-zookeeper-3.7.1-bin.tar.gz
drwxr-xr-x 8 omm omm 202 Oct 21 12:59 zk
[omm@testa1 zookeeper]$ cd zk
[omm@testa1 zk]$ ll
total 44
drwxr-xr-x 2 omm omm 4096 May 7 2022 bin
drwxr-xr-x 2 omm omm 92 Oct 21 12:48 conf
drwxr-xr-x 5 omm omm 4096 May 7 2022 docs
drwxr-xr-x 2 omm omm 4096 Oct 21 12:09 lib
-rw-r--r-- 1 omm omm 11358 May 7 2022 LICENSE.txt
drwx------ 2 omm omm 74 Oct 21 12:59 logs
-rw------- 1 omm omm 2 Oct 21 12:58 myid
-rw-r--r-- 1 omm omm 2084 May 7 2022 NOTICE.txt
-rw-r--r-- 1 omm omm 2214 May 7 2022 README.md
-rw-r--r-- 1 omm omm 3570 May 7 2022 README_packaging.md
drwx------ 2 omm omm 170 Dec 18 17:32 version-2 《《《《《《《《《《《《《
-rw------- 1 omm omm 5 Dec 18 17:31 zookeeper_server.pid
[omm@zk]$ cd version-2/
[omm@version-2]$ ll
total 2928
-rw------- 1 omm omm 1 Dec 18 17:31 acceptedEpoch
-rw------- 1 omm omm 1 Dec 18 17:31 currentEpoch
-rw------- 1 omm omm 67108880 Oct 21 13:11 log.100000001
-rw------- 1 omm omm 67108880 Oct 25 12:58 log.200000001
-rw------- 1 omm omm 67108880 Oct 25 14:23 log.300000001
-rw------- 1 omm omm 67108880 Nov 15 16:56 log.400000001
-rw------- 1 omm omm 67108880 Dec 18 20:07 log.500000001
-rw------- 1 omm omm 598 Oct 21 12:59 snapshot.0
[omm@testa1 version-2]$ rm -rf *
2、停sharding和zk （所有节点）
[omm@bin]$ zkServer.sh stop
/bin/java
ZooKeeper JMX enabled by default
Using config: /enmo/zookeeper/zk/bin/../conf/zoo.cfg
Stopping zookeeper ... STOPPED
[omm@bin]$ cd /enmo/s
shardingsphere/ soft/
[omm@bin]$ cd /enmo/shardingsphere/ssp/bin/
[omm@bin]$ ll
total 16
-rwx------ 1 omm omm 3313 Dec 6 2022 start.bat
-rwx------ 1 omm omm 6419 Dec 6 2022 start.sh
-rwx------ 1 omm omm 1455 Dec 6 2022 stop.sh
[omm@bin]$ cd /enmo/shardingsphere/ssp/bin/
[omm@bin]$ stop.sh
Stopping the ShardingSphere-Proxy ....OK!
PID: 23433
3、起sharding和zk （所有节点）
[omm@bin]$ zkServer.sh start
/bin/java
ZooKeeper JMX enabled by default
Using config: /enmo/zookeeper/zk/bin/../conf/zoo.cfg
Starting zookeeper ... STARTED
[omm@bin]$ start.sh
/bin/java
we find java version: java8, full_version=1.8.0_181, full_path=/bin/java
The classpath is /enmo/shardingsphere/ssp/conf:/enmo/shardingsphere/ssp/conf:.:/enmo/shardingsphere/ssp/lib/*:/enmo/shardingsphere/ssp/ext-lib/*
main class org.apache.shardingsphere.proxy.Bootstrap -1 /enmo/shardingsphere/ssp/conf 0.0.0.0 false
Starting the ShardingSphere-Proxy ... PID: 25411
Please check the STDOUT file: /enmo/shardingsphere/ssp/logs/stdout.log
[omm@bin]$
[omm@bin]$
1.1.5 用dsg用户连接库
[omm@testa1 bin]$ gsql -h 192.168.3.13 -p 3307 -U dsg -W dsg_2023! -d sharding_db
gsql ((PanWeiDB(openGauss) 1.0.0 build 9a7e96bc) compiled at 2022-10-15 20:54:36 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
sharding_db=>
1.2 数据库没有dsg用户
1.2.1 删除dsg用户
PanWeiDB=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------------------------------------------------------------+-----------
dbmt | Sysadmin | {}
dsg | Sysadmin | {}
jiangyue | | {}
ljc | Sysadmin | {}
omm | Sysadmin, Create role, Create DB, Replication, Administer audit, Monitoradmin, Operatoradmin, Policyadmin, UseFT | {}
ssp | | {}
PanWeiDB=# drop user dsg;
DROP ROLE
1.2.2 重启sharding+zookeeper
注： sharding和 zk 所有节点一起停
1、删除zk的version-2
[omm@bin]$ cd /enmo/zookeeper/
[omm@zookeeper]$ ll
total 12356
-rw-r--r-- 1 omm omm 12649765 Oct 21 12:07 apache-zookeeper-3.7.1-bin.tar.gz
drwxr-xr-x 8 omm omm 202 Oct 21 12:59 zk
[omm@zookeeper]$ cd zk
[omm@zk]$ ll
total 44
drwxr-xr-x 2 omm omm 4096 May 7 2022 bin
drwxr-xr-x 2 omm omm 92 Oct 21 12:48 conf
drwxr-xr-x 5 omm omm 4096 May 7 2022 docs
drwxr-xr-x 2 omm omm 4096 Oct 21 12:09 lib
-rw-r--r-- 1 omm omm 11358 May 7 2022 LICENSE.txt
drwx------ 2 omm omm 74 Oct 21 12:59 logs
-rw------- 1 omm omm 2 Oct 21 12:58 myid
-rw-r--r-- 1 omm omm 2084 May 7 2022 NOTICE.txt
-rw-r--r-- 1 omm omm 2214 May 7 2022 README.md
-rw-r--r-- 1 omm omm 3570 May 7 2022 README_packaging.md
drwx------ 2 omm omm 170 Dec 18 17:32 version-2 《《《《《《《《《《《《《
-rw------- 1 omm omm 5 Dec 18 17:31 zookeeper_server.pid
[omm@zk]$ cd version-2/
[omm@version-2]$ ll
total 2928
-rw------- 1 omm omm 1 Dec 18 17:31 acceptedEpoch
-rw------- 1 omm omm 1 Dec 18 17:31 currentEpoch
-rw------- 1 omm omm 67108880 Oct 21 13:11 log.100000001
-rw------- 1 omm omm 67108880 Oct 25 12:58 log.200000001
-rw------- 1 omm omm 67108880 Oct 25 14:23 log.300000001
-rw------- 1 omm omm 67108880 Nov 15 16:56 log.400000001
-rw------- 1 omm omm 67108880 Dec 18 20:07 log.500000001
-rw------- 1 omm omm 598 Oct 21 12:59 snapshot.0
[omm@version-2]$ rm -rf *
2、停sharding和zk （所有节点）
[omm@bin]$ zkServer.sh stop
/bin/java
ZooKeeper JMX enabled by default
Using config: /enmo/zookeeper/zk/bin/../conf/zoo.cfg
Stopping zookeeper ... STOPPED
[omm@bin]$ cd /enmo/s
shardingsphere/ soft/
[omm@bin]$ cd /enmo/shardingsphere/ssp/bin/
[omm@bin]$ ll
total 16
-rwx------ 1 omm omm 3313 Dec 6 2022 start.bat
-rwx------ 1 omm omm 6419 Dec 6 2022 start.sh
-rwx------ 1 omm omm 1455 Dec 6 2022 stop.sh
[omm@bin]$ cd /enmo/shardingsphere/ssp/bin/
[omm@bin]$ stop.sh
Stopping the ShardingSphere-Proxy ....OK!
PID: 23433
3、起sharding和zk （所有节点）
[omm@bin]$ zkServer.sh start
/bin/java
ZooKeeper JMX enabled by default
Using config: /enmo/zookeeper/zk/bin/../conf/zoo.cfg
Starting zookeeper ... STARTED
[omm@bin]$ start.sh
/bin/java
we find java version: java8, full_version=1.8.0_181, full_path=/bin/java
The classpath is /enmo/shardingsphere/ssp/conf:/enmo/shardingsphere/ssp/conf:.:/enmo/shardingsphere/ssp/lib/*:/enmo/shardingsphere/ssp/ext-lib/*
main class org.apache.shardingsphere.proxy.Bootstrap -1 /enmo/shardingsphere/ssp/conf 0.0.0.0 false
Starting the ShardingSphere-Proxy ... PID: 25411
Please check the STDOUT file: /enmo/shardingsphere/ssp/logs/stdout.log
1.2.3 用dsg用户连接库
[omm@bin]$ gsql -h 192.168.3.13 -p 3307 -U dsg -W dsg_2023! -d sharding_db
gsql ((PanWeiDB(openGauss) 1.0.0 build 9a7e96bc) compiled at 2022-10-15 20:54:36 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
sharding_db=>
结论：经过以上实验验证，发现sharding+磐维的架构 新建的用户跟数据库没有关系，只需要在sharding的参数文件中配置即可，但是需要重启!!!。
2、修改个人用户密码
2.1 修改sharding的参数文件
authority:
users:
- user: dsg
password: Bcv_1308
- user: dbmt
password: Bcv_1308
privilege:
type: ALL_PERMITTED
2.2 连接测试
[omm@conf]$
[omm@conf]$ gsql -h 192.168.3.13 -p 3307 -U dsg -W Bcv_1308 -d sharding_db
gsql: ERROR: FATAL: password authentication failed for user "dsg" <<<<
[omm@conf]$ gsql -h 192.168.3.13 -p 3307 -U dsg -W dsg_2023! -d sharding_db
gsql ((PanWeiDB(openGauss) 1.0.0 build 9a7e96bc) compiled at 2022-10-15 20:54:36 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
结论：经过以上实验验证，发现sharding+磐维的架构 修改用户密码需要重启!!!,没有重启之前老密码还是可以用
3、删除dsg个人用户
3.1 修改sharding的参数文件
authority:
users:
- user: dbmt
password: Bcv_1308
privilege:
type: ALL_PERMITTED
3.2 连接测试
[omm@conf]$ gsql -h 192.168.3.13 -p 3307 -U dsg -W dsg_2023! -d sharding_db
gsql ((PanWeiDB(openGauss) 1.0.0 build 9a7e96bc) compiled at 2022-10-15 20:54:36 commit 0 last mr )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
结论：经过以上实验验证，发现sharding+磐维的架构 删除个人用户需要重启!!!,没有重启用户还是可以用
4、用dsg用户连的sharding ，数据库层是哪个用户
1种.直接查看这个current_user
sharding_db=>
sharding_db=>
sharding_db=>
sharding_db=> select * from current_user;
current_user
--------------
dbmt
(1 row)
第二种 ，gsql -r 看数据库里的会话
=# select datname,pid,usename,to_char(query_start,'mm-dd hh24:mi:ss') as query_start,waiting,state,query from pg_stat_activity ;
datname | pid | usename | query_start | waiting | state | query
----------+-----------------+---------+----------------+---------+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
data_312 | 140120623519488 | dbmt | 12-20 09:07:23 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
data_311 | 140120640300800 | dbmt | 12-20 09:07:23 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
data_310 | 140120657082112 | dbmt | 12-20 09:07:10 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
postgres | 140120673863424 | omm | 12-20 09:07:29 | f | active | select datname,pid,usename,to_char(query_start,'mm-dd hh24:mi:ss') as query_start,waiting,state,query from pg_stat_activity ;
data_310 | 140120841709312 | dbmt | 12-20 09:07:23 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
data_311 | 140120707426048 | dbmt | 12-20 09:07:14 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
data_312 | 140120690644736 | dbmt | 12-20 09:07:14 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
data_311 | 140120959186688 | dbmt | 12-20 09:07:10 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
data_310 | 140120606738176 | dbmt | 12-20 09:07:14 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
data_312 | 140120724207360 | dbmt | 12-20 09:07:10 | f | idle | SELECT relname, relnamespace, relkind, reloptions FROM pg_catalog.pg_class WHERE relkind IN ('r','v','m','S','L','f','e','o','') AND relname NOT LIKE 'matvie
wmap\_%' AND relname NOT LIKE 'mlog\_%' AND pg_catalog.pg_table_is_visible(oid)
postgres | 140121009530624 | omm | | f | |
postgres | 140121026311936 | omm | | f | |
发现连接数据库的用户是dbmt用户，这个dbmt用户是数据源的用户
minPoolSize: 1
ds_8: #可配置多个数据源
url: jdbc:opengauss://192.168.3.17:15400,192.168.3.18:15400/data_318?targetServerType=master&loadBalanceHosts=true&connectionExtraInfo=true
username: dbmt
password: Bcv_1308
connectionTimeoutMilliseconds: 30000
idleTimeoutMilliseconds: 60000
maxLifetimeMilliseconds: 1800000
maxPoolSize: 50
minPoolSize: 1
结论：经过以上实验验证，发现sharding+架构 ,个人用户通过sharing连接数据库都是dbmt用户，为以后找出哪个个人用户跑的sql留下隐患
知识总结
无论是新建个人用户、修改个人用户的密码、删除个人用户 ，都需要重启sharding和zookeeper，不能滚动重启，必须所有节点停掉，然后在起来
