• 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 Group/role

        Groups are entirely optional in postgresql. They are only used to simplify granting and revoking privileges for the postgresql database

        It is frequently convenient to group users together to ease management of privileges: that way, privileges can be granted to, or revoked from, a group as a whole. In PostgreSQL this is done by creating a role that represents the group, and then granting membership in the group role to individual user roles.

        SYNOPSIS:

        In ascending order – Groups –> Users –>Schemas –> tables

        select * from pg_group;
        create group <group_name>
        create group <group_name> with user <user_name>
        drop group <group_name>
        alter group <group_name> add user <user_name> 
        alter group <group_name> drop user <user_name>

        Syntax:

        CREATE GROUP name [ [ WITH ] option [ ... ] ]
        
        where option can be:
        
              SUPERUSER | NOSUPERUSER
            | CREATEDB | NOCREATEDB
            | CREATEROLE | NOCREATEROLE
            | INHERIT | NOINHERIT
            | LOGIN | NOLOGIN
            | [ ENCRYPTED ] PASSWORD 'password'
            | VALID UNTIL 'timestamp'
            | IN ROLE role_name [, ...]
            | IN GROUP role_name [, ...]
            | ROLE role_name [, ...]
            | ADMIN role_name [, ...]
            | USER role_name [, ...]
            | SYSID uid

        EXAMPLE FOR GROUP CREATION:
        — creating sample user u4 & u5

        db2=# create user u4  with password 'u4';
        CREATE ROLE
        db2=# create user u5  with password 'u5';
        CREATE ROLE

        If we wanted to create a group with alice as an initial member, we can use

        db2=# CREATE GROUP gp2 WITH USER U4  ENCRYPTED PASSWORD 'gp2'; 
        CREATE ROLE
        
        db2=# \du
                                     List of roles
         Role name |                   Attributes                   | Member of 
        -----------+------------------------------------------------+-----------
         gp2       | Cannot login                                   | {}
         group1    | Cannot login                                   | {}
         nijam     |                                                | {}
         postgres  | Superuser, Create role, Create DB, Replication | {}
         rep       | Replication                                   +| {}
                   | 1 connection                                   | 
         u4        |                                                | {gp2}
         u5        |                                                | {}
         u8        | Superuser, Create role, Create DB              | {}
         u9        |                                                | {}

        EXAMPLE FOR ADDING AND REMOVING USERS INTO THE GROUP:

        Syntax

        ALTER GROUP groupname [ADD|DROP] USER username [, ...  ]

        — Here are adding u5 user into  the GP2 group and removing  U4 user from gp2 group

        db2=# ALTER GROUP gp2 ADD USER u5;
        ALTER ROLE
        db2=# ALTER GROUP gp2 drop USER u4;
        ALTER ROLE
        db2=# \du
                                     List of roles
         Role name |                   Attributes                   | Member of 
        -----------+------------------------------------------------+-----------
         gp2       | Cannot login                                   | {}
         group1    | Cannot login                                   | {}
         nijam     |                                                | {}
         postgres  | Superuser, Create role, Create DB, Replication | {}
         rep       | Replication                                   +| {}
                   | 1 connection                                   | 
         u4        |                                                | {}
         u5        |                                                | {gp2}
         u8        | Superuser, Create role, Create DB              | {}
         u9        |                                                | {}
        EXAMPLE FOR GRPOUP VIEWS
        We can see group membership by viewing the pg_group system table. In this example I’ve added U4 USER into the GP2 group.
        db2=# ALTER GROUP gp2 add USER u4; 
        ALTER ROLE
        db2=# select * from pg_group ;
         groname | grosysid |    grolist    
        ---------+----------+---------------
         group1  |    24647 | {}
         gp2     |    24659 | {24658,24657}
        (3 rows)

        –The grolist column shows a list of user ids that are in the group. If you want to see the usernames in a particular group you can use

        db2=# select usename from pg_user, (select grolist from pg_group where groname ='gp2') as groups where usesysid = ANY(grolist);   
         usename 
        ---------
         u4
         u5
        (2 rows)

        EXAMPLE FOR RENAMING THE GROUPS
        Syntax:

        ALTER GROUP groupname RENAME TO newname;

        –To rename gp2 to gp3 we would use

        db2=# ALTER GROUP gp2 RENAME TO gp3;
        NOTICE:  MD5 password cleared because of role rename
        ALTER ROLE

        — check the group name whether changed or not

        db2=# \du    
                                     List of roles
         Role name |                   Attributes                   | Member of 
        -----------+------------------------------------------------+-----------
         gp3       | Cannot login                                   | {}
         group1    | Cannot login                                   | {}
         nijam     |                                                | {}
         postgres  | Superuser, Create role, Create DB, Replication | {}
         rep       | Replication                                   +| {}
                   | 1 connection                                   | 
         u4        |                                                | {gp3}
         u5        |                                                | {gp3}
         u8        | Superuser, Create role, Create DB              | {}

        EXAMPLE FOR DROPPING GROUP

        db2=# drop group group1;
        DROP ROLE

        — listing the group whether group1 is dropped or not

        db2=# \du
                                     List of roles
         Role name |                   Attributes                   | Member of 
        -----------+------------------------------------------------+-----------
         gp3       | Cannot login                                   | {}
         nijam     |                                                | {}
         postgres  | Superuser, Create role, Create DB, Replication | {}
         rep       | Replication                                   +| {}
                   | 1 connection                                   | 
         u4        |                                                | {gp3}
         u5        |                                                | {gp3}
         u8        | Superuser, Create role, Create DB              | {}
         u9        |                                                | {}
        if u4,u5 user having any objects means you can drop gp3 group by using  simple drop command  otherwise if group have any  owned objects  means you cannot drop the group by using  simple drop command you have to use “DROP OWNED” command first
        1.Dropping group by using  Simple drop command
        connect as u4 user and create some objects note one thing u4 user is member of group gp3
        -bash-3.2$ ./psql -U u4 -d db2
        Password for user u4: 
        psql.bin (9.3.14)
        Type "help" for help.
        
        No entry for terminal type "xterm";
        using dumb terminal settings.
        
        db2=> create table t7 as select * from pg_user;
        SELECT 10
        
        -bash-3.2$ ./psql -U u8 -d db2
        Password for user u8: 
        psql.bin (9.3.14)
        Type "help" for help.
        
        No entry for terminal type "xterm";
        using dumb terminal settings.
        
        db2=# drop group gp3;
        DROP ROLE
        2.Dropping group by using owned by command:
        If grouped user having any objects means you cannot drop the group by using simple drop  command
        Let us see example
        –listing  user & groups
        db2=> \du
                                     List of roles
         Role name |                   Attributes                   | Member of 
        -----------+------------------------------------------------+-----------
         gp1       | Cannot login                                   | {}
         nijam     |                                                | {}
         postgres  | Superuser, Create role, Create DB, Replication | {}
         rep       | Replication                                   +| {}
                   | 1 connection                                   | 
         u8        | Superuser, Create role, Create DB              | {}
         u9        |

        — creating two user

        db2=# create user u1 with password 'u1';
        CREATE ROLE
        db2=# create user u2 with password 'u2';
        CREATE ROLE

        — Create a group then add u1 and u2 user into that group

        -bash-3.2# ./psql -U u8 -d db2
        db2=# create GROUP gp1;
        CREATE ROLE
        
        db2=# alter group gp1 add user u1,u2; 
        ALTER ROLE

        — Checking the gp1 group member

        db2=# \du
                                     List of roles
         Role name |                   Attributes                   | Member of 
        -----------+------------------------------------------------+-----------
         gp1       | Cannot login                                   | {}
         nijam     |                                                | {}
         postgres  | Superuser, Create role, Create DB, Replication | {}
         rep       | Replication                                   +| {}
                   | 1 connection                                   | 
         u1        |                                                | {gp1}
         u2        |                                                | {gp1}
         u3        |                                                | {}
         u4        |                                                | {}
         u5        |                                                | {}
         u8        | Superuser, Create role, Create DB              | {}
         u9        |                                                | {}

        — creating some table into u1 user

        -bash-3.2# ./psql -U u1 -d db2
        Password for user u9: 
        psql.bin (9.3.14)
        Type "help" for help.
        
        No entry for terminal type "xterm";
        using dumb terminal settings.
        
        db2=> create table t5 as select * from pg_user;
        SELECT 10
        
        db2=# \dt
               List of relations
         Schema | Name | Type  | Owner 
        --------+------+-------+-------
         public | t5   | table | u1
         public | t6   | table | u9
         public | t7   | table | u4
        (3 rows)

        — changing t5 table owner to gp1 role

        -bash-3.2# ./psql -U u8 -d db2
        db2=> alter table t5 owner to gp1;  
        ALTER TABLE
        
        db2=# \dt
               List of relations
         Schema | Name | Type  | Owner 
        --------+------+-------+-------
         public | t5   | table | gp1
         public | t6   | table | u9
         public | t7   | table | u4
        (3 rows)
        
        db2=# drop group gp1;
        ERROR:  role "gp1" cannot be dropped because some objects depend on it
        DETAIL:  owner of table t5
        
        db2=# drop owned by gp1;
        DROP OWNED
        
        db2=# drop group gp1;   
        DROP ROLE

        Granting bulk of  privileges to some  user:

        — creating group

        postgres=# create group gp1;
        CREATE ROLE

        syntax:

        grant all privileges on database <db_name> to <group_name>
        grant all privileges on database db1 to gp1;
        grant connect on database db1 to gp1;

        granting gp1 privilleges to u3 user:
        You can grant membership to other group roles, too (since there isn’t really any distinction between group roles and non-group roles). The database will not let you set up circular membership loops. Also, it is not permitted to grant membership in a role to PUBLIC.
        syntax:

        RANT group_role TO role1, ... ;
        REVOKE group_role FROM role1, ... ;
        grant gp1 to u3;

        check the u3 user privileges

        postgres=# \du u3
                   List of roles
         Role name | Attributes | Member of
        -----------+------------+-----------
         u3        |            | {gp1}

        after connecting as role u3, a database session will have use of privileges granted directly to u3 plus any privileges granted to gp1 group, because u3 “inherits” gp1’s privileges

        GROUP=ROLE

        Here i created nijam role but you can use nijam role as a GROUP by mentioning GROUP keyword

        postgres=# create role nijam;
        CREATE ROLE
        
        postgres=# \du
                                           List of roles
         Role name |                         Attributes                         | Member of
        -----------+------------------------------------------------------------+-----------
         gp1       | Cannot login                                               | {}
         nijam     | Cannot login                                               | {}
         postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
         u3        |                                                            | {gp1}
         u4        |                                                            | {}
         u5        |                                                            | {}
        
        postgres=# grant all privileges on database db1 to nijam;
        GRANT
        postgres=# ALTER GROUP nijam ADD USER u5;
        ALTER ROLE
        
                                           List of roles
         Role name |                         Attributes                         | Member of
        -----------+------------------------------------------------------------+-----------
         gp1       | Cannot login                                               | {}
         nijam     | Cannot login                                               | {}
         postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
         u3        |                                                            | {gp1}
         u4        |                                                            | {}
         u5        |                                                            | {nijam}

        If  you want to add any user into the role means you have to mention GROUP key word instead of role  if it is even role actually role.

        postgres=# ALTER role nijam ADD USER u4;
        ERROR:  syntax error at or near "ADD"
        LINE 1: ALTER role nijam ADD USER u4;
        Prev PostgreSQL Password Changing and Views
        Next Alter PostgreSQL User/Role/group

        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