ApsaraDB RDS:Modify the owner of an object in an ApsaraDB RDS for PostgreSQL instance

Background information

The object hierarchy in PostgreSQL is Instance > Database > Schema > Table/View/Sequence/Function. Therefore, when you modify the object owner, you must perform the operation at each level, starting from the database, then the schema, and finally the specific tables, views, sequences, and functions.

Precautions

When you perform Step 3: Batch generate SQL statements to modify owners, you must use a client such as the pgAdmin client or the PostgreSQL command line interface to connect to the ApsaraDB RDS for PostgreSQL instance and execute the relevant SQL statements. Do not perform this operation in DMS because the execution result will not include the NOTICE output, which will affect subsequent operations.

1. Modify the owner of a database

In the ApsaraDB RDS console, you can directly modify the owner of a database on the user interface (UI).

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

2. In the navigation pane on the left, click Databases.

3. In the Actions column for the target database, modify the owner.

   

2. Modify the owner of a schema

1. Connect to an ApsaraDB RDS for PostgreSQL instance using the pgAdmin client or the PostgreSQL command line interface.

   psql -U <Username of the ApsaraDB RDS for PostgreSQL instance> -h <Internal or public endpoint of the instance> -p <Port number corresponding to the endpoint>

   For more information, see View the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.

2. Execute the following SQL statement to query the business schemas in the current database:

   SELECT * FROM information_schema.schemata where catalog_name = 'Enter the name of the business database' and schema_name not in ('information_schema','public','pg_catalog','pg_temp_1', 'pg_toast','pg_toast_temp_1');

3. Execute the following SQL statement to change the owner of the specified schema to the target user:

   ALTER schema <Enter the name of the business schema> OWNER TO <Enter the name of the target owner>;

   Note

   • We recommend that you use a privileged account to execute the preceding command to prevent permission errors.

   • If you want to modify the owner of only a specific business schema, you only need to perform Step 2.1 and Step 2.3.

   • To modify the owners of all business schemas in a database, you must repeat Step 2.3 for each business schema found in Step 2.2.

4. Execute the following SQL statement to verify that the owner of the schema has been modified:

   SELECT schema_name, schema_owner FROM information_schema.schemata where schema_name = 'Enter the name of the business schema';

3. Modify the owner of a table, view, or function in a schema

1. Connect to an ApsaraDB RDS for PostgreSQL instance using the pgAdmin client or the PostgreSQL command line interface.

   psql -U <Username of the ApsaraDB RDS for PostgreSQL instance> -h <Internal or public endpoint of the instance> -p <Port number corresponding to the endpoint>

   For more information, see View the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.

2. Modify the owner of a table, view, or sequence

   Execute the following SQL statement to modify the owner of a specified object (table, view, or sequence):

   ALTER table schema_name.object OWNER TO new_owner;

   The following table describes the parameters.

   • schema_name: The name of the schema to which the object belongs.

   • object: The name of the table, view, or sequence.

   • new_owner: The username of the target owner.

3. Modify the owner of a function

   Execute the following SQL statement to modify the owner of a specified object (function):

   ALTER function schema_name.function OWNER TO new_owner;

   The following table describes the parameters.

   • schema_name: The name of the schema to which the function belongs.

   • function: The name of the function.

   • new_owner: The username of the target owner.

   Note

   If the following error is reported, this indicates that the function name is not unique. This means that multiple functions with the same name exist in the current PostgreSQL database. In this case, you must add arguments after the function name to uniquely identify the target function.

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

4. Batch generate SQL statements to modify owners and verify the modification

   1. To modify the owners of all objects in a schema in batches, you can use the following command to generate SQL statements:

      Important

      • SQL execution client limit: The following SQL statement does not generate NOTICE output when executed in DMS. Therefore, you must use a client such as psql or pgAdmin to execute the statement.

      • System schema limit: You cannot modify the owner of a TOAST table in the pg_toast schema because it is a system schema and its owner cannot be changed. However, this does not affect normal operations, and regular users can still access these tables.

      • Partitioned tables and tables with foreign keys: Modifying the owner of partitioned tables and tables with foreign keys is the same as for standard tables. The following steps automatically handle the owner changes for these objects.

      DO $$
      DECLARE
      r record;
      i int;
      v_schema text[] := '{public,schema_name}'; -- Enter the array of schema names to be modified. You can enter multiple schema_name values. However, if a schema contains many tables, we recommend that you execute the statement for each schema to avoid affecting your business.
      v_new_owner varchar := 'owner_name';   -- The username of the target 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
      $$;

      After you execute the preceding code block, SQL statements that contain NOTICE are returned. Confirm that the SQL statements meet your expectations. If they do, copy and execute the generated SQL statements to complete the batch modification.

      Example of batch generation (including the SQL command and the returned result)

      In the db1 database, change the owner of all database objects, such as tables, views, sequences, and functions, in the specified schemas (public and myschema1) to the target user (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
      $$;

      The following result is returned:

      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. Verify that the owners of the objects have been modified

      • Verify that the owner of the table, view, or sequence has been modified

        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 = 'The name of the schema to which the object belongs' AND c.relname = 'The name of the table, view, or sequence';

      • Verify that the owner of the function has been modified

        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 = 'The name of the schema to which the function belongs' AND p.proname = 'The name of the function';

Applicable to

ApsaraDB RDS for PostgreSQL