PostgreSQL角色迁移原理简述

概述

本文描述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视图详解

视图字段说明

列名	列类型	描述
rolname	name	角色名。
rolsuper	bool	角色具有超级用户权限。
rolinherit	bool	如果此角色是另一个角色的成员，角色能自动继承另一个角色的权限。
rolcreaterole	bool	角色能创建更多角色。
rolcreatedb	bool	角色能创建数据库。
rolcanlogin	bool	角色是否能登录。即该角色是否能够作为初始会话授权标识符。
rolreplication	bool	角色是一个复制角色。复制角色可以开启复制连接并且创建和删除复制槽。
rolconnlimit	int4	对于一个可登录的角色，这里设置角色可以发起的最大并发连接数。-1表示无限制。
rolpassword	text	不显示密码，一直显示********。
rolvaliduntil	timestamptz	口令失效时间（只用于口令认证），如果永不失效则为空
rolbypassrls	bool	绕过每一条行级安全性策略的角色。
rolconfig	text[]	运行时配置变量的角色特定默认值。
oid	oid	角色的ID。

视图定义说明

通过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 哈希值将是该用户的口令串接上它们的用户名。

列名	列类型	描述
rolname	name	角色名。
rolsuper	bool	角色具有超级用户权限。
rolinherit	bool	如果此角色是另一个角色的成员，角色能自动继承另一个角色的权限。
rolcreaterole	bool	角色能创建更多角色。
rolcreatedb	bool	角色能创建数据库。
rolcanlogin	bool	角色是否能登录。即该角色是否能够作为初始会话授权标识符。
rolreplication	bool	角色是一个复制角色。复制角色可以开启复制连接并且创建和删除复制槽。
rolconnlimit	int4	对于一个可登录的角色，这里设置角色可以发起的最大并发连接数。-1表示无限制。
rolpassword	text	密码（可能被加密过），如果没有口令则为空。格式取决于使用的加密方法的形式。
rolvaliduntil	timestamptz	口令失效时间（只用于口令认证），如果永不失效则为空
rolbypassrls	bool	绕过每一条行级安全性策略的角色。
rolconfig	text[]	运行时配置变量的角色特定默认值。
oid	oid	角色的ID。

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 );