当前位置：theitroad>在 PostgreSQL 中同时修改所有表的 OWNER

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

声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow 原文地址: http://stackoverflow.com/questions/1348126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-10 22:20:55 　来源:igfitidea点击:

Modify OWNER on all tables simultaneously in PostgreSQL

postgresql

提问by Kai

How do I modify the owner of all tables in a PostgreSQL database?

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

I tried ALTER TABLE * OWNER TO new_ownerbut it doesn't support the asterisk syntax.

我试过了，ALTER TABLE * OWNER TO new_owner但它不支持星号语法。

回答by Trygve Laugst?l

You can use the REASSIGN OWNEDcommand.

您可以使用该REASSIGN OWNED命令。

Synopsis:

概要：

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

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

This changes all objects owned by old_roleto the new role. You don't have to think about what kind of objects that the user has, they will all be changed. Note that it only applies to objects inside a single database. It does not alter the owner of the database itself either.

这会将所有对象更改old_role为新角色。您不必考虑用户拥有什么样的对象，它们都会被更改。请注意，它仅适用于单个数据库内的对象。它也不会改变数据库本身的所有者。

It is available back to at least 8.2. Their online documentation only goes that far back.

它至少可以返回到 8.2。他们的在线文档只能追溯到那么远。

回答by Alex Soto

See REASSIGN OWNEDcommand

查看REASSIGN OWNED命令

Note:As @trygvis mentions in the answer below, the REASSIGN OWNEDcommand is available since at least version 8.2, and is a much easier method.

注意：正如@trygvis在下面的答案中提到的，该REASSIGN OWNED命令至少从 8.2 版开始可用，并且是一种更简单的方法。

Since you're changing the ownership for all tables, you likely want views and sequences too. Here's what I did:

由于您要更改所有表的所有权，因此您可能也需要视图和序列。这是我所做的：

Tables:

表格：

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

Sequences:

序列：

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

Views:

意见：

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

You could probably DRYthat up a bit since the alter statements are identical for all three.

你也许可以DRY，高达一点，因为ALTER语句对所有三个相同。

回答by rkj

This: http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.phpis also a nice and fast solution, and works for multiple schemas in one database:

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

Tables

表

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;

Sequences

序列

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;

Views

观看次数

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;

Materialized Views

物化视图

Based on this answer

基于这个答案

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

This generates all the required ALTER TABLE/ ALTER SEQUENCE/ ALTER VIEWstatements, copy these and paste them back into plsql to run them.

这将生成所有必需ALTER TABLE/ ALTER SEQUENCE/ALTER VIEW语句，这些复制并粘贴到PLSQL来运行它们。

Check your work in psql by doing:

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

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

回答by Johan Dahlin

If you want to do it in one sql statement, you need to define an exec() function as mentioned in http://wiki.postgresql.org/wiki/Dynamic_DDL

如果你想在一个 sql 语句中完成，你需要定义一个 exec() 函数，如http://wiki.postgresql.org/wiki/Dynamic_DDL 中提到的

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

Then you can execute this query, it will change the owner of tables, sequences and views:

然后你可以执行这个查询，它会改变表、序列和视图的所有者：

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 is the postgresql new name of the new owner.

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

In most circumstances you need to be superuser to execute this. You can avoid that by changing the owner from your own user to a role group you are a member of.

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

Thanks to RhodiumToad on #postgresqlfor helping out with this.

感谢#postgresql 上的RhodiumToad帮助解决这个问题。

回答by magiconair

I recently had to change the ownership of all objects in a database. Although tables, views, triggers and sequences were somewhat easily changed the above approach failed for functions as the signature is part of the function name. Granted, I have a MySQL background and am not that familiar with Postgres.

我最近不得不更改数据库中所有对象的所有权。尽管表、视图、触发器和序列在某种程度上很容易更改，但上述方法对于函数失败了，因为签名是函数名称的一部分。当然，我有 MySQL 背景，对 Postgres 不太熟悉。

However, pg_dumpallows you to dump just the schema and this contains the ALTER xxx OWNER TO yyy;statements you need. Here is my bit of shell magic on the topic

但是，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

回答by mwendamseke

very simple, try it...

很简单，试试吧...

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

回答by durenzo

is very simple

很简单

  1. su - postgres
  2. psql
  3. REASSIGN OWNED BY [old_user] TO [new_user];
  4. \c [your database]
  5. REASSIGN OWNED BY [old_user] TO [new_user];
  1. su - postgres
  2. 查询语句
  3. 将 [old_user] 拥有的重新分配给 [new_user]；
  4. \c [你的数据库]
  5. 将 [old_user] 拥有的重新分配给 [new_user]；

done.

完毕。

回答by elysch

I like this one since it modifies tables, views, sequencesand functionsowner of a certain schemain one go(in one sql statement), without creating a function and you can use it directly in PgAdmin IIIand psql:

我这样的之一，因为它修改表，视图，序列和功能有一定的所有者架构在一个去（在一个SQL语句），而无需创建一个功能，你可以直接使用它的pgAdmin III和PSQL：

(Tested in PostgreSql v9.2)

（在 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$$;

Based on answers provided by @rkj, @AlannaRose, @SharoonThomas, @user3560574 and this answerby @a_horse_with_no_name

基于由@rkj，@AlannaRose，@SharoonThomas，@ user3560574提供答案，这个答案由@a_horse_with_no_name

Thank's a lot.

非常感谢。

Better yet: Also change databaseand schemaowner.

更好的是：同时更改数据库和架构所有者。

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$$;

回答by Judge

I had to change the ownership of tables, views and sequences and found the great solution posted by @rjk is working fine - despite one detail: If the object names are of mixed case (e.g. "TableName"), this will fail with an "not found"-error.
To circumvent this, wrap the object names with ' " ' like this:

我不得不更改表、视图和序列的所有权，发现@rjk 发布的出色解决方案工作正常 - 尽管有一个细节：如果对象名称是大小写混合的（例如“TableName”），这将失败并显示“未找到”-错误。
要避免这种情况，请像这样用 ' " ' 包裹对象名称：

Tables

表

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;

Sequences

序列

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;

Views

观看次数

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;

回答by user3560574

You can try the following in PostgreSQL 9

您可以在 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$$;

相关推荐

在 PostgreSQL 中显示表

在 PostgreSQL 中显示表

PostgreSQL 自增

PostgreSQL 自增

在 PostgreSQL 中创建数据库的副本

在 PostgreSQL 中创建数据库的副本

有没有适用于 linux 的好的 PostgreSQL 客户端？

有没有适用于 linux 的好的 PostgreSQL 客户端？

相关推荐
最近更新
标签