在PostgreSQL中同时修改所有表上的OWNER [英] Modify OWNER on all tables simultaneously in PostgreSQL
本文介绍了在PostgreSQL中同时修改所有表上的OWNER的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何修改PostgreSQL数据库中所有表的所有者?
How do I modify the owner of all tables in a PostgreSQL database?
我尝试了ALTER TABLE * OWNER TO new_owner
,但是它不支持星号语法.
I tried ALTER TABLE * OWNER TO new_owner
but it doesn't support the asterisk syntax.
推荐答案
请参见在下面的答案中提及的内容, REASSIGN OWNED
命令至少在8.2版以上可用,并且是更简单的方法.
See REASSIGN OWNED
command
Note: As @trygvis mentions in the answer below, the REASSIGN OWNED
command is available since at least version 8.2, and is a much easier method.
由于您要更改所有表的所有权,因此您可能还需要视图和序列.这是我所做的:
Since you're changing the ownership for all tables, you likely want views and sequences too. Here's what I did:
表格:
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 有点夸张,因为这三个语句都完全相同.
You could probably DRY that up a bit since the alter statements are identical for all three.
这篇关于在PostgreSQL中同时修改所有表上的OWNER的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文