PostgreSQL：数据库角色

JSON 2024-03-25 16:34:03 139

数据库级角色的权限范围是数据库范围的。角色可以被视为一个数据库用户或一组数据库用户，具体取决于角色的设置方式。角色由用户（通常是管理员）创建，用于将权限（权限是执行特定类型的 SQL 语句的权限，或访问其他用户的对象的权限。）或其他角色组合在一起。接下来sojson给大家举例。

数据库角色

PostgreSQL 使用角色的概念来管理数据库访问权限。您可以使用 CREATE ROLE SQL 命令创建角色。 CREATE ROLE 向 PostgreSQL 数据库集群添加新角色。您必须具有 CREATEROLE 权限或者是数据库超级用户才能使用此命令。

句法：

CREATE ROLE name [ [ WITH ] option [ ... ] ]

参数：

name：新角色的名称。

选项	描述
SUPERUSER NOSUPERUSER	这些选项确定新角色是否是“超级用户”。 NOSUPERUSER 是默认值。
CREATEDB NOCREATEDB	这些选项定义角色创建数据库的能力。 NOCREATEDB 是默认值。
CREATEROLE NOCREATEROLE	这些选项决定是否允许角色创建新角色（即执行 CREATE ROLE）。具有 CREATEROLE 权限的角色还可以更改和删除其他角色。如果未指定，NOCREATEROLE 是默认值。
INHERIT NOINHERIT	这些选项确定角色是否“继承”其所属角色的特权。 INHERIT 是默认值。
LOGIN NOLOGIN	这些选项决定是否允许某个角色登录；即是否可以指定该角色作为客户端连接时的初始会话授权名。 NOLOGIN 是默认值，除非通过其替代拼写 CREATE USER 调用 CREATE ROLE。
REPLICATION NOREPLICATION	这些选项确定是否允许角色启动流复制或使系统进入和退出备份模式。 NOREPLICATION 是默认值。
CONNECTION LIMIT connlimit	如果角色可以登录，则指定角色可以建立多少个并发连接。 -1（默认值）表示没有限制。
PASSWORD password	设置角色的密码。
ENCRYPTED UNENCRYPTED	这些关键字控制密码是否以加密方式存储在系统目录中。
VALID UNTIL 'timestamp'	VALID UNTIL 子句设置一个日期和时间，在此之后角色的密码将不再有效。如果省略此子句，则密码将始终有效。
IN ROLE role_name	IN ROLE 子句列出一个或多个现有角色，新角色将立即作为新成员添加到这些角色中。
IN GROUP role_name	IN GROUP 是 IN ROLE 的过时拼写。
ROLE role_name	ROLE 子句列出一个或多个现有角色，这些角色将自动添加为新角色的成员。
ADMIN role_name	ADMIN 子句与 ROLE 类似，但指定的角色会添加到新角色中，并带有 ADMIN OPTION，赋予它们将此角色的成员资格授予其他人的权利。
USER role_name	USER 子句是 ROLE 子句的过时拼写。
SYSID uid	SYSID 子句将被忽略，但为了向后兼容而被接受。

例子：

创建一个可以登录的角色（无密码）：

创建角色大卫登录；

创建一个带有密码的角色：

创建用户 sojson，密码为“sojosn839#@S”；

创建一个角色，密码有效期到2014年底。2015年一秒后，密码就不再有效。

创建角色 david，登录密码为“ks839#@S”，有效期至“2015-01-01”；

创建角色，可以创建数据库并管理角色：

使用 CREATEDB CREATEROLE 创建角色管理员；

兼容性：

CREATE ROLE 语句符合 SQL 标准，但该标准仅要求语法：

创建角色名称 [WITH ADMIN 角色名称]

多个初始管理员，以及 CREATE ROLE 的所有其他选项都是 PostgreSQL 扩展。

改变角色

ALTER ROLE 命令用于更改 PostgreSQL 角色的属性。

句法：

ALTER ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET ALL

例子：

更改角色的密码：

更改角色 davide，密码为“hu8jmn3”；

