http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php也是一个很好的快速解决方案，适用于一个数据库中的多个模式:

表

SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;

序列

SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;

的观点

SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;

物化视图

基于这个答案

SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;

这将生成所有必需的ALTER TABLE / ALTER SEQUENCE / ALTER VIEW语句，复制这些语句并将它们粘贴回plsql以运行它们。

检查你在psql中的工作，执行以下操作:

\dt *.*
\ds *.*
\dv *.*

很简单，试试看…

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

我最近不得不改变数据库中所有对象的所有权。虽然表、视图、触发器和序列可以很容易地更改，但上述方法对于函数来说失败了，因为签名是函数名的一部分。当然，我有MySQL的背景，不太熟悉Postgres。

然而，pg_dump允许你只转储模式，其中包含ALTER xxx OWNER to yyy;你需要的陈述。下面是我在这个话题上的一点贝壳魔法

pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB

Docker:修改所有表和序列的所有者

export user="your_new_owner"
export dbname="your_db_name"

cat <<EOF | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname" | grep ALTER | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname"
SELECT 'ALTER TABLE '||schemaname||'.'||tablename||' OWNER TO $user;' FROM pg_tables WHERE schemaname = 'public';
SELECT 'ALTER SEQUENCE '||relname||' OWNER TO $user;' FROM pg_class WHERE relkind = 'S';
EOF

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版本之间迁移的好方法。

我喜欢这个，因为它可以一次性(在一个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$$;