特权下放角色

admin

特权下放角色

sql

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

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

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;

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

或者像我可以更改该角色拥有的权限之类的东西，是否可以用一条语句更改所有特权？

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

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

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

阅读 218

2021-06-07

共1个答案

admin

我需要放弃这个角色。

在角色可能拥有任何东西或具有任何授予的特权的同一集群的每个数据库中运行此命令：

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

postgres作为默认的超级用户，您可以选择其他任何用户。它要拥有当前由旧角色拥有的对象。在紧接之后REASSIGN OWNED，没有剩余的对象将由同一用户拥有。跑步似乎是不合常理的DROP OWNED。该命令的措辞是误导，因为它也
撤销了在同一个数据库中的角色的所有特权和缺省权限。手册：

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

大胆强调我的。
您仍然必须在角色拥有任何东西或具有任何授予的特权的 _ 每个数据库_ 中执行它。手册：

因为REASSIGN OWNED它不影响其他数据库中的对象，所以通常需要在每个数据库中执行此命令，该数据库包含要删除的角色所拥有的对象。

最后，运行（一次）：

DROP role some_role_name;

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

手册中有一个相关页面，其中包含说明。

全自动化

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

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

该信息使PostgreSQL可以确保在尝试删除这些对象之前未对其进行引用。

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

CREATE OR REPLACE FUNCTION f_generate_ddl_to_remove_role(dead_role_walking regrole)
  RETURNS text
  LANGUAGE sql 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$;

称呼：

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;

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

DROP role some_role_name;

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

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

2021-06-07