postgresql 如果有依赖对象，如何将用户放入 postgres
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32988702/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to drop user in postgres if it has depending objects
提问by Andrus
Database idd owner is role idd_owner.
数据库 idd 所有者是 role idd_owner。
Database has 2 data schemas: publicand firma1.
User may have directly or indirectly assigned rights in this database and objects.
User is not owner of any object. It has only granted rights.
数据库有 2 个数据模式：public和firma1. 用户可能在此数据库和对象中直接或间接分配了权限。用户不是任何对象的所有者。它只授予了权利。
How to drops such user ?
如何删除这样的用户？
I tried
我试过
revoke all on all tables in schema public,firma1 from "vantaa" cascade;
revoke all on all sequences in schema public,firma1 from "vantaa" cascade;
revoke all on database idd from "vantaa" cascade;
revoke all on all functions in schema public,firma1 from "vantaa" cascade;
revoke all on schema public,firma1 from "vantaa" cascade;
revoke idd_owner from "vantaa" cascade;
ALTER DEFAULT PRIVILEGES IN SCHEMA public,firma1 revoke all ON TABLES from "vantaa";
DROP ROLE if exists "vantaa"
but got error
但有错误
role "vantaa" cannot be dropped because some objects depend on it
DETAIL: privileges for schema public
DROP ROLE if exists "vantaa"
How to fix this so that user can dropped ?
如何解决这个问题，以便用户可以删除？
How to create sql or plpgsql method which takes user name as parameter and drops this user in all cases without dropping data ?
如何创建 sql 或 plpgsql 方法，该方法将用户名作为参数并在所有情况下删除此用户而不删除数据？
Using Postgres 9.1+
使用 Postgres 9.1+
采纳答案by aleroot
Before dropping the user you can run :
在删除用户之前，您可以运行：
REASSIGN OWNED BY vantaa TO <newuser>
you could just reassign to postgres if you don't know who to reassign that to ...
如果您不知道将其重新分配给谁，则可以将其重新分配给 postgres ...
REASSIGN OWNED BY vantaa TO postgres;
回答by Olivier D
Your reassignment can fail. When you reassign to a new user/role - you do it on the currently selected database - the current user must belong to the 'from' role and 'to' (grant them and revoke them after)
您的重新分配可能会失败。当您重新分配给新用户/角色时 - 您在当前选择的数据库上执行此操作 - 当前用户必须属于“from”角色和“to”（授予它们并在之后撤销它们）
Excerpt of a shell script of mine (a few things omitted)
我的一个shell脚本的摘录（省略了一些东西）
# The DROP USER operation will fail if this user is the owner of an existing schema, table, index...
# The user creating a resource owns it. It should transfer the ownership to the role
# When reassigning, the current user needs to have the <<roles>> associated to BY and TO to execute the command.
GRANT $_roleservice TO $_superuser;
GRANT $_account TO $_superuser;
REASSIGN OWNED BY $_account TO $_roleservice;
# dropping the user removes it from the current user list of roles/users
DROP USER $_account;
REVOKE $_roleservice FROM $_superuser;
