ApsaraDB RDS:RDS for PostgreSQL におけるオブジェクト所有者の変更

背景

PostgreSQL では、オブジェクトは インスタンス > データベース > スキーマ > テーブル/ビュー/シーケンス/関数 という階層構造になっています。 したがって、オブジェクトの所有者は、まずデータベース、次にスキーマ、最後にテーブル、ビュー、シーケンス、関数の順に、階層ごとに変更する必要があります。

使用上の注意

ステップ 3: 所有者を変更し、変更を検証するための SQL 文を一括で生成するの手順に従う際は、pgAdmin クライアントまたは PostgreSQL コマンドラインツールを使用して ApsaraDB RDS for PostgreSQL インスタンスに接続してください。後続のステップで必要な NOTICE 出力が抑制されるため、このステップではデータ管理 (DMS) を使用しないでください。

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

ApsaraDB RDS コンソールで、データベースの所有者を変更できます。

1. RDSインスタンスにアクセスし、上部のリージョンを選択し、対象のRDSインスタンスのIDをクリックします。

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

3. 対象のデータベースを見つけ、[操作] 列の [所有者の変更] をクリックします。

   [所有者の変更] ダイアログボックスで、[アカウントリスト] ドロップダウンリストから対象のアカウントを選択し、[OK] をクリックします。 新しいアカウントを作成する必要がある場合は、[新しいアカウントの作成] をクリックします。

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

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

   psql -U <username_of_the_instance> -h <internal_or_public_endpoint> -p <port_of_the_endpoint>

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

2. 次の SQL ステートメントを実行して、現在のデータベース内のビジネススキーマを照会します。

   SELECT * FROM information_schema.schemata where catalog_name = 'your_business_database_name' and schema_name not in ('information_schema', 'public', 'pg_catalog', 'pg_temp_1', 'pg_toast', 'pg_toast_temp_1');

3. 次の SQL ステートメントを実行して、指定したスキーマの所有者を目的のユーザーに変更します。

   ALTER schema <your_business_schema_name> OWNER TO <target_owner_name>;

   説明

   • 特権アカウントを使用してこのコマンドを実行し、権限エラーを防いでください。

   • 単一のスキーマの所有者のみを変更する場合は、手順 1 と手順 3 を実行するだけで済みます。

   • データベース内のすべてのビジネススキーマの所有者を変更するには、手順 2 のクエリで返された各スキーマについて、手順 3 を繰り返します。

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

   SELECT schema_name, schema_owner FROM information_schema.schemata where schema_name = 'your_business_schema_name';

3. スキーマ内のオブジェクト所有者の変更

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

   psql -U <username_of_the_instance> -h <internal_or_public_endpoint> -p <port_of_the_endpoint>

   詳細については、「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 クライアントの制限：データ管理 (DMS) では、次の SQL スクリプトを実行しても NOTICE 出力は生成されません。 このスクリプトは、psql や pgAdmin などのクライアントを使用して実行する必要があります。

      • システムスキーマの制限：pg_toast スキーマ内の TOAST テーブルはシステムスキーマであり、その所有者は不変であるため、所有者を変更することはできません。 通常のユーザーは引き続きこれらのテーブルにアクセスできるため、通常の使用に影響はありません。

      • パーティションテーブルと外部テーブル：パーティションテーブルと外部テーブルの所有権の変更は、通常のテーブルと同様です。 以下のスクリプトは、これらのオブジェクトを自動的に処理します。

      DO $$
      DECLARE
      r record;
      i int;
      v_schema text[] := '{public,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
      $$;

      このコードブロックを実行すると、PostgreSQL は SQL ステートメントを NOTICE メッセージとして出力します。 生成されたステートメントが正確であることを確認してください。 正しい場合は、それらをコピーして実行し、所有権の変更を適用してください。

      一括生成の例 (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. 所有権の変更の確認

      • テーブル、ビュー、またはシーケンスの所有者が変更されたことを確認するには、次の SQL ステートメントを実行します。

        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_name_of_the_object' AND c.relname = 'name_of_the_table_view_or_sequence';

      • 関数の所有者が変更されたことを確認するには、次の SQL ステートメントを実行します。

        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_name_of_the_function' AND p.proname = 'name_of_the_function';

適用対象

ApsaraDB RDS for PostgreSQL