AI 助理
备案控制台
官方文档
输入文档关键字查找
首页 云数据库 RDS RDS PostgreSQL数据库 服务支持 常见问题 账号/权限 如何修改RDS PostgreSQL实例下的对象Owner

如何修改RDS PostgreSQL实例下的对象Owner

更新时间：
产品详情
我的收藏

本文介绍如何修改RDS PostgreSQL实例下所有对象（包括数据库、Schema、表、视图、序列和函数）的所有者（Owner）。

背景信息

PostgreSQL的对象层级结构为实例 > 数据库 > Schema > 表/视图/序列/函数。因此在修改对象所有者时，需要逐层操作，从数据库到Schema，再到具体的表、视图、序列和函数。

说明

如果需要修改整个实例下所有对象的所有者（Owner），需连接到对应的数据库后，重复执行本文步骤2~步骤3以覆盖所有数据库和Schema。

注意事项

若参考本文步骤3：批量生成修改所有者的SQL时，请使用pgAdmin客户端或PostgreSQL命令行工具等连接RDS PostgreSQL实例后执行相关SQL，避免在DMS中操作，否则执行结果中不会有NOTICE输出，影响后续操作。

1. 修改数据库的Owner

在RDS控制台中，您可以通过图形化界面直接修改某个数据库的所有者。

  1. 访问RDS实例列表，在上方选择地域，然后单击目标实例ID。

  2. 在左侧导航栏单击数据库管理。

  3. 在目标数据库的操作列下，修改Owner。

    image

2. 修改Schema的Owner

  1. 通过pgAdmin客户端或PostgreSQL命令行工具连接RDS PostgreSQL实例。

    psql -U <RDS PostgreSQL实例的用户名> -h <实例内网或外网地址> -p <连接地址对应端口>

    更多详情，请参见查看实例内网或外网连接地址及端口号。

  2. 执行如下SQL，查询当前数据库中的业务Schema：

    SELECT * FROM information_schema.schemata where catalog_name = '填写业务的数据库名称' and schema_name not in ('information_schema','public','pg_catalog','pg_temp_1', 'pg_toast','pg_toast_temp_1');

  3. 执行如下SQL，将指定Schema的所有者修改为目标用户：

    ALTER schema <填写业务Schema名称> OWNER TO <填写目标owner名称>;
    说明

    • 建议使用高权限账号执行如上命令，以免出现权限报错。

    • 如果只修改某个业务Schema的Owner，则只需要执行2.1和2.3即可。

    • 如需修改某个数据库下所有业务Schema的Owner，则2.2中查到几个业务Schema，就需重复执行步骤2.3。

  4. 执行如下SQL，验证Schema的所有者是否已成功修改：

    SELECT schema_name, schema_owner FROM information_schema.schemata where schema_name = '填写业务schema名称';

3. 修改schema_name下表/视图/函数的Owner

  1. 通过pgAdmin客户端或PostgreSQL命令行工具连接RDS PostgreSQL实例。

    psql -U <RDS PostgreSQL实例的用户名> -h <实例内网或外网地址> -p <连接地址对应端口>

    更多详情，请参见查看实例内网或外网连接地址及端口号。

  2. 修改表/视图/序列的所有者

    执行如下SQL，修改指定对象（表/视图/序列）的所有者：

    ALTER table schema_name.object OWNER TO new_owner;

    参数说明如下：

    • schema_name：对象所属的Schema名称。

    • object：表、视图或序列的名称。

    • new_owner：目标所有者的用户名。

  3. 修改函数的所有者

    执行如下SQL，修改指定对象（函数）的所有者：

    ALTER function schema_name.function OWNER TO new_owner;

    参数说明如下：

    • schema_name：函数所属的Schema名称。

    • function：函数的名称。

    • new_owner：目标所有者的用户名。

    说明

    若遇到如下报错，说明函数名不唯一（当前PostgreSQL数据库中存在多个同名函数），则函数名后需带参数来唯一标识目标函数。

    ERROR: function name "function_name" is not unique
