ApsaraDB RDS：如何修改RDS PostgreSQL執行個體下的對象Owner

背景資訊

PostgreSQL的對象層級結構為執行個體 > 資料庫 > Schema > 表/視圖/序列/函數。因此在修改對象所有者時，需要逐層操作，從資料庫到Schema，再到具體的表、視圖、序列和函數。

注意事項

若參考本文步驟3：批量產生修改所有者的SQL時，請使用pgAdmin用戶端或PostgreSQL命令列工具等串連RDS PostgreSQL執行個體後執行相關SQL，避免在DMS中操作，否則執行結果中不會有NOTICE輸出，影響後續操作。

1. 修改資料庫的Owner

在RDS控制台中，您可以通過圖形化介面直接修改某個資料庫的所有者。

1. 訪問RDS執行個體列表，在上方選擇地區，然後單擊目標執行個體ID。

2. 在左側導覽列單擊資料庫管理。

3. 在目標資料庫的操作列下，修改Owner。

   

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版