PostgreSQL：在PostgreSQL中同时修改所有表的OWNER

Question

我要如何修改PostgreSQL数据库中所有表的所有者？

我尝试使用ALTER TABLE * OWNER TO new_owner，但它不支持星号语法。

- Kai

Answer 1

您可以使用REASSIGN OWNED命令。

概要：

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

这将把 old_role 拥有的所有对象更改为新角色。您无需考虑用户拥有哪种类型的对象，它们都将被更改。请注意，它仅适用于单个数据库内的对象。它不会更改数据库本身的所有者。至少从8.2版本开始可用。在线文档只提供到那个版本。

- Trygve Laugstøl

---

即使我连接到一个我创建的数据库（即非系统数据库），它仍然无法适用于用户postgres，它显示： 错误：无法重新分配属于角色postgres的对象的所有权，因为它们是数据库系统所需的。 - thnee

听起来你正在尝试重新分配PostgreSQL内部的对象所有权，比如pg_*表/视图。 - Trygve Laugstøl

根据@thnee的报告，REASSIGN影响数据库中的所有对象，不区分用户定义和系统对象，因此如果有任何扩展具有自己的表，则它对Postgres无效。尽管它对我没有太大帮助（我的数据库之前是由postgres拥有的），但我仍然认为这个选项更加优雅，所以我支持（+1）它。 - Pavel V.

请明确，此命令仅适用于您当前连接的数据库。如果旧角色拥有多个数据库中的对象，则应在每个数据库中连接并运行此命令。 - mavroprovato

这似乎在通过AWS RDS托管的Postgres上无法运行。我收到了这个错误消息“permission denied to reassign objects”，并且这个链接提供了原因：“似乎唯一可以“重新分配所有者”的方法是作为超级用户（这与文档相矛盾），而在RDS中无法访问超级用户。”http://www.postgresql-archive.org/REASSIGN-OWNED-simply-doesn-t-work-td5987901.html - typoerrpr

Answer 2

查看重新分配所属命令

注意：正如@trygvis在下面的回答中提到的那样，重新分配所属命令自版本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一些，因为所有三个语句的修改语句都是相同的。

---

- Alex Soto

---

谢谢Alex。我根据你的答案创建了一个小的Bash脚本，可以在https://gist.github.com/2482969找到。 - gingerlime

请参考@trygvis最近的回答。迄今为止最简单的答案是：REASSIGN OWNED BY old_role [, ...] TO new_role。 - David

REASSIGN OWNED BY 命令不能用于被 postgres 用户所拥有的对象。 - Bruno Medeiros

另外，REASSIGN OWNED实际上会影响旧角色拥有的所有数据库的所有权（请参见：http://www.postgresql.org/docs/9.3/static/sql-reassign-owned.html）。因此，如果您只想更改单个数据库的所有权，请注意！ - kitsune

根据@gingerlime的脚本，bspkrs（找不到他的名字）创建了一个也改变功能的脚本：https://gist.github.com/bspkrs/b997ed7f1eb1268f3403。 - elysch

Answer 3

这是一个不错且快速的解决方案，可以处理同一个数据库中的多个模式： 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 语句，复制并粘贴回PL/SQL以运行它们。

通过执行以下操作在psql中检查您的工作：

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

- rkj

---

很棒的解决方案。我的唯一问题是我必须导出脚本，然后执行导出的脚本。我是SQL Server大师，但我不确定执行的快捷方式是什么。我点击了执行查询和执行pgScript。我做错了什么？ - Tyrone Moodley

我更喜欢这种方式，因为它可以在登录后从plsql中运行 - unix级别的脚本（目前最受欢迎的答案）需要在我的环境中输入“-U postgres”和密码。 - Dazed

我更喜欢这个答案，因为(1)它可以在psql或pgAdmin中完成 (2)它可以轻松地让您查看要修改的对象。我还使用了https://dev59.com/GGEh5IYBdhLWcg3wHAND，它是类似的，但用于函数。 - AlannaRose

你能为函数添加SELECT吗？ - MysteriousNothing

请为大型对象添加另一个脚本。（将大型对象的所有者更改为{新所有者}） - undefined

Answer 4

如果您想在一个SQL语句中完成此操作，您需要像http://wiki.postgresql.org/wiki/Dynamic_DDL中所述定义一个exec()函数。

CREATE FUNCTION exec(text) returns text language plpgsql volatile
  AS $f$
    BEGIN
      EXECUTE $1;
      RETURN $1;
    END;
$f$;

