通过如下方式先查询角色及密码:
highgo=# \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
rolcanlogin | boolean | not null
rolreplication | boolean | not null
rolbypassrls | 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"
highgo=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
---------+-------------------------------------
highgo |
test | md505a671c66aefea124cc08b76ea6d30bb
(2 rows)
新库中执行如下方法重建新角色:
highgo=# create user test;
CREATE ROLE
highgo=# update pg_authid set rolpassword='md505a671c66aefea124cc08b76ea6d30bb' where rolname='test';
UPDATE 1
highgo=# create user aaa;
CREATE ROLE
highgo=# update pg_authid set rolpassword='md505a671c66aefea124cc08b76ea6d30bb' where rolname='aaa';
UPDATE 1
highgo=# \q
[highgo@hg highgo]$
说明:使用同一个md5密文,需确保新旧数据库的数据库名字必须一致,否则创建的其他的用户名使用此md5密文将无法登录,除非修改密码。
highgo=# \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
rolcanlogin | boolean | not null
rolreplication | boolean | not null
rolbypassrls | 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"
highgo=# select rolname,rolpassword from pg_authid;
rolname | rolpassword
---------+-------------------------------------
highgo |
test | md505a671c66aefea124cc08b76ea6d30bb
(2 rows)
新库中执行如下方法重建新角色:
highgo=# create user test;
CREATE ROLE
highgo=# update pg_authid set rolpassword='md505a671c66aefea124cc08b76ea6d30bb' where rolname='test';
UPDATE 1
highgo=# create user aaa;
CREATE ROLE
highgo=# update pg_authid set rolpassword='md505a671c66aefea124cc08b76ea6d30bb' where rolname='aaa';
UPDATE 1
highgo=# \q
[highgo@hg highgo]$
说明:使用同一个md5密文,需确保新旧数据库的数据库名字必须一致,否则创建的其他的用户名使用此md5密文将无法登录,除非修改密码。