• 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 Revoke

        The REVOKE command revokes previously granted privileges from one or more roles

        Syntax-for grant option:

        REVOKE [ GRANT OPTION FOR ]
            { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
            [, ...] | ALL [ PRIVILEGES ] }
            ON { [ TABLE ] table_name [, ...]
                 | ALL TABLES IN SCHEMA schema_name [, ...] }
            FROM { [ GROUP ] role_name | PUBLIC } [, ...]
            [ CASCADE | RESTRICT ]

        Examples:

        — connect as u1 user and view the buy table

        postgres=> select * from benz2.buy; 
         item_id | volume 
        ---------+--------
              10 |   1000
              30 |    300
        (2 rows)

        — connect as u2 superuser and revoke privileges from u1 user

        postgres=# revoke all privileges on benz2.buy from u1;        
        REVOKE

        — after revoking privilege  user  u1 can’t access the buy table

        postgres=> select * from benz2.buy;
        ERROR:  permission denied for relation buy

        Granting options are two Type:

        I.WITH GRANT OPTION
        II.WITH ADMIN OPTION

        I.WITH GRANT OPTION:

        If GRANT OPTION FOR is specified, only the grant option for the privilege is revoked, not the privilege itself. Otherwise, both the privilege and the grant option are revoked.

        If a user holds a privilege with grant option and has granted it to other users then the privileges held by those other users are called dependent privileges. If the privilege or the grant option held by the first user is being revoked and dependent privileges exist, those dependent privileges are also revoked if CASCADE is specified; if it is not, the revoke action will fail. This recursive revocation only affects privileges that were granted through a chain of users that is traceable to the user that is the subject of this REVOKE command. Thus, the affected users might effectively keep the privilege if it was also granted through other users.

        — list down the user to which user do u want to grant & revoking privileges, Now we will choose u3 & u5 user

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

        — granting select privilege to u3 user with grant option

        postgres=# GRANT SELECT ON demo TO u3 with grant option;
        GRANT

        — check the demo table u3 user having ‘r’ privilege means read privilege

        postgres=# \dp demo
                                    Access privileges
         Schema | Name | Type  | Access privileges | Column privileges | Policies
        --------+------+-------+-------------------+-------------------+----------
         public | demo | table | u2=arwdDxt/u2    +|                   |
                |      |       | u3=r*/u2          |                   |
        (1 row)

        — issue the select statement against demo table as a u3 user if you try to insert any data on demo table means it will throw “permission denied” error because u2 user have only  “select”(read) permission . if  u3 user want insert/update..etc means you can assign privileges to u3 user as per your company recruitment.

        [postgres@slave data]$ psql -U u3 -d postgres
        Password for user u3:
        psql (10.4)
        Type "help" for help.
        
        postgres=> select * from demo;
         id
        ----
          1
        (1 row)
        postgres=> insert into demo values(2);
        ERROR:  permission denied for relation demo

        — Connect as u3 user then grant given ‘select’ privilege to u5 user, this option only can do who have “with grant  option” privileges

        postgres=> GRANT SELECT ON demo TO u5 with grant option;
        GRANT

        — Connect as u5 user then issue the select statement against demo table, u5 user can see demo table because u5 user having read privilege which is given by u3 user

        postgres=> select * from demo;
         id
        ----
          1
        (1 row)
        
        postgres=> \dp demo
                                    Access privileges
         Schema | Name | Type  | Access privileges | Column privileges | Policies
        --------+------+-------+-------------------+-------------------+----------
         public | demo | table | u2=arwdDxt/u2    +|                   |
                |      |       | u3=r*/u2         +|                   |
                |      |       | u5=r*/u3          |                   |
        (1 row)

        Note: Here u5=r*/u3 means   r for read permission,/u3 means select privileges given by u3 user

        you can revoke the given privileges following two option as a u2 superuser

        1. With cascade keyword
        2. Revoking chain method

        1.WITH CASCADE KEYWORD :

        previously  u2 super user given ‘select’ privilege to u3 user “with grant option”  then u3 user grants his privileges to u5 user,  so u5 user may give his privileges to any other user,  so if u2 user want to revoke his u3 user privileges means he need to specify cascade keyword  otherwise  u2 superuser will get “dependent privileges exist” error.

        —  connect as u2 user , initially revoke u3 user privileges without cascade keyword.

        postgres=# revoke SELECT ON demo from u3;               
        ERROR:  dependent privileges exist
        HINT:  Use CASCADE to revoke them too.

        solution:

        Before revoking check the demo tables privileges

        postgres=# \dp demo
                                    Access privileges
         Schema | Name | Type  | Access privileges | Column privileges | Policies
        --------+------+-------+-------------------+-------------------+----------
         public | demo | table | u2=arwdDxt/u2    +|                   |
                |      |       | u3=r*/u2         +|                   |
                |      |       | u5=r*/u3          |                   |
        (1 row)
        postgres=# revoke SELECT ON demo from u3 cascade;
        REVOKE
        postgres=# \dp demo
                                    Access privileges
         Schema | Name | Type  | Access privileges | Column privileges | Policies
        --------+------+-------+-------------------+-------------------+----------
         public | demo | table | u2=arwdDxt/u2     |                   |
        (1 row)

        Initially cascade will revoke select privilege from u5 user then will revoke the select privilege from u3 user

        2.REVOKING CHAIN METHOD:

        If you don’t want to go cascade method initially you have to go to revoke from u5 user then revoke from u3 user

        –connect as u3 user & issue the following command

        postgres=> revoke SELECT ON demo from u5;               
        REVOKE

        –connect the u2 user & issue the following command

        postgres=# revoke SELECT ON benz2.demo from u3;               
        REVOKE

        II.WITH ADMIN OPTION:

        When revoking membership in a role, GRANT OPTION is instead called ADMIN OPTION, but the behavior is similar. Note also that this form of the command does not allow the noise word GROUP.

        syntax- for with admin option:

        REVOKE [ ADMIN OPTION FOR ]
            role_name [, ...] FROM role_name [, ...]
            [ CASCADE | RESTRICT ]

        — Following command line will shows the access privileges

        postgres=> \dp+
                                          Access privileges
         Schema |    Name     | Type  |     Access privileges     | Column access privil
        eges 
        --------+-------------+-------+---------------------------+--------------------------
         benz2  | buy         | table | u2=arwdDxt/u2             | 
         benz2  | comment_log | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | contable    | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | demo        | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2            +| 
                |             |       | u3=r/u2                   | 
         benz2  | dept        | table | postgres=arwdDxt/postgres+| 
                |             |       | u1=arwdDxt/postgres       | 
         benz2  | item        | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | mytab       | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | mytab2      | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | orders      | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | practis     | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | prod        | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | product     | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | products    | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | sale        | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | stock       | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | t1          | table | u7=arwdDxt/u7            +| 
                |             |       | u1=arwdDxt/u7             | 
         benz2  | t10         | table | u3=arwdDxt/u3            +| 
                |             |       | u1=arwdDxt/u3             | 
         benz2  | t11         | table | u5=arwdDxt/u5            +| 
                |             |       | u1=arwdDxt/u5             | 
         benz2  | t6          | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
         benz2  | t9_renamed  | table | u2=arwdDxt/u2            +| 
                |             |       | u1=arwdDxt/u2             | 
        (20 rows)

        — connect as u2 superuser  grant u3 user privileges to u6 user “with admin” option

        syntax:
        GRANT role_name [, ...] TO role_name [, ...] [ WITH ADMIN OPTION ]
        
        postgres=# GRANT U3 TO U6 WITH ADMIN OPTION;
        GRANT ROLE

        — Now  u6 user can access the u3 user’s objects  and he can give u3 user privileges to any other user

        postgres=> select * from benz2.demo;        
         id |  name   |  place  | pincode 
        ----+---------+---------+---------
          1 | anbu    | pulicat |  601205
          2 | nijam   | pulicat |  601206
          3 | umar    | pulicat |  601205
          4 | junaith | pulicat |  601206
          3 | umar    | pulicat |  601205
          4 | junaith | pulicat |  601206
          9 | mohad   | pulicat |  601202
          7 | kadahar | pulicat |  601201
        (8 rows)

        — connect as  u6 user and  he can grant u6 user privileges to u7 user

        postgres=> grant u6 TO u7;
        GRANT ROLE

        — connect as u7 user and fetch the u6 user demo table

        [postgres@r1 ~]$ psql -U u7 
        Password for user u7: 
        psql.bin (9.3.14)
        Type "help" for help.
        
        No entry for terminal type "xterm";
        using dumb terminal settings.
        
        postgres=> select * from benz2.demo;
         id |  name   |  place  | pincode 
        ----+---------+---------+---------
          1 | anbu    | pulicat |  601205
          2 | nijam   | pulicat |  601206
          3 | umar    | pulicat |  601205
          4 | junaith | pulicat |  601206
          3 | umar    | pulicat |  601205
          4 | junaith | pulicat |  601206
          9 | mohad   | pulicat |  601202
          7 | kadahar | pulicat |  601201
        (8 rows)

        — connect as u2 super user and revoke all (u3) privileges from (u6&u7 user)

        postgres=# revoke U3 from U6;               
        REVOKE ROLE
        postgres=# revoke U3 from U7;
        REVOKE ROLE

        –If you try to feth demo table from u7 user it will show error by following

        postgres=> select * from benz2.demo;
        ERROR:  permission denied for relation demo

        Prev PostgreSQL Grant
        Next Postgres user creation and restrict DDL statement & other database access

        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

        • thought behind whiteboard activity
        • 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

        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