然后您可以执行此查询，它将更改表、序列和视图的所有者：

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' ||
            quote_ident(s.relname) || ' OWNER TO $NEWUSER')
  FROM (SELECT nspname, relname
          FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
         WHERE nspname NOT LIKE E'pg\\_%' AND 
               nspname <> 'information_schema' AND 
               relkind IN ('r','S','v') ORDER BY relkind = 'S') s;

$NEWUSER是新的postgresql所有者的名称。

在大多数情况下，您需要成为超级用户才能执行此操作。您可以通过将所有者从自己的用户更改为您是其成员的角色组来避免这种情况。

感谢RhodiumToad在#postgresql上的帮助。

- Johan Dahlin

---

非常有用，因为它会改变整个模式的所有权，包括函数、索引、序列等等。谢谢！ - liviucmg

它不会更改模式所有者。如何更改模式所有者呢？ - Andrus

@Andrus ALTER DATABASE $DB OWNER TO $OWNER;@Andrus修改数据库$DB的所有者为$OWNER; - Johan Dahlin

修改数据库会更改整个数据库所有者。我问如何更改模式所有者。 - Andrus

将模式 fred 的所有者更改为 betty； - Eric Aldinger

Answer 5

非常简单

1. su - postgres
2. psql
3. REASSIGN OWNED BY [旧用户] TO [新用户];
4. \c [你的数据库]
5. REASSIGN OWNED BY [旧用户] TO [新用户];

完成。

- durenzo

---

这可能做到了询问者想要的。迄今为止最简单的方法。 - Geof Sawaya

你来晚了4年，向上滑动查看：https://dev59.com/C3M_5IYBdhLWcg3wgzdl#13535184 - Ben Johnson

@BenJohnson在这个回答中虽然没有解释如何使用它 :/ - Jamie Hutber

Answer 6

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

- Ebrahim Abdollahian

---

谢谢！这是真正的可直接使用的代码。 - Danil Eroshenko

Answer 7

我喜欢这个，因为它可以在一个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、@AlannaRose、@SharoonThomas、@user3560574 的回答，以及 @a_horse_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$$;

- elysch

---

太棒了！为什么Postgres没有添加这个我不知道！ - pip

两个问题：1）第一行和第三行的“ALTER TABLE”看起来是重复的。这是有意为之吗（例如，您是否必须在表上进行两次操作以更改所有权？）。2）我们发现information_schema.sequences为空，即使SELECT c.* FROM pg_class c WHERE c.relkind ='S';列出序列。为什么他们可能不匹配？ - GuyPaddock

另外，第二个ALTER查询应该是ALTER SEQUENCE吗？ - GuyPaddock

为什么要通过alter_table命令更新序列所有者？ - WestCoastProjects

谢谢，我也使用GRANT ALL ON SCHEMA public TO <NEW_OWNER>; GRANT ALL ON ALL TABLES IN SCHEMA public TO <NEW_OWNER>; GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO <NEW_OWNER>; GRANT ALL ON ALL FUNCTIONS IN SCHEMA public TO <NEW_OWNER>;来修复现有实体的权限。以及ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO <NEW_OWNER>; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO <NEW_OWNER>; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO <NEW_OWNER>;来修复新创建实体的权限。 - undefined

Answer 8

非常简单，试一下...

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

- mwendamseke

---

你可以加上一条注释说明相应的字符串需要复制并执行，虽然这似乎是显而易见的 :p。 - Nightscape

这包括删除更改语句周围的所有引号。在这种情况下，多光标或替换功能会有所帮助。 - knownasilya

在命令的末尾添加“\gexec”对我有用。select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public' \gexec - tlogbon

Answer 9

最近我需要改变数据库中所有对象的所有权。虽然表、视图、触发器和序列都比较容易更改，但这种方法对于函数来说是失败的，因为函数名包含了函数签名。虽然我有MySQL的背景，但对Postgres并不是很熟悉。

然而，pg_dump允许您仅转储模式，其中包含您需要的ALTER xxx OWNER TO yyy;语句。以下是我在此主题上的一点shell技巧：

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

- magiconair

---

我不确定为什么您要使用grep命令。我自己也是Linux的新手，但据我的理解，似乎使用sed就足够了，尤其是因为您正在指定一个不区分大小写的匹配。 - Bobort

在这里使用grep来过滤pg_dump的输出，只保留包含'owner to'的行，并用sed进行修改。只有带有'owner to'的行才会被执行，而不是整个转储文件。 - ThoSil

Answer 10

我必须更改表格、视图和序列的所有权，并发现@rjk发布的绝妙解决方案完全有效 - 尽管存在一个细节： 如果对象名称使用混合大小写（例如“TableName”），则会出现“未找到”错误。
为了避免这种情况，可以像这样用' " '包装对象名称：

表格

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;

- Judge