Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL Group

Groups are entirely optional in postgresql. They are only used to simplify granting and revoking privileges for the db admin, and users do not need to be a member of any group.
SYNOPSIS:
  • In ascending order - Groups --> Users -->Shemas --> 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 <groupIname> drop user <user_name> 
Syntax:
CREATE GROUP name [ [ WITH ] option [ ... ] ]

where option can be:
    
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp' 
    | IN ROLE rolename [, ...]
    | IN GROUP rolename [, ...]
    | ROLE rolename [, ...]
    | ADMIN rolename [, ...]
    | USER rolename [, ...]
    | SYSID uid 
PRACTICAL 1.GROUP CREATION:
--create 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        |                                                | {}
PRACTICAL  2.ADDING AND REMOVING USERS:
Syntax
ALTER GROUP groupname [ADD|DROP] USER username [, ...  ]
--we wanted to add U5 to the GP2 group and remove U4
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        |                                                | {}
PRACTICAL  3.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)
PRACTICAL 4.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
Note:- Here told that md5 password cleared means we already set password gp2 for gp2 group that password is cleared

--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              | {}
PRACTICAL 5.GROUP REMOVING
db2=# drop group group1;
DROP ROLE
--list the group whether group1 group droped 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        |                                                | {}
PRACTICAL 6.
if u4,u5 user having any objects means you can drop gp3 group simple method but if group have any objects owner means you cannot drop the group simple drop method you need to use "DROP OWNED" method

1.Drop group Simple  method
connect as u4 user and create some objects
-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.Drop group owned by method
If grouped user having any objects means you cannot drop the group simple drop method you need to use owned by method
--listing the 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        |  
--create two user
db2=# create user u1 with password 'u1';
CREATE ROLE
db2=# create user u2 with password 'u2';
CREATE ROLE
--create group and adding user u1,u2 for single command
 -bash-3.2# ./psql -U u8 -d db2
db2=# create GROUP gp1;
CREATE ROLE

db2=# alter group gp1 add user u1,u2; 
ALTER ROLE
--check the group wheter that two user is added or not
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        |                                                | {}
--create table from user u1
-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)
--change the owner to group "gp1"
-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
<<< Preview                                                                                                                 Next>>>

Comments

Popular posts from this blog

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to Enable/Disable autovacuum on PostgreSQL