Drop user which objects are in different databases of PG Instance.

One of my colleague has asked about this. She was doing some testing and wanted to drop a user which has objects across the databases.

While dropping user/role, DBA/Admin will get following error messages:

postgres=# drop user test;
ERROR:  role "test" cannot be dropped because some objects depend on it
DETAIL:  2 objects in database test

Above error messages gives the sufficient information to Admin that there are some objects depend/Owned by the user exists in other database.

For dropping such user, there are two methods:
1. Reassign all the objects owned by the user to some other user and then drop the user.
Above is very useful, if employee, who left the company, has written some Procedure/objects, which is getting used in Application/process.

Command Which can be are following:

REASSIGN OWNED BY old_role to new_role; 
DROP USER old_role;

Note:: reassign command need to be executed for all the databases under one PG instance.

2. First Drop all the objects owned by the user and then drop the user.
This is useful if admin don't want to keep the users objects and wants to drop all the objects owned by user:
Command which can be use are following:

DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ];
DROP user username;

Note:: DROP OWNED BY NAME need to be executed for all the database.


To make it further better, I have written following Function, which uses the dblink to make connections to other database and execute the command to drop the user:

CREATE OR REPLACE function reassign_drop_user(text,text) returns integer
AS
$$
Declare
    db record;
    cmd text;
    status text;
BEGIN
   cmd:='REASSIGN OWNED BY '||$1||' TO '||$2||';';
   for db in select datname from pg_database where datname!='template0'
   Loop
      execute 'select dblink_connect('||''''||'dbname='||db.datname||''''||')' into status;
      RAISE NOTICE 'CONNCETION STATUS % :- %',db.datname,status;
      execute 'select dblink_exec('||''''||cmd||''''||')' into status;
      RAISE NOTICE 'COMMAND " % " STATUS :- %',cmd,status;
      execute 'select dblink_disconnect()' into status;
   END Loop;
   execute 'DROP USER '||$1;
   exception when others then
     return 1;
   Return 0;
END;
$$ language plpgsql;

I have kept return value integer, so that I can verify if the function is successful (0) or unsuccessful (1)
Output:

select reassign_drop_user('fromuser','touser');
NOTICE:  CONNCETION STATUS template1 :- OK
NOTICE:  COMMAND " REASSIGN OWNED BY fromuser TO touser; " STATUS :- REASSIGN OWNED
NOTICE:  CONNCETION STATUS template_postgis :- OK
NOTICE:  COMMAND " REASSIGN OWNED BY fromuser TO touser; " STATUS :- REASSIGN OWNED
NOTICE:  CONNCETION STATUS test :- OK
NOTICE:  COMMAND " REASSIGN OWNED BY fromuser TO touser; " STATUS :- REASSIGN OWNED
NOTICE:  CONNCETION STATUS postgres :- OK
NOTICE:  COMMAND " REASSIGN OWNED BY fromuser TO touser; " STATUS :- REASSIGN OWNED 
reassign_drop_user 
--------------------                  
0
(1 row)

Similarly for Dropping Objects with User following function can be use:

REATE OR REPLACE function drop_user_withobjects(text) returns integer
AS
$$
Declare
    db record;
    cmd text;
    status text;
BEGIN
   cmd:='DROP OWNED BY '||$1||' CASCADE;';
   for db in select datname from pg_database where datname!='template0'
   Loop
      execute 'select dblink_connect('||''''||'dbname='||db.datname||''''||')' into status;
      RAISE NOTICE 'CONNCETION STATUS % :- %',db.datname,status;
      execute 'select dblink_exec('||''''||cmd||''''||')' into status;
      RAISE NOTICE 'COMMAND " % " STATUS :- %',cmd,status;
      execute 'select dblink_disconnect()' into status;
   END Loop;
   execute 'DROP USER '||$1;
   exception when others then
     return 1;
   Return 0;
END;
$$ language plpgsql;