南大通用GBase 8a进程信息show processlist里的Host内容显示百分号%的原因

GBase 8a通过show processlist得到当前节点的连接信息,本文介绍host列的信息,包括带%百分号,主机名,IP加端口的。其中显示百分号%是因为创建的存储过程的用户,是允许任意主机连接,而当前用户又不是创建者时才显示%。

显示localhost

没有端口信息,表示是通过socket方式连接的本地客户端。

gbase> show processlist;
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+
| Id  | User            | Host      | db   | Command | Time  | State                       | Info             |
+-----+-----------------+-----------+------+---------+-------+-----------------------------+------------------+
|   1 | event_scheduler | localhost | NULL | Daemon  | 11232 | Waiting for next activation | NULL             |
| 534 | gbase           | localhost | NULL | Query   |     0 | NULL                        | show processlist |
+-----+-----------------+-----------+

复现方法是不加-h参数,用本地socket登录。当然如果需要密码的化,还是要输入的。

[gbase@gbase_rh7_001 ~]$ gccli

可以在本机查看进程,比如

[root@gbase_rh7_001 ~]# ps -ef|grep gccli
gbase    20066  2661  0 12:02 pts/0    00:00:00 gccli -ugbase -pxxxxxxxxxxxxx
root     20131 20089  0 12:02 pts/1    00:00:00 grep --color=auto gccli
[root@gbase_rh7_001 ~]# 

显示IP和端口

其中10.0.2.102:42014中的10.0.2.102是连接发起端的IP, 42014是发起端连接端口

gbase> show processlist;
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| Id  | User            | Host             | db     | Command | Time  | State                       | Info             |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
|   1 | event_scheduler | localhost        | NULL   | Daemon  | 12340 | Waiting for next activation | NULL             |
| 576 | root            | localhost        | testdb | Query   |     0 | NULL                        | show processlist |
| 577 | root            | 10.0.2.102:42014 | NULL   | Sleep   |    26 |                             | NULL             |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
3 rows in set (Elapsed: 00:00:00.00)

复现如下

[gbase@gbase_rh7_001 ~]$ gccli  -h10.0.2.101

,如果是本机,还可用 -h127.0.0.1.

[gbase@gbase_rh7_001 ~]$ gccli  -h127.0.0.1

GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

gbase> show processlist;
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| Id  | User            | Host             | db     | Command | Time  | State                       | Info             |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
|   1 | event_scheduler | localhost        | NULL   | Daemon  | 12583 | Waiting for next activation | NULL             |
| 576 | root            | localhost        | testdb | Sleep   |   209 |                             | NULL             |
| 579 | root            | 10.0.2.102:42016 | NULL   | Sleep   |   219 |                             | NULL             |
| 584 | root            | 127.0.0.1:40108  | NULL   | Query   |     0 | NULL                        | show processlist |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
4 rows in set (Elapsed: 00:00:00.00)

排查方法

去IP的主机,用lsof -I:端口查找对应的进程

[gbase@gbase_rh7_001 gbase]$ gccli -uroot testdb -h10.0.2.101

GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

gbase> show processlist;
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| Id  | User            | Host             | db     | Command | Time  | State                       | Info             |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
|   1 | event_scheduler | localhost        | NULL   | Daemon  | 13023 | Waiting for next activation | NULL             |
| 589 | root            | 10.0.2.101:60452 | testdb | Query   |     0 | NULL                        | show processlist |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.00)

101机器上可以看到是gccli连接的。


[gbase@gbase_rh7_001 ~]$ lsof -i:60452
COMMAND     PID  USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
gclusterd  2726 gbase   78u  IPv6 8055325      0t0  TCP gbase_rh7_001:5258->gbase_rh7_001:60452 (ESTABLISHED)
gccli     31684 gbase    3u  IPv4 8055324      0t0  TCP gbase_rh7_001:60452->gbase_rh7_001:5258 (ESTABLISHED)
[gbase@gbase_rh7_001 ~]$

