526互联

KingbaseESV8R6普通用户无权限执行vacuum

发布时间 2023-09-18 13:51:23作者: KINGBASE研究院

背景

数据库日志有如下提示：
WARNING: skipping "pivot_t1" --- only table or database owner can vacuum it
从提示可以看出，有用户对表pivot_t1进行vacuum操作时，出现了权限问题。

测试

TEST=# \c - ud;
You are now connected to database "TEST" as user "ud".
TEST=>
TEST=> vacuum pivot_t1;
WARNING:  skipping "pivot_t1" --- only table or database owner can vacuum it
VACUUM
TEST=> \du
                                    List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+------------
 u1        |                                                            | {role01}
 ud        |                                                            | {role01}

TEST=> \dn
      List of schemas
       Name       | Owner
------------------+--------
 ud               | system

1.把system的权限授予给用户ud

TEST=> \c - system
You are now connected to database "TEST" as user "system".
TEST=# grant system to ud;
GRANT ROLE
TEST=#
TEST=# \c - ud
You are now connected to database "TEST" as user "ud".
TEST=> vacuum pivot_t1;
VACUUM
TEST=> \c - system
You are now connected to database "TEST" as user "system".
TEST=# revoke system from ud;
REVOKE ROLE
TEST=#
TEST=# \c - ud
You are now connected to database "TEST" as user "ud".
TEST=> vacuum pivot_t1;
WARNING:  skipping "pivot_t1" --- only table or database owner can vacuum it
VACUUM

2.更改用户ud为超级用户

TEST=# alter user ud with superuser;
ALTER ROLE
TEST=# \c - ud
You are now connected to database "TEST" as user "ud".
TEST=# vacuum pivot_t1;
VACUUM

查看ud用户权限

TEST=# \du
                                    List of roles
 ud        | Superuser                                                  | {role01}

结论

只有superuser权限的用户可以对表执行vacuum操作，不建议单独授予此权限，此权限应由system超级用户统一管理。
一般常见的业务系统中，autovacuum达到阈值自动触发，无需手工执行vacuum，即使一些场景需要手工执行vacuum，也应由超级用户完成操作。