如何修改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_dump as insert statements
pg_dump -d -O database filename
-d ( data as inserts ) -O ( capital O is no owner )
然后使用以下命令将备份文件输送回PostgreSQL:
psql -d database -U username -h hostname < filename
由于没有包含所有者,因此创建的所有表、模式等都是在指定的登录用户下创建的。
我了解到这也是一个在PostgreSQL版本之间迁移的好方法。
我最近不得不改变数据库中所有对象的所有权。虽然表、视图、触发器和序列可以很容易地更改,但上述方法对于函数来说失败了,因为签名是函数名的一部分。当然,我有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
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
希望这能有所帮助。
重新分配所有没有为我工作,因为我想改变由postgres拥有的表。
我最终使用Alex的方法,但我想从psql中做到这一点。下面这些对我来说就足够了。
DO $$
DECLARE
rec record;
BEGIN
FOR rec in
SELECT *
FROM pg_tables
where schemaname = 'public'
LOOP
EXECUTE 'alter table ' || quote_ident(rec.tablename) || ' owner to new_owner';
END LOOP;
END
$$;
我不得不改变表,视图和序列的所有权,并发现@rjk发布的伟大解决方案工作得很好-尽管有一个细节: 如果对象名称是大小写混合的(例如。"TableName"),这将失败,并报错"not found"。 为了避免这种情况,可以像这样用' " '来包装对象名称:
表
SELECT 'ALTER TABLE \"'|| schemaname || '.' || tablename ||'\" OWNER TO my_new_owner;'
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename;
序列
SELECT 'ALTER SEQUENCE \"'|| sequence_schema || '.' || sequence_name ||'\" OWNER TO my_new_owner;'
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name;
的观点
SELECT 'ALTER VIEW \"'|| table_schema || '.' || table_name ||'\" OWNER TO my_new_owner;'
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name;