All Products
Search

This Product

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

    Document Center

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

    Last Updated:Mar 28, 2026

    PostgreSQL organizes objects in a hierarchy: Instance > Database > Schema > Table/View/Sequence/Function. To reassign ownership across an entire instance, work level by level — database first, then schemas, then the objects within each schema.

    To modify the owner of all objects in the instance, you must connect to the corresponding database and repeat Step 2 and Step 3 for all databases and schemas.

    Prerequisites

    Before you begin, ensure that you have:

    • A privileged account on the ApsaraDB RDS for PostgreSQL instance

    • psql or pgAdmin for the batch generation step (Step 3.3) — do not use DMS for that step, because DMS suppresses NOTICE output that the batch generation script relies on

    Step 1: Modify the owner of a database

    Change the database owner from the ApsaraDB RDS console — no SQL required.

    1. Go to the Instances page. In the top navigation bar, select the region where the instance resides, then click the instance ID.

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

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

    image

    Step 2: Modify the owner of a schema

    Connect to the target database using psql or pgAdmin:

    psql -U <username> -h <endpoint> -p <port>

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

    Find the business schemas in the current database (excludes system schemas):

    SELECT * FROM information_schema.schemata
WHERE catalog_name = '<database-name>'
  AND schema_name NOT IN (
    'information_schema', 'public', 'pg_catalog',
    'pg_temp_1', 'pg_toast', 'pg_toast_temp_1'
  );

    Change the schema owner:

    ALTER SCHEMA <schema-name> OWNER TO <new-owner>;

    Use a privileged account to avoid permission errors. To modify all business schemas in a database, repeat the ALTER SCHEMA statement for each schema returned by the query above.

    Verify the change:

    SELECT schema_name, schema_owner
FROM information_schema.schemata
WHERE schema_name = '<schema-name>';

    Step 3: Modify the owner of tables, views, sequences, and functions

    Connect to the target database using psql or pgAdmin (same connection command as Step 2).

    Reassign individual objects

    Table, view, or sequence:

    ALTER TABLE <schema-name>.<object-name> OWNER TO <new-owner>;
    ParameterDescription
    <schema-name>Name of the schema the object belongs to
    <object-name>Name of the table, view, or sequence
    <new-owner>Username of the new owner

    Function:

    ALTER FUNCTION <schema-name>.<function-name>(<arg-types>) OWNER TO <new-owner>;
    ParameterDescription
    <schema-name>Name of the schema the function belongs to
    <function-name>Name of the function
    <arg-types>Argument types (for example, arg1 integer). Required when the function name is not unique in the schema.
    <new-owner>Username of the new owner

    If you omit <arg-types> and the function name is not unique, PostgreSQL returns:

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

    Generate ALTER statements in bulk

    Important

    Run this script using psql or pgAdmin. DMS does not display NOTICE output, so the generated statements will not appear.

    To modify the owners of all objects in a schema in batches, use the following script to generate the required ALTER statements:

    DO $$
DECLARE
r record;
i int;
v_schema text[] := '{public,schema_name}'; -- Replace with the target schema names. For schemas with many objects, process one schema at a time.
v_new_owner varchar := 'owner_name';        -- Replace with the target owner username.
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 script outputs a NOTICE-prefixed ALTER statement for each object. Review the output, then copy and run the statements to apply the changes. Partitioned tables and tables with foreign keys are handled automatically.

    You cannot modify the owner of TOAST tables in the pg_toast system schema. This does not affect normal operations.

    Example — In the db1 database, reassign all objects in public and myschema1 to 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
$$;

    Output:

    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

    Verify the changes

    Table, view, or sequence:

    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>'
  AND c.relname = '<object-name>';

    Function:

    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>'
  AND p.proname = '<function-name>';

    Applicable to

    ApsaraDB RDS for PostgreSQL