主页 / user-193646

Kajsa's questions

Martin Hope
Kajsa
Asked: 2022-07-14 08:19:23 +0800 CST
  • 0

我有一个 PSQL 13.6 数据库，它最初是由 创建的owner_1，并且随着时间的推移而被填充。由于外部原因，我现在需要定期更改数据库的所有者帐户并删除旧用户帐户。当我尝试实现这一点时，我被阻止了，因为有些对象依赖于它。

我不想冒险失去manager_role可能存在的或任何级联依赖项的特权。
我怎样才能找到并转移仍然拥有的对象owner_1？

MWE：

  1. 作为postgres用户：创建owner_1.
CREATE USER owner_1 WITH CREATEDB CREATEROLE ENCRYPTED PASSWORD 'owner_password_1';
  1. As owner_1：设置数据库和对象。
CREATE DATABASE test_db;
\c test_db
CREATE ROLE manager_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE ON TABLES TO manager_role;
CREATE USER manager_1 WITH ENCRYPTED PASSWORD 'manager_password_1' IN ROLE manager_role;
  1. As owner_1：创建一个owner_role，转移所有权，并在该角色中创建新用户。
CREATE ROLE owner_role WITH NOLOGIN NOSUPERUSER INHERIT CREATEDB CREATEROLE NOREPLICATION;
GRANT USAGE, CREATE ON SCHEMA public TO owner_role;
GRANT owner_role TO "owner_1";
REASSIGN OWNED BY owner_1 TO owner_role;

CREATE USER owner_2 WITH CREATEDB CREATEROLE ENCRYPTED PASSWORD 'owner_password_2' IN ROLE owner_role;

您现在应该拥有这些用户定义

\du
                                       List of roles
  Role name   |                         Attributes                         |   Member of    
--------------+------------------------------------------------------------+----------------
 manager_1    |                                                            | {manager_role}
 manager_role | Cannot login                                               | {}
 owner_1      | Create role, Create DB                                     | {owner_role}
 owner_2      | Create role, Create DB                                     | {owner_role}
 owner_role   | Create role, Create DB, Cannot login                       | {}
 postgres     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
  1. As owner_2：删除旧用户。
DROP USER owner_1;

最后一个命令将失败并出现错误：

ERROR:  role "owner_1" cannot be dropped because some objects depend on it
DETAIL:  owner of default privileges on new relations belonging to role owner_1 in schema public

编辑DROP OWNED BY owner_1;从用户 执行owner_2失败并出现错误：

test_db=> DROP OWNED BY owner_1;
ERROR:  permission denied to drop objects
postgresql postgresql-13