显示百分号%

目前已知的可能是编写了存储过程,在其定义里加入了用户和主机的声明。如果没显示指定,则默认的用户的权限是%(任何主机都能访问),则也会自动生成%。

比如

DELIMITER $$

DROP PROCEDURE IF EXISTS `bizarre` $$
CREATE DEFINER=`gbase`@`%` PROCEDURE `bizarre`()
BEGIN

show processlist;

END $$

DELIMITER ;

自动生成的例子

[gbase@gbase_rh7_001 gbase]$ gccli -uroot testdb -h10.0.2.101

GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

gbase> DELIMITER $$
gbase> DROP PROCEDURE IF EXISTS `testhost` $$
Query OK, 0 rows affected, 1 warning (Elapsed: 00:00:00.00)

gbase> CREATE PROCEDURE `testhost`()
    -> BEGIN
    -> show processlist;
    -> END $$
Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> DELIMITER ;
gbase> show create procedure testhost;
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                                                                                                            | Create Procedure                                                             | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| testhost  | PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PAD_CHAR_TO_FULL_LENGTH | CREATE DEFINER="root"@"%" PROCEDURE "testhost"()
BEGIN
show processlist;
END | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> call testhost();
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
| Id  | User            | Host             | db     | Command | Time  | State                       | Info             |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
|   1 | event_scheduler | localhost        | NULL   | Daemon  | 13186 | Waiting for next activation | NULL             |
| 589 | root            | 10.0.2.101:60452 | testdb | Query   |     0 | NULL                        | show processlist |
| 592 | root            | 10.0.2.101:60492 | testdb | Sleep   |    19 |                             | NULL             |
+-----+-----------------+------------------+--------+---------+-------+-----------------------------+------------------+
3 rows in set (Elapsed: 00:00:00.00)

Query OK, 0 rows affected (Elapsed: 00:00:00.00)

如上运行存储过程的用户是定义的用户,显示的是正常的主机名或IP+端口。

如果运行存储过程的用户,不是代码里定义的用户,例子里是gbase,运行时,显示的就是%。

注意如下例子中的3次show processlist. 第1和第3是当前用户执行的,显示为localhost, 而第二个是通过当前root用户,执行了一个定义为 'gbase'@'%'的存储过程输出的,显示为%。

[gbase@gbase_rh7_001 gbase]$ gccli -uroot testdb

GBase client 9.5.2.44.1045e3118. Copyright (c) 2004-2022, GBase.  All Rights Reserved.

gbase> show processlist;
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| Id  | User            | Host      | db     | Command | Time  | State                       | Info             |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
|   1 | event_scheduler | localhost | NULL   | Daemon  | 12844 | Waiting for next activation | NULL             |
| 588 | root            | localhost | testdb | Query   |     0 | NULL                        | show processlist |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> call bizarre();
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| Id  | User            | Host      | db     | Command | Time  | State                       | Info             |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
|   1 | event_scheduler | localhost | NULL   | Daemon  | 12845 | Waiting for next activation | NULL             |
| 588 | gbase           | %         | testdb | Query   |     0 | NULL                        | show processlist |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.01)

Query OK, 0 rows affected (Elapsed: 00:00:00.01)

gbase> show processlist;
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
| Id  | User            | Host      | db     | Command | Time  | State                       | Info             |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
|   1 | event_scheduler | localhost | NULL   | Daemon  | 12849 | Waiting for next activation | NULL             |
| 588 | root            | localhost | testdb | Query   |     0 | NULL                        | show processlist |
+-----+-----------------+-----------+--------+---------+-------+-----------------------------+------------------+
2 rows in set (Elapsed: 00:00:00.00)

总结

数据库用户,允许所有IP可以连接,还是存在一定的安全隐患,建议生产环境,通过hosts参数,限定可以连接的IP,请参考

GBase 8a基于主机IP的用户白名单(allowlist)功能