0 like 0 dislike
218 views
asked by (13.4k points)  
edited by
If I had forgot the Postgres password
How can we find the Postgres password

Please log in or register to answer this question.

1 Answer

0 like 0 dislike
answered by (13.4k points)  
edited by

You can see your password Following  tw option

  1. PG_SHADOW view
  2.  PG_AUTHID View

1.PG_SHADOW:

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
--create the 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
--Describe the  PG_SHADOW view
postgres=# \d  PG_SHADOW
    View "pg_catalog.pg_shadow"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 usename     | name    | 
 usesysid    | oid     | 
 usecreatedb | boolean | 
 usesuper    | boolean | 
 usecatupd   | boolean | 
 userepl     | boolean | 
 passwd      | text    | 
 valuntil    | abstime | 
 useconfig   | text[]  | 
--list the user and show the users password and password validatation 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 | 2017-06-06 00:00:00-04 | 
 nijam    |    24586 |                                     |                        | 
(6 rows)

2.PostgreSQL PG_AUTHID View

 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
--create the 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
--Describe the pg_authid view
postgres=# \d  pg_authid
             Table "pg_catalog.pg_authid"
     Column     |           Type           | Modifiers 
----------------+--------------------------+-----------
 rolname        | name                     | not null
 rolsuper       | boolean                  | not null
 rolinherit     | boolean                  | not null
 rolcreaterole  | boolean                  | not null
 rolcreatedb    | boolean                  | not null
 rolcatupdate   | boolean                  | not null
 rolcanlogin    | boolean                  | not null
 rolreplication | boolean                  | not null
 rolconnlimit   | integer                  | not null
 rolpassword    | text                     | 
 rolvaliduntil  | timestamp with time zone | 
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global" 
--list the user and show the users password and password validatation 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)

Related questions

0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
...