我喜欢这个，因为它可以一次性(在一个sql语句中)修改某个模式的表、视图、序列和函数所有者，而不需要创建函数，你可以直接在PgAdmin III和psql中使用它:

(在PostgreSql v9.2中测试)

DO $$DECLARE r record;
DECLARE
    v_schema varchar := 'public';
    v_new_owner varchar := '<NEW_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 = 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 = 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 = 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 = v_schema
    LOOP
        EXECUTE r.a;
    END LOOP;
END$$;

根据@rkj， @ alannrose， @SharoonThomas， @user3560574提供的答案和@ a_horis_with_no_name的回答

非常感谢。

更好的方法是:更改数据库和模式所有者。

DO $$DECLARE r record;
DECLARE
    v_schema varchar := 'public';
    v_new_owner varchar := 'admin_ctes';
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 = 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 = 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 = 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 = v_schema
        union all
        select 'ALTER SCHEMA "' || v_schema || '" OWNER TO ' || v_new_owner 
        union all
        select 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner 
    LOOP
        EXECUTE r.a;
    END LOOP;
END$$;

ansible非常简单。你也可以跳过obj_type来修改任何对象类型的所有权。

- name: Reassigner owner of all objects
  postgresql_owner:
    login_user: "{{ postgres_admin_username }}"
    login_unix_socket: "{{postgres_socket}}"
    db: "db-name"
    new_owner: "new-owner"
    reassign_owned_by: "old-owner"
    obj_type: "table"

以下是带有进一步信息的文档:https://docs.ansible.com/ansible/latest/collections/community/general/postgresql_owner_module.html

希望这能有所帮助。

很简单，试试看…

 select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';

pg_dump as insert statements 
pg_dump -d -O database filename
-d ( data as inserts ) -O ( capital O is no owner )

然后使用以下命令将备份文件输送回PostgreSQL:

psql -d database -U username -h hostname < filename

由于没有包含所有者，因此创建的所有表、模式等都是在指定的登录用户下创建的。

我了解到这也是一个在PostgreSQL版本之间迁移的好方法。

在PostgreSQL中没有这样的命令。但是您可以使用我之前为GRANTs描述的方法来解决它。

被接受的解决方案不关心函数所有权，下面的解决方案会关心所有的问题(在回顾时，我注意到它类似于上面的@magiconair)

echo "Database: ${DB_NAME}"
echo "Schema: ${SCHEMA}"
echo "User: ${NEW_OWNER}"

pg_dump -s -c -U postgres ${DB_NAME} | egrep "${SCHEMA}\..*OWNER TO"| sed -e "s/OWNER TO.*;$/OWNER TO ${NEW_OWNER};/" | psql -U postgres -d ${DB_NAME}
# do following as last step to allow recovery
psql -U postgres -d postgres -c "ALTER DATABASE ${DB_NAME} OWNER TO ${NEW_OWNER};"