如果用户具有依赖对象，如何删除用户的postgres

Database idd owner is role idd_owner.

数据库idd所有者是角色idd_owner。

Database has 2 data schemas: public and 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+

2 个解决方案

#1

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;

#2

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;

#1