删除角色的密码：

更改角色 davide，密码为空；

更改密码到期日期，指定密码应在 2015 年 5 月 4 日中午到期，时区比 UTC 早一小时：

更改角色 chris 有效期至“2015 年 5 月 4 日 12:00:00 +1”；使密码永久有效：

ALTER ROLE fred 有效直到“无穷大”；

赋予角色创建其他角色和新数据库的能力：

ALTER ROLE miriam CREATEROLE CREATEDB;

为角色提供 Maintenance_work_mem 参数的非默认设置：

更改角色worker_bee设置maintenance_work_mem = 100000；

为角色提供非默认、特定于数据库的 client_min_messages 参数设置：

ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;

兼容性：

ALTER ROLE 语句是 PostgreSQL 扩展。

角色属性

数据库角色可以具有许多定义其权限并与客户端身份验证系统交互的属性。

姓名	描述
登录权限	只有具有 LOGIN 属性的角色才能用作数据库连接的初始角色名称。要创建具有登录权限的角色，请使用：句法： CREATE ROLE name LOGIN; CREATE USER name; 注意：CREATE USER 与 CREATE ROLE 等效，只是 CREATE USER 默认假定为 LOGIN，而 CREATE ROLE 则不然。
超级用户状态	数据库超级用户绕过除登录权之外的所有权限检查。这是一个危险的权限，不应谨慎使用；最好以非超级用户的角色来完成大部分工作。
数据库创建	必须明确授予角色创建数据库的权限（超级用户除外，因为它会绕过所有权限检查）。
角色创建	必须明确授予角色创建更多角色的权限（超级用户除外，因为它会绕过所有权限检查）。
启动复制	必须明确授予角色启动流复制的权限（超级用户除外，因为这会绕过所有权限检查）。
密码	仅当客户端身份验证方法要求用户在连接到数据库时提供密码时，密码才有意义。

创建角色后可以使用 ALTER ROLE 修改角色的属性。有关详细信息，请参阅 CREATE ROLE 和 ALTER ROLE 命令的参考页。

角色还可以具有许多运行时配置设置的特定于角色的默认值。例如，如果由于某种原因您想在连接时随时禁用索引扫描，您可以使用：

ALTER ROLE myname SET enable_indexscan TO off；

角色成员资格

将用户分组在一起以简化权限管理通常很方便：这样，可以向整个组授予或撤销权限。在 PostgreSQL 中，这是通过创建代表组的角色，然后将组角色的成员身份授予各个用户角色来完成的。

要设置组角色，首先创建角色：

句法：

CREATE ROLE name;

通常，用作组的角色不会具有 LOGIN 属性，但您可以根据需要进行设置。

一旦组角色存在，您可以使用 GRANT 和 REVOKE 命令添加和删除成员：

将 group_role 授予 role1, ... ;

REVOKE group_role FROM role1, ... ;

您也可以将成员资格授予其他组角色（因为组角色和非组角色之间实际上没有任何区别）。数据库不允许您设置循环成员资格循环。此外，不允许向 PUBLIC 授予角色成员资格。

组角色的成员可以通过两种方式使用该角色的权限。首先，组中的每个成员都可以显式执行 SET ROLE 来暂时“成为”组角色。其次，具有 INHERIT 属性的成员角色自动可以使用其所属角色的特权，包括这些角色继承的任何特权。举个例子，假设我们已经做了：

创建角色大卫登录继承；

创建角色 admin NOINHERIT；

创建角色轮NOINHERIT；

授予大卫管理员权限；

将轮子授予管理员；

以 david 角色连接后，数据库会话将立即使用直接授予 david 的权限以及授予 admin 的任何权限，因为 joe “继承”admin 的权限。但是，授予 Wheel 的权限不可用，因为即使 joe 间接是 Wheel 的成员，但该成员身份是通过具有 NOINHERIT 属性的 admin 获得的。后：

设置角色管理员；

要销毁组角色，请使用 DROP ROLE：

删除角色名称；

组角色中的任何成员身份都会自动撤销（但成员角色不受影响）。