如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
当前回答
根据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
$$;
其他回答
下面这个更简单的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 =新模式。
从其他人的讨论来看,他们不同意我的问题。如前所述,如果所有者是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
与@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
从PostgreSQL 9.0开始,你可以GRANT [priv名称]ON ALL[对象类型]in SCHEMA,其中[priv名称]是典型的SELECT, INSERT, UPDATE, DELETE等,[对象类型]可以是其中之一:
表 序列 功能
PostgreSQL的GRANT和REVOKE文档对此有更详细的说明。在某些情况下,仍然需要使用涉及系统编目(pg_catalog.pg_*)的技巧,但这种情况并不常见。我经常做以下事情:
开始一个事务来修改privs 将数据库的所有权更改为“DBA角色” 将schema的所有权更改为“DBA角色” 撤销所有角色的所有表、序列和函数上的所有私权 将相关/适当表上的SELECT、INSERT、UPDATE、DELETE权限授予适当的角色 提交DCL事务。
根据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
$$;