PG中的ROLE系列

CREATE ROLE

CREATE ROLE — 定义一个新的数据库角色

大纲

描述

CREATE ROLE向PostgreSQL数据库集簇增加一个新的角色。一个角色是一个实体，它可以拥有数据库对象并且拥有数据库特权。根据一个角色如何被使用，它可以被考虑成一个“用户”、一个“组”或者两者。有关管理用户和认证的信息可以参考第 21 章和第 20 章。要使用这个命令，你必须具有CREATEROLE特权或者成为一个数据库超级用户。

注意角色是定义在数据库集簇层面上的，并且因此在集簇中的所有数据库中都可用。

参数

• name

  新角色的名称。

• SUPERUSER NOSUPERUSER

  这些子句决定新角色是否是一个“超级用户”，它可以越过数据库内的所有访问限制。超级用户状态很危险并且只应该在确实需要时才用。要创建一个新超级用户，你必须自己是一个超级用户。如果没有指定，默认值是NOSUPERUSER。

• CREATEDB NOCREATEDB

  这些子句定义一个角色创建数据库的能力。如果指定了CREATEDB，被定义的角色将被允许创建新的数据库。指定NOCREATEDB将否定一个角色创建数据库的能力。如果没有指定，默认值是NOCREATEDB。

• CREATEROLE NOCREATEROLE

  这些子句决定一个角色是否被允许创建新的角色（也就是执行CREATE ROLE）。一个带有CREATEROLE特权的角色也能修改和删除其他角色。如果没有指定，默认值是NOCREATEROLE。

• INHERIT NOINHERIT

  如果新的角色是其他角色的成员，这些子句决定新角色是否从那些角色中“继承”特权，把新角色作为成员的角色称为新角色的父角色。一个带有INHERIT属性的角色能够自动使用已经被授予给其直接或间接父角色的任何数据库特权。如果没有INHERIT，在另一个角色中的成员关系只会把SET ROLE的能力授予给那个其他角色，只有在这样做后那个其他角色的特权才可用。如果没有指定，默认值是INHERIT。

• LOGIN NOLOGIN

  这些子句决定一个角色是否被允许登录，也就是在客户端连接期间该角色是否能被给定为初始会话认证名称。一个具有LOGIN属性的角色可以被考虑为一个用户。没有这个属性的角色对于管理数据库特权很有用，但是却不是用户这个词的通常意义。如果没有指定，默认值是NOLOGIN，不过当CREATE ROLE被通过CREATE USER调用时默认值会是LOGIN。

• REPLICATION NOREPLICATION

  这些子句决定一个角色是否为复制角色。角色必须具有这个属性（或者成为一个超级用户）才能以复制模式（物理复制或者逻辑复制）连接到服务器以及创建或者删除复制槽。一个具有REPLICATION属性的角色是一个具有非常高特权的角色，并且只应被用于确实需要复制的角色上。如果没有指定，默认值是NOREPLICATION。 您必须是超级用户才能创建具有REPLICATION属性的新角色。

• BYPASSRLS NOBYPASSRLS

  这些子句决定是否一个角色可以绕过每一条行级安全性（RLS）策略。 默认是NOBYPASSRLS。 您必须是超级用户才能创建具有BYPASSRLS属性的新角色。注意 pg_dump 将默认把row_security设置为OFF， 以确保一个表的所有内容被转储出来。如果运行 pg_dump 的用户不具有适当的权限，将会返回一个错误。 但是，超级用户和被转储表的拥有者总是可以绕过 RLS。

  本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

• CONNECTION LIMIT connlimit

  如果角色能登录，这指定该角色能建立多少并发连接。-1（默认值）表示无限制。注意这个限制仅针对于普通连接。预备事务和后台工作者连接都不受这一限制管辖。

• [ ENCRYPTED ] PASSWORD 'password' PASSWORD NULL

  设置角色的口令（口令只对具有LOGIN属性的角色有用，但是不管怎样你还是可以为没有该属性的角色定义一个口令）。如果你没有计划使用口令认证，你可以忽略这个选项。如果没有指定口令，口令将被设置为空并且该用户的口令认证总是会失败。也可以用PASSWORD NULL显式地写出一个空口令。注意指定一个空字符串也将把口令设置为空，但是在PostgreSQL版本10之前是不能这样做的。在早期的版本中，是否可以使用空字符串取决于认证方法和确切的版本，而libpq在任何情况下都将拒绝使用空字符串。为了避免混淆，应该避免指定空字符串。口令总是以加密的方式存放在系统目录中。ENCRYPTED关键词没有实际效果，它只是为了向后兼容性而存在。加密的方法由配置参数password_encryption决定。如果当前的口令字符串已经是MD5加密或者SCRAM加密的格式，那么不管password_encryption的值是什么，口令字符串还是原样存储（因为系统无法解密以不同格式加密的口令字符串）。这种方式允许在转储/恢复时重载加密的口令。

• VALID UNTIL 'timestamp'

  VALID UNTIL机制设置一个日期和时间，在该时间点之后角色的口令将会失效。如果这个子句被忽略，那么口令将总是有效。

• IN ROLE role_name

  IN ROLE子句列出一个或多个现有的角色，新角色将被立即作为新成员加入到这些角色中（注意没有选项可以把新角色作为一个管理员加入，需要用一个单独的GRANT命令来完成）。

• IN GROUP role_name

  IN GROUP是IN ROLE的一种已废弃的拼写方式。

• ROLE role_name

  ROLE子句列出一个或者多个现有角色，它们会被自动作为成员加入到新角色中（这实际上新角色变成了一个“组”）。

• ADMIN role_name

  ADMIN子句与ROLE相似，但是被提及的角色被使用WITH ADMIN OPTION加入到新角色中，让它们能够把这个角色中的成员关系授予给其他人。

• USER role_name

  USER子句是ROLE子句的一个已废弃的拼写方式。

• SYSID uid

  SYSID子句会被忽略，但是会为了向后兼容，还是会接受它。

注解

使用ALTER ROLE来更改一个角色的属性，以及使用DROP ROLE来移除一个角色。所有用CREATE ROLE指定的属性可以被后来的ALTER ROLE命令所修改。

增加和移除组角色成员的最佳方式是使用GRANT和REVOKE。

VALID UNTIL子句只为一个口令而不是为一个角色本身定义了一个过期时间。特别地，当使用一个非基于口令认证的方法登录时，过期时间是不会被强制的。

INHERIT属性管理可授予特权（也就是对数据库对象和角色成员关系的访问特权）的继承性。它并不适用于由CREATE ROLE和ALTER ROLE设置的特殊角色属性。例如，作为具有CREATEDB特权的角色的一个成员，并不会立刻授予创建数据库的角色，即便INHERIT被设置也是如此，在创建一个数据库之前必须通过SET ROLE成为该角色。

INHERIT属性是用于向后兼容原因的默认值：在早前的PostgreSQL发布中，用户总是能够访问其所属组的所有特权。不过，NOINHERIT更加接近于 SQL 标准中指定的语义。

要小心CREATEROLE特权。对于一个CREATEROLE角色的特权没有继承的概念。那意味着即使一个角色没有特定的特权但被允许创建其他角色，它可以轻易地创建与自身特权不同的另一个角色（除了创建具有超级用户特权的角色）。例如，如果角色“user”具有CREATEROLE特权但是没有CREATEDB特权，但是它能够创建一个带有CREATEDB特权的新角色。因此，可以把具有CREATEROLE特权的角色看成是准超级用户角色。

PostgreSQL包括一个程序createuser，它具有和CREATE ROLE相同的功能（事实上，它会调用这个命令），但是它可以从命令 shell 中运行。

CONNECTION LIMIT只被近似地强制，如果两个新会话在几乎相同时间被开始，而此时该角色只剩下刚好一个连接“槽”，两者可能都将失败。还有，该限制从不对超级用户强制。

用这个命令指定一个非加密口令时必须加以注意。该命令将被以明文的形式传输到服务器，并且它也可能被记录在客户端命令历史或者服务器日志中。不过，命令createuser会传输加密的口令。还有，psql包含一个命令\password，它可以被用来安全地改变该口令。

例子

创建一个能登录但是没有口令的角色：

创建一个有口令的角色：

（CREATE USER和CREATE ROLE完全相同，除了它带有LOGIN）。

创建一个角色，它的口令有效期截止到 2004 年底。在进入 2005 年第一秒时，该口令会失效。

创建一个能够创建数据库并且管理角色的角色：

兼容性

SQL 标准中有CREATE ROLE语句，但是标准只要求语法

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

SQL 标准定义了用户和角色的概念，但是它把它们看成是可区分的概念并且将定义用户的所有命令留给每一种数据库实现来指定。在PostgreSQL中，我们已经选择把用户和角色统一成一种单一实体类型。因此角色比起标准中拥有更多可选的属性。

SQL 标准指定的行为可以通过给用户NOINHERIT属性来得到最大的金丝，而角色会被给予INHERIT属性。

ALTER ROLE

ALTER ROLE — 更改一个数据库角色

大纲

描述

ALTER ROLE更改一个 PostgreSQL角色的属性。

前面列出的这个命令的第一种变体能够更改CREATE ROLE中 指定的很多角色属性（覆盖了所有可能的属性，不过没有增加和移除成员关系的选项， 如果要增加和移除成员关系可使用GRANT和 REVOKE）。该命令中没有提到的属性保持它们之前的设置。 数据库超级用户能够更改任何角色的任何这些设置。具有CREATEROLE 特权的角色能够更改除SUPERUSER，REPLICATION和 BYPASSRLS外的任何这些设置，但是只能为非超级用户和非复制角色修改。普通 角色只能更改它们自己的口令。

第二种变体更改该角色的名称。数据库超级用户能重命名任何角色。具有 CREATEROLE特权的角色能够重命名任何非超级用户角色。当前的会话 用户不能被重命名（如果需要这样做，请以一个不同的用户连接）。由于 MD5加密的口令使用角色名作为 salt，因此如果一个角色的口令是 MD5加密的，重命名该角色会清空其口令。

其余的变体用于更改一个角色的配置变量的会话默认值，可以为所有数据库设置，或者 只为IN DATABASE中指定的数据库设置。如果指定的是 ALL而不是一个角色名，将会为所有角色更改该设置。把 ALL和IN DATABASE一起使用实际上和使用命 令ALTER DATABASE ... SET ...相同。

只要改角色后续开始一个新会话，指定的值将会成为该会话的默认值，并且会覆盖 postgresql.conf中存在的值或者从 postgres命令行收到的值。这只在登录时发生，执行 SET ROLE或者 SET SESSION AUTHORIZATION不会导致新的配置值被设置。 对于所有数据库设置的值会被附加到一个角色的数据库相关的设置所覆盖。特定数 据库或角色的设置会覆盖为所有角色所作的设置。

超级用户能够更改任何人的会话默认值。具有CREATEROLE特权的角色 能够更改非超级用户的默认值。普通角色只能为它们自己设置默认值。某些配置变量 不能以这种方式设置，或者只能由一个超级用户发出的命令设置。只有超级用户能够 更改所有角色在所有数据库中的设置。

参数

• name

  要对其属性进行修改的角色的名称。

• CURRENT_USER

  修改当前用户而不是一个显式标识的角色。

• SESSION_USER

  修改当前会话用户而不是一个显式标识的角色。

• SUPERUSER NOSUPERUSER CREATEDB NOCREATEDB CREATEROLE NOCREATEROLE INHERIT NOINHERIT LOGIN NOLOGIN REPLICATION NOREPLICATION BYPASSRLS NOBYPASSRLS CONNECTION LIMIT connlimit [ ENCRYPTED ] PASSWORD 'password' PASSWORD NULL VALID UNTIL 'timestamp'

  这些子句修改原来有CREATE ROLE 设置的属性。更多信息请见 CREATE ROLE参考页。

• new_name

  该角色的新名称。

• database_name

  要在其中设置该配置变量的数据库名称。

• configuration_parameter value

  把这个角色的指定配置参数的会话默认值设置为给定值。如果 value为DEFAULT 或者等效地使用了RESET，角色相关的变量 设置会被移除，这样该角色将会在新会话中继承系统范围的默认 设置。使用RESET ALL可清除所有角色相关的 设置。SET FROM CURRENT可以把会话中该参数的 当前值保存为角色相关的值。如果指定了 IN DATABASE，只会为给定的角色和数据库 设置或者移除该配置参数。角色相关的变量设置只在登录时生效， SET ROLE以及 SET SESSION AUTHORIZATION不会处理角色 相关的变量设置。关于允许的参数名称和值详见SET和 第 19 章。

注解

使用CREATE ROLE增加新角色，使用 DROP ROLE移除一个角色。

ALTER ROLE无法更改一个角色成员关系。 可以使用GRANT和 REVOKE来实现。

在使用这个命令指定一个未加密口令时要多加小心。该口令将会以明文 传送到服务器，并且它还可能会被记录在客户端的命令历史或者服务器 日志中。psql包含了一个命令 \password，它可以被用来更改一个角色 的口令而不暴露明文口令。

也可以把一个会话默认值绑定到一个指定的数据库而不是一个角色，详见 ALTER DATABASE。如果出现冲突，数据库角色相关 的设置会覆盖角色相关的设置，角色相关的又会覆盖数据库相关的设置。

示例

更改一个角色的口令：

移除一个角色的口令：

更改一个口令的失效日期，指定该口令应该在 2015 年 5 月 4 日中午 （在一个比UTC快 1 小时的时区）过期：

让一个口令永远有效：

让一个角色能够创建其他角色和新的数据库：

为一个角色指定 maintenance_work_mem参数的非默认设置：

为一个角色指定 client_min_messages参数的数据库相关的非 默认设置：

兼容性

ALTER ROLE语句是一个 PostgreSQL扩展。

DROP ROLE

DROP ROLE — 移除一个数据库角色

大纲

描述

DROP ROLE移除指定的角色。要删除一个 超级用户角色，你必须自己就是一个超级用户。要删除一个非超级用户角 色，你必须具有CREATEROLE特权。

如果一个角色仍然被集簇中任何数据库中引用，它就不能被移除。如果尝试 移除将会抛出一个错误。在删除该角色前，你必须删除（或者重新授予所有 权）它所拥有的所有对象并且收回该已经授予给该角色的在其他对象上的特 权。REASSIGN OWNED和DROP OWNED 命令可以用于这个目的。更多讨论请见第 21.4 节。

不过，没有必要移除涉及该角色的角色成员关系。 DROP ROLE会自动收回目标角色在其他角色中的成员 关系，以及其他角色在目标角色中的成员关系。其他角色不会被删除也不 会被影响。

参数

• IF EXISTS

  如果该角色不存在则不要抛出一个错误，而是发出一个提示。

• name

  要移除的角色的名称。

注解

PostgreSQL包括一个程序dropuser具有和这个命令完全相同的功能（事实 上它会调用这个命令），但是该程序可以从 shell 运行。

示例

要删除一个角色：

兼容性

SQL 标准定义了DROP ROLE， 但是它只允许一次删除一个角色并且它指定了和 PostgreSQL不同的特权需求。

SET ROLE

SET ROLE — 设置当前会话的当前用户标识符

大纲

描述

这个命令把当前 SQL 会话的当前用户标识符设置为 role_name。 角色名可以写成一个标识符或者一个字符串。在 SET ROLE之后，对 SQL 命令的权限检查时就 好像该角色就是原先登录的角色一样。

当前会话用户必须是指定的 角色role_name 的一个成员（如果会话用户是一个超级用户，则可以选择任何角色）。

SESSION和LOCAL修饰符发挥的作用和 常规的SET命令一样。

NONE和RESET形式把当前用户标识符 重置为当前会话用户标识符。这些形式可以由任何用户执行。

注解

使用这个命令可以增加特权或者限制特权。如果会话用户角色具有 INHERIT属性，则它会自动具有它能 SET ROLE到的所有角色的全部特权。在这种情况下 SET ROLE实际会删除所有直接分配给会话用户的特 权以及分配给会话用户作为其成员的其他角色的特权，只留下所提及 角色可用的特权。换句话说，如果会话用户没有 NOINHERIT属性，SET ROLE会删除 直接分配给会话用户的特权而得到所提及角色可用的特权。

特别地，当一个超级用户选择SET ROLE到一个非 超级用户角色时，它们会丢失其超级用户特权。

SET ROLE的效果堪比 SET SESSION AUTHORIZATION，但是涉及的特权检查 完全不同。还有，SET SESSION AUTHORIZATION决定 后来的SET ROLE命令可以使用哪些角色， 不过用 SET ROLE更改角色并不会改变后续 SET ROLE能够使用的角色集。

SET ROLE不会处理角色的ALTER ROLE 设置指定的会话变量。这只在登录期间发生。

SET ROLE不能在一个 SECURITY DEFINER函数中使用。

示例

兼容性

PostgreSQL允许标识符 语法（"*rolename*"），而 SQL 标准要求 角色名被写成字符串。SQL 不允许在事务中使用这个命令，而 PostgreSQL并不做此限 制，因为并没有原因需要这样做。和RESET语法 一样，SESSION和 LOCAL修饰符是一种 PostgreSQL扩展。

另见

SET SESSION AUTHORIZATION

参考

http://postgres.cn/docs/13/sql-alterrole.html