-
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
-
Alter PostgreSQL User/Role/group
- ALTER ROLE changes the attributes of a PostgreSQL role.
- If you want to change a user you can use the ALTER USER SQL command, which is similar to the CREATE USER command except you can’t change the sysid
Syntax:
ALTER USER role_specification [ WITH ] option [ ... ] where option can be: SUPERUSER | NOSUPERUSER | CREATEDB | NOCREATEDB | CREATEROLE | NOCREATEROLE | CREATEUSER | NOCREATEUSER | INHERIT | NOINHERIT | LOGIN | NOLOGIN | REPLICATION | NOREPLICATION | BYPASSRLS | NOBYPASSRLS | CONNECTION LIMIT connlimit | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'timestamp' ALTER USER name RENAME TO new_name ALTER USER role_specification SET configuration_parameter { TO | = } { value | DEFAULT } ALTER USER role_specification SET configuration_parameter FROM CURRENT ALTER USER role_specification RESET configuration_parameter ALTER USER role_specification RESET ALL where role_specification can be: [ GROUP ] role_name | CURRENT_USER | SESSION_USER
Example:
practical 1.Listing users
- You can see the users on the server by selecting from the pg_shadow & pg_authid system table. If you are not a super user, you will not have permission to access this table and will have to access the pg_user view instead, which is identical, but displays the password as stars.
db2=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+-------------------------------------+-----------------------------------+--- postgres | 10 | t | t | t | t | md505ea766c2bc9e19f34b66114ace97598 | | rep | 24576 | f | f | f | t | md5df2c887bcb2c49b903aa33bdbc5c2984 | | nijam | 24586 | f | f | f | f | | | u8 | 24591 | t | t | t | f | md5b9f930ae0484417a1883fd3f7cdb490e |2015-05-09 12:00:00-04| u9 | 24640 | f | f | f | f | md531f95351422eab63b8b270c140f60c2a |2015-05-04 12:00:00-04| u1 | 24648 | f | f | f | f | md58026a39c502750413402a90d9d8bae3c | | u2 | 24649 | f | f | f | f | md5a76d8c8015643c6a837661a10142016e | | u4 | 24657 | f | f | f | f | md54af10c3137cf79c12265e8d288070711 | | u5 | 24658 | f | f | f | f | md507a832ae72c9e818c5297f366284fb8a | | u3 | 24660 | f | f | f | f | | | (10 rows)
db2=# select * from pg_user; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+----------+----------------------+----------- postgres | 10 | t | t | t | t | ******** | | rep | 24576 | f | f | f | t | ******** | | nijam | 24586 | f | f | f | f | ******** | | u8 | 24591 | t | t | t | f | ******** |2015-05-09 12:00:00-04| u9 | 24640 | f | f | f | f | ******** |2015-05-04 12:00:00-04| u1 | 24648 | f | f | f | f | ******** | | u2 | 24649 | f | f | f | f | ******** | | u4 | 24657 | f | f | f | f | ******** | | u5 | 24658 | f | f | f | f | ******** | | u3 | 24660 | f | f | f | f | ******** | | (10 rows)
db2=# select * from pg_authid; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil ----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------------------------------+--------------- postgres | t | t | t | t | t | t | t | -1 | md505ea766c2bc9e19f34b66114ace97598 | rep | f | t | f | f | f | t | t | 1 | md5df2c887bcb2c49b903aa33bdbc5c2984 | nijam | f | t | f | f | f | t | f | -1 | | u8 | t | t | t | t | t | t | f | -1 | md5b9f930ae0484417a1883fd3f7cdb490e |2015-05-09 12:00:00-04 u9 | f | t | f | f | f | t | f | -1 | md531f95351422eab63b8b270c140f60c2a |2015-05-04 12:00:00-04 u1 | f | t | f | f | f | t | f | -1 | md58026a39c502750413402a90d9d8bae3c | u2 | f | t | f | f | f | t | f | -1 | md5a76d8c8015643c6a837661a10142016e | u4 | f | t | f | f | f | t | f | -1 | md54af10c3137cf79c12265e8d288070711 | u5 | f | t | f | f | f | t | f | -1 | md507a832ae72c9e818c5297f366284fb8a | u3 | f | t | f | f | f | t | f | -1 | | (10 rows)
–Changing a user password
One of the most common reasons for wanting to alter a user is to change the user’s password
db2=# alter user u2 with password 'u23'; ALTER ROLE
–Remove a role’s password
db2=# ALTER ROLE u5 WITH PASSWORD NULL; ALTER ROLE
–Change a password expiration date from “May 4 12:00:00 2015” to “May 8 12:00:00 2018”
db2=# ALTER ROLE u9 VALID UNTIL 'May 8 12:00:00 2018'; ALTER ROLE
–Make a password valid forever:
db2=# ALTER ROLE u8 VALID UNTIL 'infinity'; ALTER ROLE
–Say we wanted to allow u1 user to create databases and create role
db2=# ALTER ROLE u1 CREATEROLE CREATEDB; ALTER ROLE
–Checking pg_shadow again, we can see the changes highligted in red color
db2=# select * from pg_shadow; usename | usesysid | usecreatedb | usesuper | usecatupd | userepl | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+---------+-------------------------------------+------------------------+----------- postgres | 10 | t | t | t | t | md505ea766c2bc9e19f34b66114ace97598 | | rep | 24576 | f | f | f | t | md5df2c887bcb2c49b903aa33bdbc5c2984 | | nijam | 24586 | f | f | f | f | | | u4 | 24657 | f | f | f | f | md54af10c3137cf79c12265e8d288070711 | | u1 | 24648 | t | f | f | f | md53ac33e1b7b89b332aef1b757828dc8eb | | u2 | 24649 | f | f | f | f | md57a7c3259d1ebc74d9119be56686b591a | | u3 | 24660 | f | f | f | f | | | u5 | 24658 | f | f | f | f | | | u9 | 24640 | f | f | f | f | md531f95351422eab63b8b270c140f60c2a | 2018-05-08 12:00:00-04 | u8 | 24591 | t | t | t | f | md5b9f930ae0484417a1883fd3f7cdb490e | infinity | (10 rows)
–Rename the user from nijam to nijamutheen
db2=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
nijam | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
rep | Replication +| {}
| 1 connection |
u1 | Create role, Create DB | {}
u2 | | {}
u3 | | {}
u4 | | {}
u5 | | {}
u8 | Superuser, Create role, Create DB +| {}
| Password valid until infinity |
u9 | Password valid until 2018-05-08 12:00:00-04 | {}
db2=# alter user nijam rename to nijamutheen; ALTER ROLE
db2=# \du
List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------+-----------
nijamutheen | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
rep | Replication +| {}
| 1 connection |
u1 | Create role, Create DB | {}
u2 | | {}
u3 | | {}
u4 | | {}
u5 | | {}
u8 | Superuser, Create role, Create DB +| {}
| Password valid until infinity |
u9 | Password valid until 2018-05-08 12:00:00-04 | {}
Next
PostgreSQL Schema