概述

本文描述PostgreSQL角色迁移的流程，基于该方案可通过Java、Python等语言开发PostgreSQL角色迁移工具。

使用pg_dumpall导出角色

通过PostgreSQL数据库提供的pg_dumpall命令可以导出roles定义。

$ pg_dumpall --roles only
CREATE ROLE test_role;
ALTER ROLE test_role WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS PASSWORD 'md5aba4a945c8d93f0f2b8e49558964fe4e';

pg_dumpall导出角色原理

pg_dumpall主要是调用了代码文件pg_dumall.c中函数dumpRoles。该函数主要执行两个步骤，先是查询角色的相关数据，然后将数据拼凑成角色的SQL定义语句。此处不对拼凑SQL定义语句部分做分析，仅分析查询角色的部分。

if (server_version >= 90600)
		printfPQExpBuffer(buf,
						  "SELECT oid, rolname, rolsuper, rolinherit, "
						  "rolcreaterole, rolcreatedb, "
						  "rolcanlogin, rolconnlimit, rolpassword, "
						  "rolvaliduntil, rolreplication, rolbypassrls, "
						  "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
						  "rolname = current_user AS is_current_user "
						  "FROM %s "
						  "WHERE rolname !~ '^pg_' "
						  "ORDER BY 2", role_catalog, role_catalog);
	else if (server_version >= 90500)
		printfPQExpBuffer(buf,
						  "SELECT oid, rolname, rolsuper, rolinherit, "
						  "rolcreaterole, rolcreatedb, "
						  "rolcanlogin, rolconnlimit, rolpassword, "
						  "rolvaliduntil, rolreplication, rolbypassrls, "
						  "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
						  "rolname = current_user AS is_current_user "
						  "FROM %s "
						  "ORDER BY 2", role_catalog, role_catalog);
	else
		printfPQExpBuffer(buf,
						  "SELECT oid, rolname, rolsuper, rolinherit, "
						  "rolcreaterole, rolcreatedb, "
						  "rolcanlogin, rolconnlimit, rolpassword, "
						  "rolvaliduntil, rolreplication, "
						  "false as rolbypassrls, "
						  "pg_catalog.shobj_description(oid, '%s') as rolcomment, "
						  "rolname = current_user AS is_current_user "
						  "FROM %s "
						  "ORDER BY 2", role_catalog, role_catalog);

根据源码中显示，PostgreSQL会根据版本不同，执行不同的查询语句，表名通过role_catelog传入。
通过源代码追溯pg_dumall.c中函数main函数中对变量role_catelog进行了赋值

#define PG_AUTHID "pg_authid"
#define PG_ROLES  "pg_roles "

	if (no_role_passwords)
		sprintf(role_catalog, "%s", PG_ROLES);
	else
		sprintf(role_catalog, "%s", PG_AUTHID);

如果不需要导出角色的密码，查询视图pg_roles；如果要导出角色密码，查询表pg_authid。
通过上述代码分析，可得出只要查询pg_roles或`pg_authid，即可获取到角色的信息，从而用于角色的SQL定义拼凑。

pg_roles视图详解

视图字段说明

视图定义说明

通过PostgreSQL源代码中的system_views.sql文件可知，该视图是表pg_authid的一个公共可读视图，隐去了密码字段。

CREATE VIEW pg_roles AS
    SELECT
        rolname,
        rolsuper,
        rolinherit,
        rolcreaterole,
        rolcreatedb,
        rolcanlogin,
        rolreplication,
        rolconnlimit,
        '********'::text as rolpassword,
        rolvaliduntil,
        rolbypassrls,
        setconfig as rolconfig,
        pg_authid.oid
    FROM pg_authid LEFT JOIN pg_db_role_setting s
    ON (pg_authid.oid = setrole AND setdatabase = 0);

pg_authid表详解

• pg_authid包含关于数据库授权标识符（角色）的信息。
• 对于一个MD5加密的口令,rolpassword列将由字符串md5后面跟上一个 32 字符的十六进制 MD5 哈希值构成。MD5 哈希值将是该用户的口令串接上它们的用户名。

PostgreSQL角色迁移方案

综上所述，PostgreSQL角色迁移就是通过查询表pg_authid获取到角色的属性、密码等信息，从而拼凑SQL，就可以进行角色迁移。迁移的方式有以下两种：

参照pg_dumpall的方式拼凑SQL

create role xxx;
alter role xxx with xxx  xxxx xxx password 'xxxx';

生成INSERT语句，将角色直接插入到pg_authid

• 需要注意主键冲突；
• 由于PostgreSQL的角色密码中包含角色名，因此无法实现角色名映射迁移。

INSERT INTO "pg_authid" ( "oid", "rolname", "rolsuper", "rolinherit", "rolcreaterole", "rolcreatedb", "rolcanlogin", "rolreplication", "rolbypassrls", "rolconnlimit", "rolpassword", "rolvaliduntil" )
VALUES
	( 36020125, 'db_user', 'f', 't', 'f', 'f', 't', 'f', 'f', - 1, 'md52957e9728db56f59bbedbb586f6224f0', NULL );