• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL
        6
        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION
        9
        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE
        6
        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE
        16
        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
        3
        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC
        3
        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT
        9
        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS
        6
        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE
        5
        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS
        1
        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE
        1
        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE
        4
        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY
        13
        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING
        5
        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
        11
        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        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 [, …]

        Parameters EXPLANATION
        IF EXISTS Do not throw an error if the role does not exist. A notice is issued in this case.
        name The name of the role to remove.

        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)

        Prev PostgreSQL User Creation Using Utility
        Next PostgreSQL Drop User Using Utility

        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres
        • postgres script for finding queries more than 1 hours ?

        2ndquadrant.in

        (+91) 8838953252

        ITsupport@rayafeel.com

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Postgres Migration Blogs
        • Forum

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now

        Modal title

        Message modal