对于表，你必须循环:

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

虽然下面没有改变所有者，但改变了角色，这是我需要做的，当搜索谷歌时，我结束了这个问题，所以为了完整性起见，我将在这里输入:

对我来说，上述解决方案都不起作用，我一直得到:必须是关系xxx的所有者。最终的解决方案是:

格兰特·olduser

如果当前所有者不是postgres，你可以使用这个:

REASSIGN OWNED BY old_role [, ...] TO new_role

但如果当前的所有者是postgres，你肯定会得到错误，所以你必须使用@dvanrensburg回答，但如果你想在相同的sql中执行命令，可以根据需要使用这些命令:

数据库

ALTER DATABASE target_database OWNER TO new_onwer;

表

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO newuser' as command
    FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
    ORDER BY schemaname, tablename )
    SELECT command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

序列

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO newuser;' as command
    FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
    ORDER BY sequence_schema, sequence_name)
    select command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

的观点

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH temp as (
    SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO newuser;' as command
    FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
    ORDER BY table_schema, table_name)
    select command from temp
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

模式

DO
LANGUAGE plpgsql
$$
DECLARE
  stmt text;
BEGIN
  FOR stmt IN
    WITH schema_names as(
    SELECT distinct(schemaname) FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
    ORDER BY schemaname)
    SELECT 'ALTER SCHEMA '|| schemaname ||' OWNER TO newuser;' as command
    FROM schema_names
  LOOP
    EXECUTE stmt;
  END LOOP;
END;
$$;

还要注意数据库中可能需要更改成员关系的函数和其他组件

函数和触发函数

    DO
    LANGUAGE plpgsql
    $$
    DECLARE
      stmt text;
    BEGIN
      FOR stmt IN
        WITH temp as(
        SELECT 'alter function '||nsp.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to newuser;' as command
        FROM pg_proc p
        JOIN pg_namespace nsp ON p.pronamespace = nsp.oid
        WHERE NOT  nsp.nspname IN ('pg_catalog', 'information_schema'))
        SELECT command FROM temp
      LOOP
        EXECUTE stmt;
      END LOOP;
    END;
    $$;

被接受的解决方案不关心函数所有权，下面的解决方案会关心所有的问题(在回顾时，我注意到它类似于上面的@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};"

我为此创建了一个方便的脚本;pg_change_db_owner.sh。此脚本更改数据库模式中所有表、视图、序列和函数的所有权，以及模式本身的所有者。

请注意，如果你只是想改变一个特定数据库中所有对象的所有权， ，那么您可以简单地使用REASSIGN owned命令来代替。

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