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 | | {}
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 | | {}
-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
If grouped user having any objects means you cannot drop the group by using simple drop command
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;