Postgres Pro
Downloads
Home   >   mailing lists

Ynt: How to drop user if objects depend on it - Mailing list pgsql-general

From Neslisah Demirci
Subject Ynt: How to drop user if objects depend on it
Date
Msg-id AMXPR05MB150BFDC0E1C7FA101F27CBF9B360@AMXPR05MB150.eurprd05.prod.outlook.com
Whole thread Raw
In response to How to drop user if objects depend on it  ("Andrus" <kobruleht2@hot.ee>)
Responses Re: Ynt: How to drop user if objects depend on it  (Jerry Sievers <gsievers19@comcast.net>)
List pgsql-general
Tree view


Hi ,


REASSIGN OWNED -- change the ownership of database objects owned by a database role.


REASSIGN OWNED BY old_role [, ...] TO new_role
You can create a new role then you just assign database objects depend on old role.
REASSIGN owned by old_role to new_role;

Then

DROP old_role;

Is this helpful?

Neslisah.

Gönderen: pgsql-general-owner@postgresql.org <pgsql-general-owner@postgresql.org> adına Andrus <kobruleht2@hot.ee>
Gönderildi: 07 Ekim 2015 Çarşamba 13:42
Kime: pgsql-general
Konu: [GENERAL] How to drop user if objects depend on it
 
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
 
sql - How to drop user in postgres if it has depending objects - Stack Overflow
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 ob...

 
 
Andrus.

pgsql-general by date:

Previous
From: Karsten Hilbert
Date:
Subject: Re: md5(large_object_id)
Next
From: Karsten Hilbert
Date:
Subject: Re: md5(large_object_id)