如何让PostgreSQL自定义角色拥有创建索引的权限？

问题背景 #

以超级用户postgres身份执行以下SQL创建了数据库角色customrole：

CREATE USER customrole WITH PASSWORD 'mypassword';

-- 撤销所有角色在public schema的CREATE权限
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- 撤销所有用户在数据库mydb的所有权限
REVOKE ALL ON DATABASE mydb FROM PUBLIC;

-- 授予customrole连接mydb的权限
GRANT CONNECT ON DATABASE mydb TO customrole;

-- 授予customrole使用public schema的权限
GRANT USAGE ON SCHEMA public TO customrole;

-- 授予customrole在public schema的CREATE权限
GRANT CREATE ON SCHEMA public to customrole;
-- 授予customrole在数据库mydb的CREATE权限
GRANT CREATE ON DATABASE mydb to customrole;

-- 授予customrole在public schema所有现有表的全部权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO customrole;

-- 为masteruser设置public schema的默认权限
ALTER DEFAULT PRIVILEGES FOR USER masteruser IN SCHEMA public
GRANT ALL PRIVILEGES ON TABLES TO customrole;

尝试使用customrole执行索引创建语句：

create index my_idx on mytable (mycolumn);

未将表所有者改为customrole时，会触发报错：

Error : SQL Error [42501]: ERROR: must be owner of table mytable

解决方案 #

不需要变更表所有者，通过授予INDEX权限即可让customrole拥有在现有及未来表上创建索引的权限：

1. 为现有表授予创建索引权限 #

以超级用户执行以下SQL，直接为public schema下的所有现有表授予INDEX权限：

GRANT INDEX ON ALL TABLES IN SCHEMA public TO customrole;

注：你之前执行的GRANT ALL PRIVILEGES ON ALL TABLES理论上已包含INDEX权限，若仍报错，可能是权限未正确生效，显式授予INDEX权限更可靠。

2. 为未来表配置默认创建索引权限 #

确保masteruser后续创建的表，customrole自动获得创建索引的权限：

ALTER DEFAULT PRIVILEGES FOR USER masteruser IN SCHEMA public
GRANT INDEX ON TABLES TO customrole;

若需要保留全权限，也可以继续使用GRANT ALL PRIVILEGES，因为ALL PRIVILEGES已包含INDEX权限。

原理说明 #

PostgreSQL中，创建索引的权限要求为以下二者之一：

• 是目标表的所有者
• 拥有目标表的INDEX权限

通过显式授予INDEX权限，无需变更表所有权即可满足创建索引的权限要求。

内容的提问来源于stack exchange，提问作者Subhajit