PostgreSQL中授予VACUUM,ANALYZE

什么是授予VACUUM,ANALYZE?

PostgreSQL使用表VACCUM和ANALYZE命令来优化数据库。VACCUM命令回收存储空间并使其可以重用。它还具有更新可视化映射，这个映射主要用于帮助查询快速识别哪部分的表有实际的活动记录。ANALYZE命令则收集表的统计信息，用于帮助执行计划器决定最有效率的执行计划。

为什么VACUUM,ANALYZE需要授予？

目前，一个表只能由其所有者或超级用户进行VACUUM或ANALYZE。由于Nathan Bossart的补丁，现在可以通过适当的 GRANT将其扩展到任何用户。

Nathan还实施了另一个补丁，它提供了两个新的预定义角色：“pg_vacuum_all_tables”和“pg_analyze_all_tables”。

被授予这些角色特权的角色可以像超级用户一样分别对任何或所有表执行VACUUM或 ANALYZE。这消除了将超级用户权限授予角色的需要，以便他们可以执行vacuum和/或ANALYZE。

这两个补丁均由 Andrew Dunstan 提交，并由 Bharath Rupireddy、Kyotaro Horiguchi、Stephen Frost、Robert Haas、Mark Dilger、Tom Lane、Corey Huinker、David G. Johnston 和 Michael Paquier 审核。

怎样授权VACUUM和ANALYZE权限？

举例说明

$ sudo sudo -u postgres psql test
psql (16devel)
Type "help" for help.
 
test=> SHOW server_version;
 server_version 
----------------
 16devel
(1 row)
 
test=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 
test=# create role stepan;
CREATE ROLE
 
test=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 stepan    | Cannot login                                               | {}
 
test=# create table foo();
CREATE TABLE
 
test=# \dt foo
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | foo  | table | postgres
(1 row)
 
test=# vacuum foo;
VACUUM
 
test=# set role stepan;
SET
 
test=> vacuum foo;
WARNING:  permission denied to vacuum "foo", skipping it
VACUUM
 
test=> reset role;
RESET
 
test=# grant vacuum on table foo to stepan;
GRANT
 
test=# set role stepan;
SET
 
test=> vacuum foo;
VACUUM
 
test=> reset role;
RESET
 
test=# create role petro;
CREATE ROLE
 
test=# grant pg_vacuum_all_tables to petro;
GRANT ROLE
 
test=# set role petro;
SET
 
test=> vacuum foo;
VACUUM

下面是上述 SQL 代码的作用：

1. 连接到名为"test"的新数据库。
2. 作为"postgres"用户创建一个名为"stepan"的新角色和一个名为"foo"的新表。
3. 尝试"stepan"用户VACUUM"foo"表。 输出显示，在我们将"foo"表的vacuum权限授予"stepan"用户之前，"stepan"无法对"foo"表进行vacuum。
4. 使用"postgres"用户再创建一个名为"petro"的新用户。
   将"pg_vacuum_all_tables"角色授予"petro"。
   现在我们可以看到"petro"可以清理"foo"表（以及所有数据库中的所有其他表！），即使我们没有将"foo"上的清理授予"petro"角色。petro用户可以清理 "foo"表的原因是因为"petro"用户是 "pg_vacuum_all_tables"角色的成员。

总结

VACUUM和ANALYZE是用于优化数据库的 PostgreSQL命令。 GRANT VACUUM, ANALYZE 可用于将清理和分析表的权限扩展到任何用户。这也可以通过授予用户预定义的角色来完成，例如"pg_vacuum_all_tables"或"pg_analyze_all_tables"。这消除了将超级用户权限授予角色的需要，以便他们可以执行 vacuum和/或分析。

查看我们关于清理和分析表的其他博客文章。

原文标题：GRANT VACUUM, ANALYZE IN POSTGRESQL 16
原文作者：Pavlo Golub
原文地址：https://www.cybertec-postgresql.com/en/grant-vacuum-analyze-postgresql-16/