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:
--create user u4 & u5
Syntax
Syntax:
--check the group name whether changed or not
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>
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 ROLEIf 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 GROUPSSyntax:
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 ROLENote:- 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 REMOVINGdb2=# 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
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 notdb2=# \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
No comments:
Post a Comment