PostgreSQL Drop user

• DROP ROLE removes the specified role(s). To drop a superuser role, you must be a superuser yourself; to drop non-superuser roles, you must have CREATEROLE privilege.
• A role cannot be removed if it is still referenced in any database of the cluster; an error will be raised if so. Before dropping the role, you must drop all the objects it owns (or reassign their ownership) and revoke any privileges the role has been granted on other objects.
• dropuser removes an existing PostgreSQL user and the databases which that user owned. Only superusers (users with usesuper set in the pg_shadow table) can destroy PostgreSQL users

DROP ROLE [ IF EXISTS ] name [, …]

Examples:

— Here we are going to drop uncontents u7 user so you can drop that role simple drop user command

postgres=# \du                                                       
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u1        |                                                | {}
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u4        | Create DB                                      | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u7        |                                                | {}
 u8        | Superuser, Create role, Create DB              | {}

postgres=# drop role u7;
DROP ROLE

postgres=# \du          
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u1        |                                                | {}
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u4        | Create DB                                      | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u8        | Superuser, Create role, Create DB              | {}

you cannot drop any user without superuser privileges from following examples you will understand this things

postgres=> \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u1        |                                                | {}
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u4        | Create DB                                      | {}
 u5        |                                                | {}
 u6        |                                                | {}
 u8        | Superuser, Create role, Create DB              | {}


postgres=> \conninfo
You are connected to database "postgres" as user "u2" via socket in "/tmp" at port "5432".

postgres=> drop user u4;                                                                       
ERROR:  permission denied to drop role

--connect as u4 user this user having createdb privileges only not having drop user privilege
-bash-3.2# ./psql -U u4
Password for user u4: 

postgres=> drop user u1;
ERROR:  permission denied to drop role

— Now connect as u8 super user which user having superuser privileges so this user can drop any user

-bash-3.2# ./psql -U u8
Password for user u8: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
postgres=# drop user u5;
DROP ROLE
postgres=# drop user u4;
DROP ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u1        |                                                | {}
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u6        |                                                | {}
 u8        | Superuser, Create role, Create DB              | {}

—  Super user can drop super user  you will understand following example , first of all we are promoting u1 user as a superuser then we can go to dropping user

postgres=# alter user u1 superuser;
ALTER ROLE
postgres=# \du                     
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u1        | Superuser                                      | {}
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u6        |                                                | {}
 u8        | Superuser, Create role, Create DB              | {}

postgres=# drop user u1;           
DROP ROLE
postgres=# \du          
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u6        |                                                | {}
 u8        | Superuser, Create role, Create DB              | {}

—if user having any objects means for example if u2 user having any owned tables means you cannot drop u2 user by specifying simple drop command  at this moment you have to specify OWNED BY keywords else you cannot drop the u2 user

-bash-3.2# ./psql -U u2
Password for user u2: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.

postgres=> create table t2 as select * from pg_user;
SELECT 7

--connect as u8 superuser then try to drop u2 user this time will get error
-bash-3.2# ./psql -U u8
Password for user u8: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
postgres=# drop user u2;
ERROR:  role "u2" cannot be dropped because some objects depend on it
DETAIL:  owner of table t2

solution:

You can drop the objects owned user following two methods

1.REASSIGN OWNED – you can delete the user without deleting objects

2. OWNED BY –  Owned by is 2 type one is restrict  and another is by using cascade keyword.

1.REASSIGN OWNED

Here u2 user owned one object name is t2 table so here we are going to change the table ownership first then we can easily drop the u2 user by using simple drop command and note one point REASSIGN OWNED does not deal with privileges for other objects.

postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
 public | t2   | table | u2
(2 rows)

postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of 
-----------+------------------------------------------------+-----------
 nijam     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 rep       | Replication                                   +| {}
           | 1 connection                                   | 
 u2        |                                                | {}
 u3        | Password valid until 2017-06-06 00:00:00-04    | {}
 u6        |                                                | {}
 u8        | Superuser, Create role, Create DB              | {}

postgres=# REASSIGN OWNED BY u2 to u6;
REASSIGN OWNED

postgres=# drop user u2;
DROP ROLE

