如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
当前回答
对于表,你必须循环:
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
其他回答
我为此创建了一个方便的脚本;pg_change_db_owner.sh。此脚本更改数据库模式中所有表、视图、序列和函数的所有权,以及模式本身的所有者。
请注意,如果你只是想改变一个特定数据库中所有对象的所有权, ,那么您可以简单地使用REASSIGN owned命令来代替。
可以使用REASSIGN OWNED命令。
剧情简介:
重新分配old_role[,…]到new_role
这将把old_role拥有的所有对象更改为新角色。你不需要考虑用户拥有什么样的对象,它们都会被改变。注意,它只应用于单个数据库中的对象。它也不会改变数据库本身的所有者。
它至少可以在8.2版本中使用。他们的网上资料只能追溯到这段时间。
参见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语句对所有三个都是相同的。
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
希望这能有所帮助。
我最近不得不改变数据库中所有对象的所有权。虽然表、视图、触发器和序列可以很容易地更改,但上述方法对于函数来说失败了,因为签名是函数名的一部分。当然,我有MySQL的背景,不太熟悉Postgres。
然而,pg_dump允许你只转储模式,其中包含ALTER xxx OWNER to yyy;你需要的陈述。下面是我在这个话题上的一点贝壳魔法
pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB