如何修改 PostgreSQL 数据库中所有表的所有者?
我试过 ALTER TABLE * OWNER TO new_owner
但它不支持星号语法。
您可以使用 REASSIGN OWNED
命令。
概要:
将 old_role [, ...] 重新分配给 new_role
这会将 old_role
拥有的所有对象更改为新角色。您不必考虑用户拥有什么样的对象,它们都会被更改。请注意,它仅适用于单个数据库中的对象。它也不会改变数据库本身的所有者。
它至少可以回到 8.2。他们的在线文档只能追溯到那么远。
请参见 REASSIGN OWNED 命令
注意: 作为@trygvis mentions in the answer below,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 语句是相同的。
REASSIGN OWNED BY old_role [, ...] TO new_role
这: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;
物化视图
基于 this answer
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 新名称。
在大多数情况下,您需要成为超级用户才能执行此操作。您可以通过将所有者从您自己的用户更改为您所属的角色组来避免这种情况。
感谢 #postgresql 上的 RhodiumToad 提供帮助。
很简单
su - postgres psql REASSIGN OWNED BY [old_user] TO [new_user]; \c [你的数据库] REASSIGN OWNED BY [old_user] TO [new_user];
完毕。
我喜欢这个,因为它一次性修改了某个模式的表、视图、序列和函数所有者(在一个 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、@AlannaRose、@SharoonThomas、@user3560574 和@a_horse_with_no_name 提供的答案this answer
非常感谢。
更好的是:同时更改数据库和模式所有者。
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$$;
information_schema.sequences
是空的,即使 SELECT c.* FROM pg_class c WHERE c.relkind = 'S';
列出了序列。为什么他们可能不匹配?
ALTER
查询不应该是 ALTER SEQUENCE
吗?
我最近不得不更改数据库中所有对象的所有权。尽管表、视图、触发器和序列在某种程度上很容易更改,但上述方法对于函数失败了,因为签名是函数名称的一部分。当然,我有 MySQL 背景,对 Postgres 不太熟悉。
但是,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
grep
命令。我自己是 Linux 新手,但据我了解,sed
似乎很好用,尤其是因为您指定了不区分大小写的匹配。
很简单,试试...
select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';
\gexec
对我有用 select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public' \gexec
我不得不更改表、视图和序列的所有权,并发现@rjk 发布的出色解决方案运行良好 - 尽管有一个细节:如果对象名称是混合大小写的(例如“TableName”),这将失败并显示“未找到”-错误。为了避免这种情况,用 ' " ' 包装对象名称,如下所示:
表
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;
您可以在 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$$;
如果当前所有者不是 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;
$$;
PostgreSQL 中没有这样的命令。但是您可以使用前段时间用于 GRANT 的方法 I described 解决它。
基于 answer by 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
$$;
@Alex Soto 的答案是正确的,@Yoav Aner 上传的要点也适用,前提是表/视图名称中没有特殊字符(在 postgres 中是合法的)。
您需要避开它们才能工作,我为此上传了一个要点:https://gist.github.com/2911117
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 版本之间迁移的好方法。
pg_dump
命令语法已过时,-d
现在是 --column-inserts
,但不建议仅用于 pgsql 操作。我只使用 --clean
和 --no-owner
。
我为此创建了一个方便的脚本; pg_change_db_owner.sh。此脚本更改数据库模式中所有表、视图、序列和函数的所有权,以及模式本身的所有者。
请注意,如果您只想更改特定数据库中由特定数据库角色拥有的所有对象的所有权,则只需使用命令 REASSIGN OWNED
即可。
从 PostgreSQL 9.0 开始,您可以使用 GRANT [priv name] ON ALL [object type] IN SCHEMA
,其中 [priv name]
是典型的 SELECT, INSERT, UPDATE, DELETE, etc
,而 [object type]
可以是以下之一:
表格
序列
功能
PostgreSQL 在 GRANT
和 REVOKE
上的文档对此进行了更详细的介绍。在某些情况下,仍然需要使用涉及系统目录 (pg_catalog.pg_*
) 的技巧,但它并不常见。我经常做以下事情:
开始一个事务以修改 privs 将 DATABASES 的所有权更改为“DBA 角色” 将 SCHEMAS 的所有权更改为“DBA 角色” REVOKE ALL privs on all TABLES, SEQUENCES and FUNCTIONS from all role GRANT SELECT, INSERT, UPDATE, DELETE on related /适当的表到适当的角色 提交 DCL 事务。
接受的解决方案不关心函数所有权,下面的解决方案会处理所有事情(回顾时我注意到它类似于上面的@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};"
以下更简单的 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 = 到新架构。
与@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
对于表,您必须循环:
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
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
重新分配拥有的对我不起作用,因为我想更改 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');您必须将消息从法语翻译成您自己的语言。适用于第 9.5 页
虽然以下内容并没有改变所有者,但改变了角色,这是我需要做的,在搜索谷歌时我最终找到了这个问题,所以为了完整起见,我将在这里输入:
对我来说,上述解决方案都不起作用,我一直得到:must be owner of relation xxx
。最终的解决方案是:
GRANT olduser TO newuser;