我不得不改变表，视图和序列的所有权，并发现@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;

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

如果当前所有者不是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;
    $$;

参见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语句对所有三个都是相同的。

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

希望这能有所帮助。

从PostgreSQL 9.0开始，你可以GRANT [priv名称]ON ALL[对象类型]in SCHEMA，其中[priv名称]是典型的SELECT, INSERT, UPDATE, DELETE等，[对象类型]可以是其中之一:

表 序列 功能

PostgreSQL的GRANT和REVOKE文档对此有更详细的说明。在某些情况下，仍然需要使用涉及系统编目(pg_catalog.pg_*)的技巧，但这种情况并不常见。我经常做以下事情:

开始一个事务来修改privs 将数据库的所有权更改为“DBA角色” 将schema的所有权更改为“DBA角色” 撤销所有角色的所有表、序列和函数上的所有私权 将相关/适当表上的SELECT、INSERT、UPDATE、DELETE权限授予适当的角色 提交DCL事务。