PostgreSQL:在 PostgreSQL 中同时修改所有表的 OWNER [英] PostgreSQL: Modify OWNER on all tables simultaneously in PostgreSQL

问题描述

如何修改 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 命令

注意:正如@trygvis 在下面的答案中提到，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，因为所有三个的alter 语句都相同.

You could probably DRY that up a bit since the alter statements are identical for all three.

这篇关于PostgreSQL:在 PostgreSQL 中同时修改所有表的 OWNER的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！