如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
当前回答
如果希望在一条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上的帮助。
其他回答
从其他人的讨论来看,他们不同意我的问题。如前所述,如果所有者是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
@Alex Soto的答案是正确的,@Yoav Aner上传的要点也有效,前提是在表/视图名称中没有特殊字符(在postgres中是合法的)。
你需要转义它们来工作,我已经上传了一个要点:https://gist.github.com/2911117
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
希望这能有所帮助。
非常简单
Su - postgres psql 重新分配[old_user]到[new_user]; \c[你的资料库] 重新分配[old_user]到[new_user];
完成了。
我不得不改变表,视图和序列的所有权,并发现@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;