特权下放角色 [英] Drop a role with privileges

问题描述

这看起来是非常基本的需求，但是我找不到任何快速且合适的答案。我在Postgres中扮演一个角色，该角色对各种数据库中的许多其他表具有特权。

This looks like a very basic need, but I do not find any quick and suitable answer. I have a role in Postgres which has privileges to many other tables in various databases.

我需要放弃这个角色。我有一个postgres实例，然后有很多数据库。

I need to drop this role. I have one postgres instance and then many databases on top of it.

SELECT DISTINCT 'REVOKE ALL ON TABLE ' || table_schema || '.' || table_name || ' FROM ' || r.param_role_name || ';'  
FROM information_schema.table_privileges CROSS JOIN (SELECT 'some_role_name'::text AS param_role_name) r
WHERE grantee ~* r.param_role_name;

我可以像上面那样，通过访问每个数据库并找到所有吊销语句，然后删除角色。有什么办法可以在一个地方找到所有数据库的所有吊销语句。

I can do like above, by going to each and every database and find all revoke statements and then drop the role. Is there any way I can find all revoke statements at one place for all the databases.

或者像我可以更改该角色拥有的权限一样，我可以用一条语句更改所有特权吗？

Or something like I can alter owned by this role, can I alter all privileges with one statement?

Edit1：由于大多数答复的目标是重新分配归所有者所有，而归归所有者所有，我想更具体一些。
我可以在每个数据库上执行以下命令，并在删除所有依赖项后删除角色。

As most of the replies are targeted to reassign owned by and drop owned by, I want to be more specific. I can execute below commands on each database and drop the roles once all the dependencies are removed.

REASSIGN OWNED BY some_role_name TO postgres;
DROP OWNED BY some_role_name;

但是有很多数据库，因此我正在寻找可以强制清除所有特权并放弃该角色的东西。 / p>

but there are many databases so I am looking for something forcefully flush all privileges and drop this role.

推荐答案

我需要删除该角色。

I need to drop this role.

单个 已重新拥有 ，后跟 已删除

A single REASSIGN OWNED per database, followed by DROP OWNED should do it:

REASSIGN OWNED BY some_role_name TO postgres;
DROP OWNED BY some_role_name;

postgres 是默认的超级用户，即将拥有该角色曾经拥有的任何对象。在 REASSIGN OWNED 之后，没有剩余的对象由同一用户拥有。运行 DROP OWNED 似乎不直观。该命令的措词具有误导性，因为它还撤消了同一数据库中该角色的所有特权和默认特权。 手册：

postgres being the default superuser, who is going to own any object the role used to own. Immediately after REASSIGN OWNED, there are no objects left that would be owned by the same user. It may seem unintuitive to run DROP OWNED. The wording of the command is misleading, since it also revokes all privileges and default privileges for the role in the same database. The manual:

DROP OWNED 删除当前数据库中由指定角色之一拥有的所有对象。 授予给当前数据库中对象和共享对象（数据库，表空间）上的角色的任何特权也将被撤销。

DROP OWNED drops all the objects within the current database that are owned by one of the specified roles. Any privileges granted to the given roles on objects in the current database and on shared objects (databases, tablespaces) will also be revoked.

加粗强调。

您仍然必须在 每个数据库 中执行它。手册：

Bold emphasis mine.
You still have to execute it in every single database. The manual:

由于 REASSIGN OWNED 不会影响其他数据库中的对象，因此通常在每个包含要删除的角色拥有的对象的数据库中执行此命令是必需的。

Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.

在运行之前（一次！）：

Before you can run (once!):

DROP role some_role_name;

角色存储在集群范围的系统目录中，而对象的所有权和特权存储在数据库中特定于系统的目录。

Roles are stored in a cluster-wide system catalog, while ownership and privileges on objects are stored in database-specific system catalogs.

此相关答案中的详细说明：

Detailed explanation in this related answer:

• < a href = https://dba.stackexchange.com/questions/155332/find-objects-linked-to-a-postgresql-role/155356#155356>查找链接到PostgreSQL角色的对象

• Find objects linked to a PostgreSQL role

@klin在他的评论：

• https://www.postgresql.org/docs/current/static/role-removal.html

• https://www.postgresql.org/docs/current/static/role-removal.html

没有单个命令可以执行此操作所有。但是，您可以让Postgres为您生成完整的psql脚本。

There is no single command to do it all. But you can let Postgres generate a complete psql script for you.

角色的依赖关系存储在系统目录 pg_shdepend 。

Dependencies for roles are stored in the system catalog pg_shdepend. A quote from there:

此信息使PostgreSQL可以确保在尝试删除它们之前未引用这些对象。

This information allows PostgreSQL to ensure that those objects are unreferenced before attempting to delete them.

由于我们（潜在地）需要连接到不同的数据库，因此我们需要组合psql元命令（ \c my_database ）和SQL DDL命令，如上所示。一次在数据库集群中的某个位置创建此函数：

Since we (potentially) need to connect to different databases, we need a combination of psql meta-commands (\c my_database) and SQL DDL commands as shown above. Create this function somewhere in your DB cluster once:

CREATE OR REPLACE FUNCTION f_generate_ddl_to_remove_role(dead_role_walking regrole)
  RETURNS text AS
$func$
SELECT concat_ws(
   E'\n'
 ,(SELECT string_agg(format(E'\\c %I\nREASSIGN OWNED BY %2$s TO postgres; DROP OWNED BY %2$s;'
                          , d.datname, dead_role_walking)
                   , E'\n')
   FROM  (
      SELECT DISTINCT dbid
      FROM   pg_shdepend
      WHERE  refobjid = dead_role_walking
      ) s
   JOIN   pg_database d ON d.oid = s.dbid)
 , format(E'DROP role %s;\n', dead_role_walking)
   )
$func$  LANGUAGE sql;

致电：

SELECT f_generate_ddl_to_remove_role('some_role_name');

产生如下字符串：

\c my_db1
REASSIGN OWNED BY some_role_name TO postgres; DROP OWNED BY some_role_name;
\c my_db2
REASSIGN OWNED BY some_role_name TO postgres; DROP OWNED BY some_role_name;
DROP role some_role_name;

或者，如果角色不拥有任何东西且没有特权，则只需：

Or, if the role does not own anything and has no privileges, just:

DROP role some_role_name;

如果提供的角色名称不存在，则会出现错误。

If you provide a non-existing role name, you get an error.

将字符串（不包含单引号）复制到以超级用户（例如 postgres ）打开的psql会话中。或将bash脚本与其连接。

Copy the string (without enclosing single quotes) to a psql session opened with a superuser like postgres. Or concatenate a bash script with it. All done.

有几个相关的答案，其中有关于动态SQL的更多解释：

There are several related answers with more explanation for dynamic SQL:

• https://stackoverflow.com/search?q=%5Bplpgsql%5D+% 5Bdynamic-sql％5D + string_agg

• https://stackoverflow.com/search?q=%5Bplpgsql%5D+%5Bdynamic-sql%5D+string_agg

这篇关于特权下放角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！