How to drop user if objects depend on it

From: "Andrus" <kobruleht2(at)hot(dot)ee>
To: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Subject: How to drop user if objects depend on it
Date: 2015-10-07 10:42:51
Message-ID: 5E8A1D7D61274F12B0E9A6751CCC3E21@dell2
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi!

Database idd owner is role 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 rights assigned to objects.

How to drop 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

in statement

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 ?
Or maybe there is some command or simpler commands in postgres ?

Using Postgres 9.1+
Posted also in

http://stackoverflow.com/questions/32988702/how-to-drop-user-in-all-cases-in-postgres

Andrus.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Karsten Hilbert 2015-10-07 10:55:38 Re: md5(large_object_id)
Previous Message Thomas Kellerer 2015-10-07 09:54:41 Re: Issues with german locale on CentOS 5,6,7