ApsaraDB RDS:ApsaraDB RDS for PostgreSQL インスタンス内のオブジェクトの所有者を変更する

背景情報

PostgreSQL のオブジェクト階層は インスタンス > データベース > スキーマ > テーブル/ビュー/シーケンス/関数 です。したがって、オブジェクトの所有者を変更する場合、データベースから始まり、次にスキーマ、最後に特定のテーブル、ビュー、シーケンス、関数というように、各レベルで操作を実行する必要があります。

注意事項

ステップ 3: 所有者を変更するための SQL 文をバッチ生成する を実行する場合、pgAdmin クライアントや PostgreSQL コマンドラインインターフェイスなどのクライアントを使用して ApsaraDB RDS for PostgreSQL インスタンスに接続し、関連する SQL 文を実行する必要があります。DMS でこの操作を実行しないでください。実行結果に NOTICE 出力が含まれず、後続の操作に影響するためです。

1. データベースの所有者を変更する

ApsaraDB RDS コンソールでは、ユーザーインターフェース (UI) 上で直接データベースの所有者を変更できます。

1. [インスタンス] ページに移動します。上部のナビゲーションバーで、RDS インスタンスが存在するリージョンを選択します。次に、RDS インスタンスを見つけて、インスタンスの ID をクリックします。

2. 左側のナビゲーションウィンドウで、[データベース] をクリックします。

3. 対象のデータベースの [アクション] 列で、所有者を変更します。

   

2. スキーマの所有者を変更する

1. pgAdmin クライアントまたは PostgreSQL コマンドラインインターフェイスを使用して ApsaraDB RDS for PostgreSQL インスタンスに接続します。

   psql -U <ApsaraDB RDS for PostgreSQL インスタンスのユーザー名> -h <インスタンスの内部またはパブリックエンドポイント> -p <エンドポイントに対応するポート番号>

   詳細については、「ApsaraDB RDS for PostgreSQL インスタンスの内部およびパブリックエンドポイントとポート番号を表示する」をご参照ください。

2. 次の SQL 文を実行して、現在のデータベース内のビジネススキーマをクエリします:

   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 文を実行して、指定したスキーマの所有者をターゲットユーザーに変更します:

   ALTER schema <ビジネススキーマの名前を入力> OWNER TO <ターゲットオーナーの名前を入力>;

   説明

   • 権限エラーを防ぐために、特権アカウントを使用して上記のコマンドを実行することをお勧めします。

   • 特定のビジネススキーマの所有者のみを変更したい場合は、ステップ 2.1 とステップ 2.3 を実行するだけで済みます。

   • データベース内のすべてのビジネススキーマの所有者を変更するには、ステップ 2.2 で見つかった各ビジネススキーマに対してステップ 2.3 を繰り返す必要があります。

4. 次の SQL 文を実行して、スキーマの所有者が変更されたことを確認します:

   SELECT schema_name, schema_owner FROM information_schema.schemata where schema_name = 'ビジネススキーマの名前を入力';

3. スキーマ内のテーブル、ビュー、または関数の所有者を変更する

1. pgAdmin クライアントまたは PostgreSQL コマンドラインインターフェイスを使用して ApsaraDB RDS for PostgreSQL インスタンスに接続します。

   psql -U <ApsaraDB RDS for PostgreSQL インスタンスのユーザー名> -h <インスタンスの内部またはパブリックエンドポイント> -p <エンドポイントに対応するポート番号>

   詳細については、「ApsaraDB RDS for PostgreSQL インスタンスの内部およびパブリックエンドポイントとポート番号を表示する」をご参照ください。

2. テーブル、ビュー、またはシーケンスの所有者を変更する

   次の SQL 文を実行して、指定されたオブジェクト (テーブル、ビュー、またはシーケンス) の所有者を変更します:

   ALTER table schema_name.object OWNER TO new_owner;

   次の表にパラメーターを説明します。

   • schema_name: オブジェクトが属するスキーマの名前。

   • object: テーブル、ビュー、またはシーケンスの名前。

   • new_owner: ターゲットオーナーのユーザー名。

3. 関数の所有者を変更する

   次の SQL 文を実行して、指定されたオブジェクト (関数) の所有者を変更します:

   ALTER function schema_name.function OWNER TO new_owner;

   次の表にパラメーターを説明します。

   • schema_name: 関数が属するスキーマの名前。

   • 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. スキーマ内のすべてのオブジェクトの所有者をバッチで変更するには、次のコマンドを使用して SQL 文を生成できます:

      重要

      • SQL 実行クライアントの制限: 次の SQL 文は、DMS で実行された場合、NOTICE 出力を生成しません。したがって、psql や pgAdmin などのクライアントを使用して文を実行する必要があります。

      • システムスキーマの制限: pg_toast スキーマ内の TOAST テーブルの所有者は変更できません。これはシステムスキーマであり、その所有者を変更できないためです。ただし、これは通常の操作には影響せず、一般ユーザーは引き続きこれらのテーブルにアクセスできます。

      • パーティションテーブルと外部キーを持つテーブル: パーティションテーブルと外部キーを持つテーブルの所有者の変更は、標準テーブルの場合と同じです。以下の手順で、これらのオブジェクトの所有者の変更が自動的に処理されます。

      DO $$
      DECLARE
      r record;
      i int;
      v_schema text[] := '{public,schema_name}'; -- 変更するスキーマ名の配列を入力します。複数の schema_name 値を入力できます。ただし、スキーマに多数のテーブルが含まれている場合は、ビジネスに影響を与えないように、スキーマごとにステートメントを実行することをお勧めします。
      v_new_owner varchar := 'owner_name';   -- ターゲットオーナーのユーザー名。
      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 データベースで、指定されたスキーマ (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 = 'オブジェクトが属するスキーマの名前' 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 = '関数が属するスキーマの名前' AND p.proname = '関数の名前';

適用対象

ApsaraDB RDS for PostgreSQL