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

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

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

376823 次浏览
小开

在PostgreSQL中没有这样的命令。但是你可以使用方法I 描述解决这个问题。

小开
最佳答案

参见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

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

小开

我最近不得不改变数据库中所有对象的所有权。虽然表、视图、触发器和序列可以很容易地更改，但上述方法对于函数来说失败了，因为签名是函数名的一部分。当然，我有MySQL的背景，不太熟悉Postgres。

然而，pg_dump允许你只转储模式，其中包含你需要的修改xxx OWNER为yyy语句。下面是我在这个话题上的一点贝壳魔法

pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB
小开 
pg_dump as insert statements
pg_dump -d -O database filename
-d ( data as inserts ) -O ( capital O is no owner )

然后使用以下命令将备份文件输送回PostgreSQL:

psql -d database -U username -h hostname < filename

由于没有包含所有者，因此创建的所有表、模式等都是在指定的登录用户下创建的。

我了解到这也是一个在PostgreSQL版本之间迁移的好方法。

小开

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

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

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的帮助。

小开

从PostgreSQL 9.0开始，你可以使用GRANT [priv name] ON ALL [object type] IN SCHEMA，其中[priv name]是典型的SELECT, INSERT, UPDATE, DELETE, etc，而[object type]可以是:

  • TABLES
  • SEQUENCES
  • FUNCTIONS

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

  1. BEGIN一个事务修改privs
  2. 将DATABASES的所有权更改为“DBA角色”
  3. 将SCHEMAS的所有权更改为“DBA角色”
  4. 所有角色的所有TABLES、SEQUENCES和FUNCTIONS上的REVOKE ALL privs
  5. GRANT SELECT, INSERT, UPDATE, DELETE在相关的/适当的表到适当的角色
  6. COMMIT DCL事务。
小开

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

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

小开

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

你可以使用REASSIGN OWNED命令。

剧情简介:

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

这将把old_role拥有的所有对象更改为新角色。你不需要考虑用户拥有什么样的对象，它们都会被改变。注意，它只应用于单个数据库中的对象。它也不会改变数据库本身的所有者。

它至少可以在8.2版本中使用。他们的网上资料只能追溯到这段时间。

小开

很简单，试试看…

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

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

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

请注意，如果你只是想改变一个特定数据库中所有对象的所有权，

.

.

.

.
小开

下面这个更简单的shell脚本适合我。

#!/bin/bash
for i in  `psql -U $1  -qt -c  "select tablename from pg_tables where schemaname='$2'"`
do
psql -U $1 -c  "alter table $2.$i set schema $3"
done
输入$1 -用户名(数据库) $2 =现有模式 $3 = to new schema.

.

.
小开

非常简单

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

完成了。

小开

Docker:修改所有表和序列的所有者

export user="your_new_owner"
export dbname="your_db_name"


cat <<EOF | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname" | grep ALTER | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname"
SELECT 'ALTER TABLE '||schemaname||'.'||tablename||' OWNER TO $user;' FROM pg_tables WHERE schemaname = 'public';
SELECT 'ALTER SEQUENCE '||relname||' OWNER TO $user;' FROM pg_class WHERE relkind = 'S';
EOF
小开

我喜欢这个，因为它修改了表， 的观点， 序列和功能的某个模式的所有者在一个去(在一个sql语句中)，而不创建一个函数，你可以直接在PgAdmin三世和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， @ alannrose， @SharoonThomas， @user3560574和@ a_horis_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回答，下面是多个模式的解决方案:

DO $$
DECLARE
r record;
i int;
v_schema text[] := '{public,schema1,schema2,schema3}';
v_new_owner varchar := 'my_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 = ANY (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 = ANY (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 = ANY (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 = ANY (v_schema)
union all
select 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner
LOOP
EXECUTE r.a;
END LOOP;
FOR i IN array_lower(v_schema,1) .. array_upper(v_schema,1)
LOOP
EXECUTE 'ALTER SCHEMA "' || v_schema[i] || '" OWNER TO ' || v_new_owner ;
END LOOP;
END
$$;
小开

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

对于表，你必须循环:

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
小开

重新分配所有没有为我工作，因为我想改变由postgres拥有的表。

我最终使用Alex的方法，但我想从psql中做到这一点。下面这些对我来说就足够了。

