参见REASSIGN OWNED命令

注意:正如@trygvis在下面的回答中提到的，REASSIGN OWNED命令至少在8.2版本中就可用了，而且是一个更简单的方法。

因为要更改所有表的所有权，所以可能还需要视图和序列。以下是我所做的:

表:

for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = 'public';" YOUR_DB` ; do  psql -c "alter table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

序列:

for tbl in `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = 'public';" YOUR_DB` ; do  psql -c "alter sequence \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

视图:

for tbl in `psql -qAt -c "select table_name from information_schema.views where table_schema = 'public';" YOUR_DB` ; do  psql -c "alter view \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

您可能会DRY它一点，因为alter语句对所有三个都是相同的。

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 *.*

@Alex Soto的答案是正确的，@Yoav Aner上传的要点也有效，前提是在表/视图名称中没有特殊字符(在postgres中是合法的)。

你需要转义它们来工作，我已经上传了一个要点:https://gist.github.com/2911117

我不得不改变表，视图和序列的所有权，并发现@rjk发布的伟大解决方案工作得很好-尽管有一个细节: 如果对象名称是大小写混合的(例如。"TableName")，这将失败，并报错"not found"。 为了避免这种情况，可以像这样用' " '来包装对象名称:

表

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;

与@AlexSoto的函数方法相同:

IFS=$'\n'  
for fnc in `psql -qAt -c "SELECT  '\"' || p.proname||'\"' || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'public';" YOUR_DB` ; do  psql -c "alter function $fnc owner to NEW_OWNER" YOUR_DB; done

您可以在PostgreSQL 9中尝试以下操作

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'alter table '|| r.tablename ||' owner to newowner;';
    END LOOP;
END$$;