更改 PostgreSQL 数据库及其表的所有者的最佳方法？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23933327/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Best way to change the owner of a PostgreSQL database and their tables?
提问by forvas
I am trying to change the owner of a PostgreSQL database (version > 8.2) and its tables.
我正在尝试更改 PostgreSQL 数据库（版本 > 8.2）及其表的所有者。
I read this solution:
我读了这个解决方案：
Modify OWNER on all tables simultaneously in PostgreSQL
But is this the best way to do it (for recent versions of PostgreSQL)?. It seems that there is a function REASSIGN OWNED which is better, but this one changes every database owned by the old_role, doesn't it? I only want it for one database.
但这是最好的方法吗（对于最新版本的 PostgreSQL）？。似乎有一个函数 REASSIGN OWNED 更好，但是这个函数更改了old_role拥有的每个数据库，不是吗？我只想要一个数据库。
Like this post:
喜欢这个帖子：
REASSIGN OWNED BY for 1 specified database
I am not going to change the owner postgres, which is the best way nowadays?
我不打算更改所有者postgres，这是当今最好的方法吗？
Thank you in advance
先感谢您
回答by harmic
According to the manual:
根据手册：
Because REASSIGN OWNED does not affect objects within other databases, it is usually necessary to execute this command in each database
由于 REASSIGN OWNED 不会影响其他数据库内的对象，所以通常需要在每个数据库中执行此命令
which would seem to meet your requirements, although it also says the command would affect table spaces (which are not specific to the current database).
这似乎满足您的要求，尽管它也说该命令会影响表空间（并非特定于当前数据库）。
The second SO answer you linked applies to the special case of the postgres user, which owns the system catalogs. You cannot change the ownership of these.
您链接的第二个 SO 答案适用于拥有系统目录的 postgres 用户的特殊情况。您不能更改这些的所有权。
回答by d1ll1nger
The two methods that spring to mind for me are:
我想到的两种方法是：
1) First alter the database name, and then perhaps right a quick script which changes the owner on the current tables to the old tables.
1）首先更改数据库名称，然后可能是正确的快速脚本，将当前表的所有者更改为旧表。
ALTER DATABASE <dbname> OWNER TO <newowner>;
\o altertable.sql
SELECT 'ALTER TABLE ' || table_name || ' OWNER TO <newowner>; ' FROM information_schema WHERE table_schema = <oldowner> and table_catalog = '<dbname>';
\o
\i altertable.sql
The above will generate your commands, then just pop them into a file and execute it.
以上将生成您的命令，然后将它们弹出到文件中并执行它。
2) The other option would be to use pg_dump to dump your database in plain text mode and then alter the appropriate strings using search and replace:
2）另一种选择是使用 pg_dump 以纯文本模式转储数据库，然后使用搜索和替换更改适当的字符串：
pg_dump -Fp -f dbbackup.dmp <dbname>
vi dbbackup.dmp
:%s/oldowner/newowner/g
save and exit
Hope this helps.
希望这可以帮助。
