createrole_self_grant

• 19
• 18
• 17
• 16

在此 createrole_self_grant 的用法示例中，我们首先创建一个拥有角色管理权限的角色“alice”。

postgres=# CREATE ROLE alice LOGIN CREATEROLE;
CREATE ROLE

如果 alice 随后创建另一个角色，默认情况下，在没有额外的 GRANT 语句的情况下，他们将无法为此角色使用 SET ROLE。

postgres=# \c - alice
You are now connected to database "postgres" as user "alice".

postgres=> CREATE ROLE bob LOGIN;
CREATE ROLE
postgres=> \drg
            List of role grants
 Role name | Member of | Options | Grantor  
-----------+-----------+---------+----------
 alice     | bob       | ADMIN   | postgres
(1 row)

postgres=> SET ROLE bob;
ERROR:  permission denied to set role "bob"

postgres=> GRANT bob TO alice WITH SET OPTION;
GRANT ROLE

postgres=> SET ROLE bob;
SET

通过将 createrole_self_grant 设置为 set，可以避免额外的 GRANT 步骤。

postgres=> DROP ROLE bob;
DROP ROLE

postgres=> SET createrole_self_grant = 'SET';
SET

postgres=> CREATE ROLE bob LOGIN;
CREATE ROLE

postgres=> \drg
            List of role grants
 Role name | Member of | Options | Grantor  
-----------+-----------+---------+----------
 alice     | bob       | SET     | alice
 alice     | bob       | ADMIN   | postgres
(2 rows)

postgres=> SET ROLE bob;
SET

类似地，inherit 选项也消除了对显式 GRANT ... WITH INHERIT OPTION 的需求。

postgres=> SET createrole_self_grant = 'INHERIT';
SET

postgres=> CREATE ROLE bob LOGIN;
CREATE ROLE

postgres=> \drg
            List of role grants
 Role name | Member of | Options | Grantor  
-----------+-----------+---------+----------
 alice     | bob       | INHERIT | alice
 alice     | bob       | ADMIN   | postgres
(2 rows)

• PostgreSQL 文档： createrole_self_grant