-
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 Password Changing and Views
change the Database User Password
In this post, I am sharing basic commands to change the PostgreSQL database user password.
I would like to share this kind commands and scripts because this is very common and day to day exercise for a Database Administrator.
In the PostgreSQL, we have also one pg_hba.conf file.
pg_hba.conf is a configuration file which is controlled Client Authentication.
This file automatically installed when the data directory is initialized.
If you want to login without password, you can put “peer” or “trust” instead of md5 in this file.
like,
local all postgres peerBut for the security purpose, we should put md5.
Initially we will create sample encrypted user
create user u3 WITH ENCRYPTED PASSWORD 'u3';
create user u4 WITH ENCRYPTED PASSWORD 'u4';Note the user password encrypted values this values will be change after changed user password
Before Changing password
postgres=# select usename,usesysid,passwd,valuntil,useconfig from PG_SHADOW;
usename | usesysid | passwd | valuntil | useconfig
----------+----------+-------------------------------------+----------+-----------
u5 | 16393 | md507a832ae72c9e818c5297f366284fb8a | |
postgres | 10 | md53175bce1d3201d16594cebf9d7eb3f9d | |
u3 | 16416 | md5dad1ef51b879799793dc38d714b97063 | |
u4 | 16417 | md54af10c3137cf79c12265e8d288070711 | |You can change postgresl user password following two ways
1.Change u3 user password using ALTER COMMAND:
First login as postgres super user:
psql -U postgres -d postgresChanging user password using ALTER sql Command:
ALTER USER u3 WITH PASSWORD 'MyNew_Password';2.Changing u4 user password using \password:
First login as postgres super user:
psql -U postgres -d postgresUsing \password you can change user password while issuing the command password will prompt 2 time you have to give same password while prompting:
postgres=# \password u4
Enter new password:
Enter it again:After password changed
postgres=# select usename,usesysid,passwd,valuntil,useconfig from PG_SHADOW;
usename | usesysid | passwd | valuntil | useconfig
----------+----------+-------------------------------------+----------+-----------
u5 | 16393 | md507a832ae72c9e818c5297f366284fb8a | |
postgres | 10 | md53175bce1d3201d16594cebf9d7eb3f9d | |
u3 | 16416 | md5e3da90548c84e1c04f27cc674aa19a28 | |
u4 | 16417 | md5ce9d5d4c1d5d47af4bc532d53a7fc0d7 | |
(4 rows)Note: Before password changing and after password changing compare both u3 and u4 user password encrypted value
AWARENESS ABOUT PG_SHADOW & PG_AUTHID VIEWS:
PG_SHADOW is views its contains information about rolename and user password,user password validity,user connection limit and Role automatically inherits privileges of roles it is a member of,detailed information about user and privilege management.
| Name | Type | Description |
| usename | name | User name |
| usesysid | oid | ID of this user |
| usecreatedb | bool | User can create databases |
| usesuper | bool | User is a superuser |
| usecatupd | bool | User can update system catalogs. (Even a superuser cannot do this unless this column is true.) |
| passwd | text | Password (possibly encrypted); null if none. See pg_authid for details of how encrypted passwords are stored. |
| valuntil | abstime | Password expiry time (only used for password authentication) |
| useconfig | text[] | Session defaults for run-time configuration variables |
Examples
— Creating password unencrypted user
postgres=# create user u2 WITH UNENCRYPTED PASSWORD 'u2';
CREATE ROLE— Creating the user with password validation time ,this is one of the method in security maintenance once 06-06-2019 is reached means dba need to reset his/her validation time
postgres=# CREATE USER u3 WITH PASSWORD 'u3' VALID UNTIL '2019-06-06';
CREATE ROLE— listing users password and password validation time
postgres=# select usename,usesysid,passwd,valuntil,useconfig from PG_SHADOW;
usename | usesysid | passwd | valuntil | useconfig
----------+----------+-------------------------------------+------------------------+-----------
postgres | 10 | md505ea766c2bc9e19f34b66114ace97598 | |
rep | 24576 | md5df2c887bcb2c49b903aa33bdbc5c2984 | |
u1 | 24583 | | |
u2 | 24584 | u2 | |
u3 | 24585 | md5dad1ef51b879799793dc38d714b97063 | 2019-06-06 00:00:00-04 |
nijam | 24586 | | |
(6 rows)PG_AUTHID is views its contains information about rolename and user password,user password validity,user connection limit and Role automatically inherits privileges of roles it is a member of,detailed information about user and privilege management.
| Name | Type | Description |
| rolname | name | Role name |
| rolsuper | bool | Role has superuser privileges |
| rolinherit | bool | Role automatically inherits privileges of roles it is a member of |
| rolcreaterole | bool | Role can create more roles |
| rolcreatedb | bool | Role can create databases |
| rolcatupdate | bool | Role can update system catalogs directly. (Even a superuser cannot do this unless this column is true) |
| rolcanlogin | bool | Role can log in. That is, this role can be given as the initial session authorization identifier |
| rolreplication | bool | Role is a replication role. That is, this role can initiate streaming replication and set/unset the system backup mode usingpg_start_backup and pg_stop_backup |
| rolconnlimit | int4 | For roles that can log in, this sets maximum number of concurrent connections this role can make. -1 means no limit. |
| rolpassword | text | Password (possibly encrypted); null if none. If the password is encrypted, this column will begin with the string md5 followed by a 32-character hexadecimal MD5 hash. The MD5 hash will be of the user’s password concatenated to their user name. For example, if user joe has password xyzzy, PostgreSQL will store the md5 hash of xyzzyjoe. A password that does not follow that format is assumed to be unencrypted. |
| rolvaliduntil | timestamptz | Password expiry time (only used for password authentication); null if no expiration |
Examples
— creating unencrypted user
postgres=# create user u2 WITH UNENCRYPTED PASSWORD 'u2';
CREATE ROLE— create the user with password validation time
postgres=# CREATE USER u3 WITH PASSWORD 'u3' VALID UNTIL '2017-06-06';
CREATE ROLE— listing users password and password validation time
postgres=# select rolname,rolpassword,rolvaliduntil from pg_authid;
rolname | rolpassword | rolvaliduntil
----------+-------------------------------------+------------------------
postgres | md505ea766c2bc9e19f34b66114ace97598 |
rep | md5df2c887bcb2c49b903aa33bdbc5c2984 |
u1 | |
u2 | u2 |
u3 | md5dad1ef51b879799793dc38d714b97063 | 2017-06-06 00:00:00-04
(5 rows)In PostgreSQL 10:
UNENCRYPTED PASSWORD is no longer supported after postgresql 10 Before postgresql 10 allowed to create user with unencrypted password so password will be stored as md5 encrypted type
Here i used postgresql 10.4 previous above example was postgresql 9.3
postgres=# create user u3 WITH UNENCRYPTED PASSWORD 'u3';
ERROR: UNENCRYPTED PASSWORD is no longer supported
LINE 1: create user u3 WITH UNENCRYPTED PASSWORD 'u3';
^
HINT: Remove UNENCRYPTED to store the password in encrypted form instead.
postgres=#UNENCRYPTED PASSWORD is no longer supported after postgresql 10 , Before postgresql 10 allowed to create user with unencrypted password