如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
如何修改PostgreSQL数据库中所有表的所有者?
我尝试了ALTER TABLE * OWNER TO new_owner,但它不支持星号语法。
当前回答
非常简单
Su - postgres psql 重新分配[old_user]到[new_user]; \c[你的资料库] 重新分配[old_user]到[new_user];
完成了。
其他回答
Docker:修改所有表和序列的所有者
export user="your_new_owner"
export dbname="your_db_name"
cat <<EOF | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname" | grep ALTER | docker run -i --rm --link postgres:postgres postgres sh -c "psql -h \$POSTGRES_PORT_5432_TCP_ADDR -p \$POSTGRES_PORT_5432_TCP_PORT -U postgres -d $dbname"
SELECT 'ALTER TABLE '||schemaname||'.'||tablename||' OWNER TO $user;' FROM pg_tables WHERE schemaname = 'public';
SELECT 'ALTER SEQUENCE '||relname||' OWNER TO $user;' FROM pg_class WHERE relkind = 'S';
EOF
非常简单
Su - postgres psql 重新分配[old_user]到[new_user]; \c[你的资料库] 重新分配[old_user]到[new_user];
完成了。
我喜欢这个,因为它可以一次性(在一个sql语句中)修改某个模式的表、视图、序列和函数所有者,而不需要创建函数,你可以直接在PgAdmin III和psql中使用它:
(在PostgreSql v9.2中测试)
DO $$DECLARE r record;
DECLARE
v_schema varchar := 'public';
v_new_owner varchar := '<NEW_OWNER>';
BEGIN
FOR r IN
select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.tables where table_schema = v_schema
union all
select 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.sequences where sequence_schema = v_schema
union all
select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.views where table_schema = v_schema
union all
select 'ALTER FUNCTION "'||nsp.nspname||'"."'||p.proname||'"('||pg_get_function_identity_arguments(p.oid)||') OWNER TO ' || v_new_owner || ';' as a from pg_proc p join pg_namespace nsp ON p.pronamespace = nsp.oid where nsp.nspname = v_schema
LOOP
EXECUTE r.a;
END LOOP;
END$$;
根据@rkj, @ alannrose, @SharoonThomas, @user3560574提供的答案和@ a_horis_with_no_name的回答
非常感谢。
更好的方法是:更改数据库和模式所有者。
DO $$DECLARE r record;
DECLARE
v_schema varchar := 'public';
v_new_owner varchar := 'admin_ctes';
BEGIN
FOR r IN
select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.tables where table_schema = v_schema
union all
select 'ALTER TABLE "' || sequence_schema || '"."' || sequence_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.sequences where sequence_schema = v_schema
union all
select 'ALTER TABLE "' || table_schema || '"."' || table_name || '" OWNER TO ' || v_new_owner || ';' as a from information_schema.views where table_schema = v_schema
union all
select 'ALTER FUNCTION "'||nsp.nspname||'"."'||p.proname||'"('||pg_get_function_identity_arguments(p.oid)||') OWNER TO ' || v_new_owner || ';' as a from pg_proc p join pg_namespace nsp ON p.pronamespace = nsp.oid where nsp.nspname = v_schema
union all
select 'ALTER SCHEMA "' || v_schema || '" OWNER TO ' || v_new_owner
union all
select 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner
LOOP
EXECUTE r.a;
END LOOP;
END$$;
@Alex Soto的答案是正确的,@Yoav Aner上传的要点也有效,前提是在表/视图名称中没有特殊字符(在postgres中是合法的)。
你需要转义它们来工作,我已经上传了一个要点:https://gist.github.com/2911117
与@AlexSoto的函数方法相同:
IFS=$'\n'
for fnc in `psql -qAt -c "SELECT '\"' || p.proname||'\"' || '(' || pg_catalog.pg_get_function_identity_arguments(p.oid) || ')' FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid WHERE n.nspname = 'public';" YOUR_DB` ; do psql -c "alter function $fnc owner to NEW_OWNER" YOUR_DB; done