-
BASIC POSTGRESQL
6-
PostgreSQL Brief History 30 minLecture1.1
-
PostgreSQL Features 20 minLecture1.2
-
PostgreSQL Monitoring Tools Overview 30 minLecture1.3
-
PostgreSQL – Oracle Vs PostgreSQL 30 minLecture1.4
-
PostgreSQL Data Types 30 minLecture1.5
-
PostgreSQL Maximum Objects Size 30 minLecture1.6
-
-
VMWARE & POSTGRESQL INSTALLATION
9-
Vmware Installation 30 minLecture2.1
-
Creating Linux Virtual Machine 01 hourLecture2.2
-
PostgreSQL Installation Types 30 minLecture2.3
-
PostgreSQL GUI Installation 30 minLecture2.4
-
PostgreSQL Text Mode Installation 30 minLecture2.5
-
PostgreSQL Unattended Mode Installation 30 minLecture2.6
-
Configure the network & Disk Partition 30 minLecture2.7
-
How to install PostgreSQL10 On Linux Server ? 20 minLecture2.8
-
PostgreSQL -11 Installation (rpm & source code) 30 minLecture2.9
-
-
POSTGRESQL DATABASE
6-
Connect Postgres Server 10 minLecture3.1
-
PostgreSQL startup / shutdown /restart the postgresql server 30 minLecture3.2
-
PostgreSQL .bash_profile Set Up 30 minLecture3.3
-
PostgreSQL Database Creation 30 minLecture3.4
-
PostgreSQL Connect Database 30 minLecture3.5
-
PostgreSQL Drop Database 30 minLecture3.6
-
-
POSTGRESQL TABLE
16-
PostgreSQL Languages 30 minLecture4.1
-
PostgreSQL Create Table 30 minLecture4.2
-
PostgreSQL Select Table 30 minLecture4.3
-
PostgreSQL Alter Table 30 minLecture4.4
-
PostgreSQL Drop Table 30 minLecture4.5
-
PostgreSQL Truncate Table 30 minLecture4.6
-
PostgreSQL Rename 30 minLecture4.7
-
PostgreSQL Comment 30 minLecture4.8
-
PostgreSQL Insert 30 minLecture4.9
-
PostgreSQL Update 30 minLecture4.10
-
PostgreSQL Table Delete 30 minLecture4.11
-
PostgreSQL Merge Table 30 minLecture4.12
-
PostgreSQL UNION 30 minLecture4.13
-
PostgreSQL UNION ALL 30 minLecture4.14
-
PostgreSQL INTERSECT 30 minLecture4.15
-
PostgreSQL DISTINCT 30 minLecture4.16
-
-
USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
3-
PostgreSQL Grant 30 minLecture5.1
-
PostgreSQL Revoke 30 minLecture5.2
-
Postgres user creation and restrict DDL statement & other database access 30 minLecture5.3
-
-
TRANSACTIONS - MVCC
3-
PostgreSQL Commit 30 minLecture6.1
-
PostgreSQL Rollback 30 minLecture6.2
-
PostgreSQL Savepoint 30 minLecture6.3
-
-
POSTGRESQL USER/SCHEMA MANAGEMENT
9-
PostgreSQL User Creation 30 minLecture7.1
-
PostgreSQL User Creation Using Utility 30 minLecture7.2
-
PostgreSQL Drop user 30 minLecture7.3
-
PostgreSQL Drop User Using Utility 30 minLecture7.4
-
PostgreSQL Password Changing and Views 30 minLecture7.5
-
PostgreSQL Group/role 30 minLecture7.6
-
Alter PostgreSQL User/Role/group 30 minLecture7.7
-
PostgreSQL Schema 30 minLecture7.8
-
PostgreSQL user creation and restrict DDL & database access 30 minLecture7.9
-
-
POSTGRESQL CONSTRAINTS
6-
PostgreSQL Constraints 30 minLecture8.1
-
PostgreSQL Primary Key 30 minLecture8.2
-
PostgreSQL UNIQUE Constraint 30 minLecture8.3
-
PostgreSQL CHECK Constraint 30 minLecture8.4
-
PostgreSQL NOT NULL Constraint 30 minLecture8.5
-
PostgreSQL Foreign Key 30 minLecture8.6
-
-
POSTGRESQL ADVANCE DATA TYPE
5-
PostgreSQL DOMAIN Data Type 30 minLecture9.1
-
PostgreSQL Alter Domain 30 minLecture9.2
-
PostgreSQL Drop DOMAIN 30 minLecture9.3
-
PostgreSQL Json Data Type 30 minLecture9.4
-
PostgreSQL Sequence 30 minLecture9.5
-
-
POSTGRESQL VIEWS
1-
How to Create PostgreSQL View 30 minLecture10.1
-
-
POSTGRESQL MONITORING OBJECT USUAGE/SIZE
1 -
POSTGRESQL DATABASE ARCHITECTURE
4-
PostgreSQL Tablespace 30 minLecture12.1
-
PostgreSQL UnderStanding Of Objects 30 minLecture12.2
-
PostgreSQL Architecture 30 minLecture12.3
-
PostgreSQL Query Flow 30 minLecture12.4
-
-
POSTGRESQL BACKUP AND RECOVERY
13-
PostgreSQL Backup & Recovery Overview 30 minLecture13.1
-
PostgreSQL Database Backup Tools – Pg_dump 30 minLecture13.2
-
PostgreSQL Database Backup Tools – Pg_dumpall 30 minLecture13.3
-
PostgreSQL Database Backup Tools – Pg_basebackup 30 minLecture13.4
-
PostgreSQL COPY data From Text File 30 minLecture13.5
-
PostgreSQL COPY data to Text File 30 minLecture13.6
-
PostgreSQL Clone Table 20 minLecture13.7
-
Postgres Database Schema Migration 30 minLecture13.8
-
PostgreSQL Database clone/migration 30 minLecture13.9
-
PostgreSQL Compress backup format 30 minLecture13.10
-
PostgreSQL Archivelog 30 minLecture13.11
-
PostgreSQL Point In Time Recovery 30 minLecture13.12
-
Taking Backup On Postgres Slave (Standby) Server 30 minLecture13.13
-
-
POSTGRESQL PERFORMANCE TUNING
5-
PostgreSQL Index 30 minLecture14.1
-
PostgreSQL Reindex 30 minLecture14.2
-
PostgreSQL PerformanceTuning 30 minLecture14.3
-
Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 minLecture14.4
-
Postgres Autovacuum Configuration/Setup 30 minLecture14.5
-
-
HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
11-
POSTGRESQL STREAMING REPLICATION 30 minLecture15.1
-
SSL Streaming replication with PostgreSQL 10 30 minLecture15.2
-
How to make Linux partition and configure the network on Redhat Linux7.3 30 minLecture15.3
-
How To Fix Firewall Issues while connecting the other server? 30 minLecture15.4
-
How to install the PostgreSQL10 On Linux ? 30 minLecture15.5
-
How to Configure the cascade replication On PostgreSQL 10.3 ? 30 minLecture15.6
-
How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 minLecture15.7
-
PostgreSQL Switchover 30 minLecture15.8
-
Postgres Failover 30 minLecture15.9
-
Postgres Upgrade 30 minLecture15.10
-
PostgreSQL Upgrade 9.5 to 11.3 . 30 minLecture15.11
-
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 uidEXAMPLE 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 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 | | {}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 ROLE2.Dropping group by using owned by command:
If grouped user having any objects means you cannot drop the group by using simple drop 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 ROLEGranting bulk of privileges to some user:
— creating group
postgres=# create group gp1;
CREATE ROLEsyntax:
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;