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

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


当前回答

与@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事务。

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

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

非常简单

Su - postgres psql 重新分配[old_user]到[new_user]; \c[你的资料库] 重新分配[old_user]到[new_user];

完成了。

对于表,你必须循环:

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

参见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语句对所有三个都是相同的。