NOTICE: Specify the argument list to SELECT the function unambiguously.

  4. 批量生成修改所有者的SQL，并验证是否修改成功

    1. 如需批量修改某个Schema下所有对象的所有者，可以使用以下命令生成SQL语句：

      重要

      • SQL执行工具限制：如下SQL在DMS中执行时不会有NOTICE输出，因此请务必使用psql或pgAdmin等客户端执行。

      • 系统模式限制：pg_toast模式下的TOAST表无法修改所有者，因为该模式属于系统模式，其所有者不可更改。但这不会影响正常使用，普通用户仍然可以访问这些表。

      • 分区表和外键表：分区表和外键表的所有者修改与普通表一致，下面的步骤会自动覆盖并正确处理这些对象的所有者变更。

      DO $$
DECLARE
r record;
i int;
v_schema text[] := '{public,schema_name}'; -- 填入需要修改的Schema名称数组，可填入多个schema_name，但若Schema下表较多，建议逐个执行，避免影响业务
v_new_owner varchar := 'owner_name';   -- 目标Owner的用户名
BEGIN
FOR r IN
SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = ANY (v_schema)
UNION ALL
SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = ANY (v_schema)
UNION ALL
SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = ANY (v_schema)
UNION ALL
SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = ANY (v_schema)
LOOP
RAISE NOTICE '%', r.a;
END LOOP;
END
$$;

      执行上述代码块后，会得到含NOTICE 的SQL语句，请自行确认SQL语句是否符合预期，如符合预期，复制生成的SQL语句并执行，即可完成批量修改。

      批量生成示例（含SQL命令及返回结果）

      在db1数据库中，将指定Schema（public和myschema1）下的所有数据库对象（包括表、视图、序列和函数）的所有者统一更改为目标用户（user_test）。

      db1=> DO $$
DECLARE
r record;
i int;
v_schema text[] := '{public,myschema1}';
v_new_owner varchar := 'user_test';
BEGIN
FOR r IN
SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.tables WHERE table_schema = ANY (v_schema)
UNION ALL
SELECT 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.sequences WHERE sequence_schema = ANY (v_schema)
UNION ALL
SELECT 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' AS a FROM information_schema.views WHERE table_schema = ANY (v_schema)
UNION ALL
SELECT 'ALTER FUNCTION "' || nsp.nspname || '"."' || p.proname || '"(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO ' || v_new_owner || ';' AS a FROM pg_proc p JOIN pg_namespace nsp ON p.pronamespace = nsp.oid WHERE nsp.nspname = ANY (v_schema)
LOOP
RAISE NOTICE '%', r.a;
END LOOP;
END
$$;

      则会得到如下返回：

      NOTICE: ALTER TABLE "public"."t1" OWNER TO user_test;
NOTICE: ALTER TABLE "public"."user_view_tt1" OWNER TO user_test;
NOTICE: ALTER TABLE "myschema1"."tt1" OWNER TO user_test;
NOTICE: ALTER TABLE "myschema1"."user_view_tt1" OWNER TO user_test;
NOTICE: ALTER TABLE "public"."my_seq2" OWNER TO user_test;
NOTICE: ALTER TABLE "myschema1"."my_seq2" OWNER TO user_test;
NOTICE: ALTER TABLE "public"."user_view_tt1" OWNER TO user_test;
NOTICE: ALTER TABLE "myschema1"."user_view_tt1" OWNER TO user_test;
NOTICE: ALTER FUNCTION "public"."my_demo_func"(arg1 integer) OWNER TO user_test;
DO

    2. 确认对象的所有者是否修改成功

      • 确认表/视图/序列的所有者是否已修改成功

        SELECT n.nspname AS schema_name, c.relname AS table_name , u.rolname AS owner FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_roles u ON u.oid = c.relowner WHERE n.nspname = '对象所属的Schema名称' AND c.relname = '表、视图或序列的名称';

      • 确认函数的所有者是否已修改成功

        SELECT n.nspname AS schema_name, p.proname AS function_name, u.rolname AS owner FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid JOIN pg_roles u ON p.proowner = u.oid WHERE n.nspname = '函数所属的Schema名称' AND p.proname = '函数的名称';

适用于

云数据库RDS PostgreSQL版

上一篇：账号/权限下一篇：RDS数据库实例授权其他客户登录方法