postgres=# \dt
        List of relations
 Schema | Name | Type  |  Owner   
--------+------+-------+----------
 public | t1   | table | postgres
 public | t2   | table | u6
(2 rows)

Note: here t2 table owner is changed from u2 user to u6 user

2.OWNED BY – remove database objects owned by a database role, DROP OWNED drops all the objects within the current database that are owned by one of the specified roles. Any privileges granted to the given roles on objects in the current database and on shared objects (databases, tablespaces) will also be revoked

syntax:

DROP OWNED BY name [, ...] [ CASCADE | RESTRICT ]

name
The name of a role whose objects will be dropped, and whose privileges will be revoked.
CASCADE
Automatically drop objects that depend on the affected objects.
RESTRICT
Refuse to drop the objects owned by a role if any other database objects depend on one of the affected objects. This is the default.

Note: Databases and tablespaces owned by the role(s) will not be removed.

Owned by classified two types they are:

1. RESTRICT
2. CASCADE

Before using restrict and cascade keyword we have to create some objects inside the dropping user

--connect as u9 user and create some objects 
postgres=# create user u9 with password  'u9';
CREATE ROLE
-bash-3.2# ./psql -U u9
Password for user u9: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.

postgres=> create table t5 as select * from pg_user;
SELECT 7

--connect as super user and delete u9 user
-bash-3.2# ./psql -U u8
Password for user u8: 
psql.bin (9.3.14)
Type "help" for help.

No entry for terminal type "xterm";
using dumb terminal settings.
postgres=# drop user u9;
ERROR:  role "u9" cannot be dropped because some objects depend on it
DETAIL:  owner of table t5

1.RESTRICT

Refuse to drop the objects owned by a role if any other database objects depend on one of the affected objects. This is the default.

-- As postgres user
postgres=# create table t1(id int);
CREATE TABLE
postgres=# grant select on t1 to u5;
GRANT

As u5 create t2 table
[postgres@slave ~]$ psql -U u5 -d postgres
postgres=> create table t2(id int);
CREATE TABLE

postgres=> \dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | employe | table | postgres
 public | t1      | table | postgres
 public | t2      | table | u5
(3 rows)

postgres=> \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u5        |                                                            | {}

postgres=> \dp
                                  Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column privileges | Policies
--------+---------+-------+---------------------------+-------------------+----------
 public | employe | table |                           |                   |
 public | t1      | table | postgres=arwdDxt/postgres+|                   |
        |         |       | u5=r/postgres             |                   |
 public | t2      | table |                           |                   |
(3 rows)


-- As postgres user:
 drop owned by u5 restrict;

postgres=#  drop owned by u5 restrict;
DROP OWNED

postgres=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | employe | table | postgres
 public | t1      | table | postgres
(2 rows)

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u5        |                                                            | {}


postgres=# \dp
                                  Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column privileges | Policies
--------+---------+-------+---------------------------+-------------------+----------
 public | employe | table |                           |                   |
 public | t1      | table | postgres=arwdDxt/postgres |                   |
(2 rows)

Note: t1 table privileges are revoked from u5 user now you can drop this user by using simple drop command.

2.CASCADE

Automatically drop objects that depend on the affected objects

postgres=# grant select on t1 to u5;
GRANT
postgres=# \q
[postgres@slave ~]$ psql -U u5 -d postgres
Password for user u5:
psql (10.4)
Type "help" for help.

postgres=> create table t2(id int);
CREATE TABLE
postgres=> create table t3(id int);
CREATE TABLE

postgres=# drop owned by u5 cascade;
DROP OWNED
postgres=# \dt
          List of relations
 Schema |  Name   | Type  |  Owner
--------+---------+-------+----------
 public | employe | table | postgres
 public | t1      | table | postgres
(2 rows)

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 u5        |                                                            | {}

postgres=# \dp
                                  Access privileges
 Schema |  Name   | Type  |     Access privileges     | Column privileges | Policies
--------+---------+-------+---------------------------+-------------------+----------
 public | employe | table |                           |                   |
 public | t1      | table | postgres=arwdDxt/postgres |                   |
(2 rows)