DO $$
DECLARE
rec record;
BEGIN
FOR rec in
SELECT *
FROM pg_tables
where schemaname = 'public'
LOOP
EXECUTE 'alter table ' || quote_ident(rec.tablename) || ' owner to new_owner';
END LOOP;
END
$$;
小开

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

希望这能有所帮助。

小开

从其他人的讨论来看，他们不同意我的问题。如前所述，如果所有者是postgres, REASSIGN OWNED不工作。所以，受之前讨论的启发，我写了这个脚本:

CREATE OR REPLACE FUNCTION public.alt_own_onall (v_new_owner text)
RETURNS void
LANGUAGE plpgsql
AS $alt_own_onall$
-- ALTer OWNer ON ALL objects
DECLARE
r           RECORD;
v_sqlcmd    TEXT; -- commande SQL
b_modif     BOOLEAN DEFAULT false;    -- si au moins une modif
BEGIN
v_new_owner := quote_ident (v_new_owner);
IF v_new_owner NOT IN
(SELECT role_name FROM information_schema.enabled_roles WHERE role_name <>'postgres')
THEN
RAISE WARNING '[%] est inconnu', v_new_owner;
RETURN;
END IF
; -- tables
RAISE INFO 'Le nouveau propriétaire des tables, vues, fonctions, schémas et de la base va être [%]', v_new_owner
;
FOR r IN
SELECT quote_ident(schemaname) || '.' || quote_ident(tablename) sch_tbl
FROM pg_catalog.pg_tables
WHERE schemaname !~'^(pg_|information)' AND tableowner <> v_new_owner
LOOP
v_sqlcmd := 'ALTER TABLE ' || r.sch_tbl || ' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
; -- views
FOR r IN
SELECT quote_ident(schemaname) || '.' || quote_ident(viewname) v_sch_nam
FROM pg_catalog.pg_views
WHERE schemaname !~'^(pg_|information)' AND viewowner <> v_new_owner
LOOP
v_sqlcmd := 'ALTER VIEW '|| r.v_sch_nam ||' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
; -- séquences
/*
ERROR:  cannot change owner of sequence "*_seq"
DETAIL:  Sequence "*_seq" is linked to table "*".


FOR r IN
SELECT quote_ident(sequence_schema) || '.' || quote_ident(sequence_name) s_sch_nam
FROM information_schema.sequences
WHERE sequence_schema !~'^(pg_|information)'
LOOP
EXECUTE 'ALTER SEQUENCE ' || r.s_sch_nam || ' OWNER TO ' || v_new_owner || ';'
;
END LOOP
; -- fonctions */
FOR r IN
SELECT
quote_ident (n.nspname) || '.' || quote_ident (p.proname) || '(' ||
pg_get_function_identity_arguments (p.oid) || ')' AS nsp_pro_arg
FROM pg_proc AS p
JOIN pg_namespace   AS n ON p.pronamespace    = n.oid
JOIN pg_authid      AS a ON p.proowner        = a.oid
WHERE n.nspname !~'^(pg_|information)' AND quote_ident(a.rolname) <> v_new_owner
LOOP
v_sqlcmd := 'ALTER FUNCTION ' || r.nsp_pro_arg || ' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
; -- schémas
FOR r IN
SELECT quote_ident(schema_name) s_nam
FROM information_schema.schemata
WHERE schema_name !~'^(pg_|information)' AND quote_ident(schema_owner) <> v_new_owner
LOOP
v_sqlcmd := 'ALTER SCHEMA ' || r.s_nam || ' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
; -- base
FOR r IN
SELECT d.datname, a.rolname
FROM pg_database d JOIN pg_authid a ON d.datdba=a.oid
WHERE d.datname=current_database() AND a.rolname <> v_new_owner
LOOP
v_sqlcmd := 'ALTER DATABASE ' || quote_ident(current_database()) || ' OWNER TO ' || v_new_owner || ';';
RAISE INFO 'Exécution de [%]', v_sqlcmd;
EXECUTE v_sqlcmd;
b_modif := true;
END LOOP
;
IF NOT b_modif THEN RAISE INFO 'Aucune modification n''a été effectuée.'; END IF;
END
;$alt_own_onall$;

在PSQL下使用它: SELECT alt_own_onall ('new_role_name'); 你必须把法语信息翻译成你自己的语言。 适用于pg 9.5

小开

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

对我来说，上面的解决方案都不起作用，我一直得到:must be owner of relation xxx。最终的解决方案是:

GRANT olduser TO newuser;

小开

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

提交答案