如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
当前回答
@Alex Soto的答案是正确的,@Yoav Aner上传的要点也有效,前提是在表/视图名称中没有特殊字符(在postgres中是合法的)。
你需要转义它们来工作,我已经上传了一个要点:https://gist.github.com/2911117
其他回答
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语句中)修改某个模式的表、视图、序列和函数所有者,而不需要创建函数,你可以直接在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, @ 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$$;
参见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语句对所有三个都是相同的。
从其他人的讨论来看,他们不同意我的问题。如前所述,如果所有者是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
我不得不改变表,视图和序列的所有权,并发现@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;