如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
当前回答
如果当前所有者不是postgres,你可以使用这个:
REASSIGN OWNED BY old_role [, ...] TO new_role
但如果当前的所有者是postgres,你肯定会得到错误,所以你必须使用@dvanrensburg回答,但如果你想在相同的sql中执行命令,可以根据需要使用这些命令:
数据库
ALTER DATABASE target_database OWNER TO new_onwer;
表
DO
LANGUAGE plpgsql
$$
DECLARE
stmt text;
BEGIN
FOR stmt IN
WITH temp as (
SELECT 'ALTER TABLE '|| schemaname || '."' || tablename ||'" OWNER TO newuser' as command
FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, tablename )
SELECT command from temp
LOOP
EXECUTE stmt;
END LOOP;
END;
$$;
序列
DO
LANGUAGE plpgsql
$$
DECLARE
stmt text;
BEGIN
FOR stmt IN
WITH temp as (
SELECT 'ALTER SEQUENCE '|| sequence_schema || '."' || sequence_name ||'" OWNER TO newuser;' as command
FROM information_schema.sequences WHERE NOT sequence_schema IN ('pg_catalog', 'information_schema')
ORDER BY sequence_schema, sequence_name)
select command from temp
LOOP
EXECUTE stmt;
END LOOP;
END;
$$;
的观点
DO
LANGUAGE plpgsql
$$
DECLARE
stmt text;
BEGIN
FOR stmt IN
WITH temp as (
SELECT 'ALTER VIEW '|| table_schema || '."' || table_name ||'" OWNER TO newuser;' as command
FROM information_schema.views WHERE NOT table_schema IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name)
select command from temp
LOOP
EXECUTE stmt;
END LOOP;
END;
$$;
模式
DO
LANGUAGE plpgsql
$$
DECLARE
stmt text;
BEGIN
FOR stmt IN
WITH schema_names as(
SELECT distinct(schemaname) FROM pg_tables WHERE NOT schemaname IN ('pg_catalog', 'information_schema')
ORDER BY schemaname)
SELECT 'ALTER SCHEMA '|| schemaname ||' OWNER TO newuser;' as command
FROM schema_names
LOOP
EXECUTE stmt;
END LOOP;
END;
$$;
还要注意数据库中可能需要更改成员关系的函数和其他组件
函数和触发函数
DO
LANGUAGE plpgsql
$$
DECLARE
stmt text;
BEGIN
FOR stmt IN
WITH temp as(
SELECT 'alter function '||nsp.nspname||'.'||p.proname||'('||pg_get_function_identity_arguments(p.oid)||') owner to newuser;' as command
FROM pg_proc p
JOIN pg_namespace nsp ON p.pronamespace = nsp.oid
WHERE NOT nsp.nspname IN ('pg_catalog', 'information_schema'))
SELECT command FROM temp
LOOP
EXECUTE stmt;
END LOOP;
END;
$$;
其他回答
从PostgreSQL 9.0开始,你可以GRANT [priv名称]ON ALL[对象类型]in SCHEMA,其中[priv名称]是典型的SELECT, INSERT, UPDATE, DELETE等,[对象类型]可以是其中之一:
表 序列 功能
PostgreSQL的GRANT和REVOKE文档对此有更详细的说明。在某些情况下,仍然需要使用涉及系统编目(pg_catalog.pg_*)的技巧,但这种情况并不常见。我经常做以下事情:
开始一个事务来修改privs 将数据库的所有权更改为“DBA角色” 将schema的所有权更改为“DBA角色” 撤销所有角色的所有表、序列和函数上的所有私权 将相关/适当表上的SELECT、INSERT、UPDATE、DELETE权限授予适当的角色 提交DCL事务。
在PostgreSQL中没有这样的命令。但是您可以使用我之前为GRANTs描述的方法来解决它。
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
希望这能有所帮助。
可以使用REASSIGN OWNED命令。
剧情简介:
重新分配old_role[,…]到new_role
这将把old_role拥有的所有对象更改为新角色。你不需要考虑用户拥有什么样的对象,它们都会被改变。注意,它只应用于单个数据库中的对象。它也不会改变数据库本身的所有者。
它至少可以在8.2版本中使用。他们的网上资料只能追溯到这段时间。
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版本之间迁移的好方法。