• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – MySQL
    • Blog – Linux
  • Forums
      • Cart

        0

    Have any question?
    (+91) 8838953252
    support@2ndquadrant.in
    RegisterLogin
    RayaFeeLRayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – MySQL
      • Blog – Linux
    • Forums
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL 0

        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION 1

        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE 0

        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE 0

        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING 1

        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC 0

        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT 1

        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS 0

        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE 0

        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS 0

        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE 0

        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE 0

        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY 2

        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING 2

        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION 11

        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        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     peer

        But 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 postgres

        Changing 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 postgres

        Using \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

         

        Prev PostgreSQL Drop User Using Utility
        Next PostgreSQL Group/role

        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $250.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres
        • postgres script for finding queries more than 1 hours ?
        • How to take the tables backup in postgres
        • what are the extensions required to be in instance for performance

        2ndquadrant.in

        (+91) 8838953252

        contact@2ndquadrant.in

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Forums
        • Postgres Migration Blogs

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now