Postgresql 中文操作指南

22.4. Dropping Roles #

由于角色可以拥有数据库对象并可以持有访问其他对象的权限，因此删除角色通常不仅仅是 DROP ROLE 的问题。角色拥有的任何对象都必须首先删除或重新分配给其他所有者；并且授予该角色的所有权限都必须被撤销。

Because roles can own database objects and can hold privileges to access other objects, dropping a role is often not just a matter of a quick DROP ROLE. Any objects owned by the role must first be dropped or reassigned to other owners; and any permissions granted to the role must be revoked.

可以使用 ALTER 命令一次一个地转移对象的所有权，例如：

Ownership of objects can be transferred one at a time using ALTER commands, for example:

ALTER TABLE bobs_table OWNER TO alice;

另外， REASSIGN OWNED 命令可用于将要删除的角色所拥有所有对象的所有权重新分配给单一角色。因为 REASSIGN OWNED 无法访问其他数据库中的对象，因此必须在包含该角色所拥有对象的每个数据库中运行它。（注意，第一个此类 REASSIGN OWNED 将更改由要删除的角色所拥有的任何跨数据库共享的对象即数据库或表空间的所有权。）

Alternatively, the REASSIGN OWNED command can be used to reassign ownership of all objects owned by the role-to-be-dropped to a single other role. Because REASSIGN OWNED cannot access objects in other databases, it is necessary to run it in each database that contains objects owned by the role. (Note that the first such REASSIGN OWNED will change the ownership of any shared-across-databases objects, that is databases or tablespaces, that are owned by the role-to-be-dropped.)

一旦所有有价值的对象转移给新所有者，就可以使用 DROP OWNED 命令删除要删除的角色所拥有剩下的任何对象。同样，此命令无法访问其他数据库中的对象，因此必须在包含该角色所拥有对象的每个数据库中运行它。另外， DROP OWNED 不会删除整个数据库或表空间，因此，如果角色拥有尚未转移给新所有者的任何数据库或表空间，则必须手动进行操作。

Once any valuable objects have been transferred to new owners, any remaining objects owned by the role-to-be-dropped can be dropped with the DROP OWNED command. Again, this command cannot access objects in other databases, so it is necessary to run it in each database that contains objects owned by the role. Also, DROP OWNED will not drop entire databases or tablespaces, so it is necessary to do that manually if the role owns any databases or tablespaces that have not been transferred to new owners.

DROP OWNED 还负责移除目标角色对不属于该角色的对象所授予的任何权限。由于 REASSIGN OWNED 不会触碰此类对象，因此通常必须分别运行 REASSIGN OWNED 和 DROP OWNED（按此顺序！），以彻底地移除将要删除的角色的依赖关系。

DROP OWNED also takes care of removing any privileges granted to the target role for objects that do not belong to it. Because REASSIGN OWNED does not touch such objects, it’s typically necessary to run both REASSIGN OWNED and DROP OWNED (in that order!) to fully remove the dependencies of a role to be dropped.

总之，移除用来拥有对象的某个角色的最通用的做法是：

In short then, the most general recipe for removing a role that has been used to own objects is:

REASSIGN OWNED BY doomed_role TO successor_role;
DROP OWNED BY doomed_role;
-- repeat the above commands in each database of the cluster
DROP ROLE doomed_role;

如果所有受拥有对象并非要传输给同一位继任所有者，则最好是手动处理异常，然后再执行上述步骤进行清理。

When not all owned objects are to be transferred to the same successor owner, it’s best to handle the exceptions manually and then perform the above steps to mop up.

如果在仍然存在依赖对象时尝试 DROP ROLE，它将发出消息，标识需要重新分配或删除哪些对象。

If DROP ROLE is attempted while dependent objects still remain, it will issue messages identifying which objects need to be reassigned or dropped.