如果有依赖对象，如何将用户放到postgres中 [英] How to drop user in postgres if it has depending objects

问题描述

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

Database idd owner is role idd_owner.

数据库具有2个数据模式:public和firma1. 用户可以在此数据库和对象中直接或间接分配权限. 用户不是任何对象的所有者.它只授予权利.

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.

如何删除此类用户?

我尝试过

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"

但出现错误

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 ?

如何创建以用户名作为参数并在所有情况下都删除该用户而不删除数据的sql或plpgsql方法?

How to create sql or plpgsql method which takes user name as parameter and drops this user in all cases without dropping data ?

使用Postgres 9.1 +

Using Postgres 9.1+

推荐答案

在删除用户之前，您可以运行:

Before dropping the user you can run :

REASSIGN OWNED BY vantaa TO <newuser>

如果您不知道将谁重新分配给...，可以将其重新分配给Postgres.

you could just reassign to postgres if you don't know who to reassign that to ...

REASSIGN OWNED BY vantaa TO postgres;

这篇关于如果有依赖对象，如何将用户放到postgres中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！