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;

About Courses

Greenplum

About Courses

Greenplum

Become An Instructor?

About Courses

Greenplum

About Courses

Greenplum

PostgreSQL Group/role

Login with your site account

Modal title