views of postgresql user password and encrypted or unencrypted

標籤：

password_encryption = on
postgres=# create user user1 with encrypted password ‘user1‘;
CREATE ROLE
postgres=# create user user2 with unencrypted password ‘user2‘;
CREATE ROLE
postgres=# create user user3 with password ‘user3‘;
CREATE ROLE
postgres=# select usename,passwd from pg_shadow;
usename | passwd
----------+-------------------------------------
postgres |
user1 | md57d1b5a4329b6478e976508ab9a49ee3d
user2 | user2
user3 | md516bd93afc66e593f3aeedecdf1201ee6
(4 rows)
postgres=# select rolname,rolpassword from pg_authid ;
rolname | rolpassword
----------+-------------------------------------
postgres |
user1 | md57d1b5a4329b6478e976508ab9a49ee3d
user2 | user2
user3 | md516bd93afc66e593f3aeedecdf1201ee6
(4 rows)
以下兩個視圖顯示加密的密碼
postgres=# select usename,passwd from pg_user;
usename | passwd
----------+----------
postgres | ********
user1 | ********
user2 | ********
user3 | ********
(4 rows)
postgres=# select rolname,rolpassword from pg_roles;
rolname | rolpassword
----------+-------------
postgres | ********
user1 | ********
user2 | ********
user3 | ********
(4 rows)

password_encryption = off
postgres=# select usename,passwd from pg_shadow;
usename | passwd
----------+-------------------------------------
postgres |
user1 | md57d1b5a4329b6478e976508ab9a49ee3d
user2 | user2
user3 | md516bd93afc66e593f3aeedecdf1201ee6
(4 rows)

postgres=# select rolname,rolpassword from pg_authid ;
rolname | rolpassword
----------+-------------------------------------
postgres |
user1 | md57d1b5a4329b6478e976508ab9a49ee3d
user2 | user2
user3 | md516bd93afc66e593f3aeedecdf1201ee6
(4 rows)

postgres=# select usename,passwd from pg_user;
usename | passwd
----------+----------
postgres | ********
user1 | ********
user2 | ********
user3 | ********
(4 rows)

postgres=# select rolname,rolpassword from pg_roles;
rolname | rolpassword
----------+-------------
postgres | ********
user1 | ********
user2 | ********
user3 | ********
(4 rows)
postgres=# create user user4 with password ‘user4‘;
CREATE ROLE
postgres=# create user user5 with encrypted password ‘user5‘;
CREATE ROLE
postgres=# create user user6 with unencrypted password ‘user6‘;
CREATE ROLE
postgres=# select usename,passwd from pg_shadow;
usename | passwd
----------+-------------------------------------
postgres |
user1 | md57d1b5a4329b6478e976508ab9a49ee3d
user2 | user2
user3 | md516bd93afc66e593f3aeedecdf1201ee6
user4 | user4
user5 | md51dc34834df4da4804236eb250118fb41
user6 | user6
(7 rows)

postgres=# select rolname,rolpassword from pg_authid ;
rolname | rolpassword
----------+-------------------------------------
postgres |
user1 | md57d1b5a4329b6478e976508ab9a49ee3d
user2 | user2
user3 | md516bd93afc66e593f3aeedecdf1201ee6
user4 | user4
user5 | md51dc34834df4da4804236eb250118fb41
user6 | user6
(7 rows)

postgres=# select usename,passwd from pg_user;
usename | passwd
----------+----------
postgres | ********
user1 | ********
user2 | ********
user3 | ********
user4 | ********
user5 | ********
user6 | ********
(7 rows)

postgres=# select rolname,rolpassword from pg_roles;
rolname | rolpassword
----------+-------------
postgres | ********
user1 | ********
user2 | ********
user3 | ********
user4 | ********
user5 | ********
user6 | ********
(7 rows)

postgres=# alter user postgres with password ‘postgres‘;
ALTER ROLE
postgres=# select usename,passwd from pg_shadow;
usename | passwd
----------+-------------------------------------
user1 | md57d1b5a4329b6478e976508ab9a49ee3d
user2 | user2
user3 | md516bd93afc66e593f3aeedecdf1201ee6
user4 | user4
user5 | md51dc34834df4da4804236eb250118fb41
user6 | user6
postgres | postgres
(7 rows)

postgres=# alter user user1 with password ‘pguser1‘;
ALTER ROLE
postgres=# select usename,passwd from pg_shadow;
usename | passwd
----------+-------------------------------------
user2 | user2
user3 | md516bd93afc66e593f3aeedecdf1201ee6
user4 | user4
user5 | md51dc34834df4da4804236eb250118fb41
user6 | user6
postgres | postgres
user1 | pguser1
(7 rows)

views of postgresql user password and encrypted or unencrypted