postgresql owner of table




PostgreSQL에서 모든 테이블의 OWNER를 동시에 수정하십시오 (13)

PostgreSQL 데이터베이스에있는 모든 테이블의 소유자를 수정하려면 어떻게해야합니까?

나는 ALTER TABLE * OWNER TO new_owner 시도했지만 별표 구문을 지원하지 않습니다.


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

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 table \"$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 table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done

당신은 아마도 alter 문이 세 가지 모두에 대해 동일하기 때문에 조금 위로 DRY 수 있습니다.


PostgreSQL 9.0부터는 GRANT [priv name] ON ALL [object type] IN SCHEMAGRANT [priv name] ON ALL [object type] IN SCHEMA 할 수 있습니다. 여기서 [priv name] 은 일반적인 SELECT, INSERT, UPDATE, DELETE, etc 이며 [object type] 은 다음 중 하나 일 수 있습니다.

  • TABLES
  • SEQUENCES
  • FUNCTIONS

GRANTGRANT 에 관한 PostgreSQL의 문서는 이것에 관해 더 자세히 설명합니다. 어떤 상황에서는 여전히 시스템 카탈로그 ( pg_catalog.pg_* )와 관련된 트릭을 사용해야하지만 거의 비슷하지는 않습니다. 나는 종종 다음과 같은 일을한다.

  1. privs를 수정하기위한 거래를 시작하십시오.
  2. DATABASES 소유권을 "DBA 역할"로 변경하십시오.
  3. SCHEMAS 소유권을 "DBA 역할"로 변경
  4. 모든 역할에서 모든 TABLES , SEQUENCESFUNCTIONS 에 대한 모든 REVOKE ALL 철회하십시오
  5. 적절한 / 적절한 테이블에 적절한 역할에 대한 GRANT SELECT, INSERT, UPDATE, DELETEGRANT SELECT, INSERT, UPDATE, DELETE 합니다.
  6. DCL 트랜잭션을 COMMIT 하십시오.

PostgreSQL 9에서 다음을 시도 할 수 있습니다.

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT tablename FROM pg_tables WHERE schemaname = 'public'
    LOOP
        EXECUTE 'alter table '|| r.tablename ||' owner to newowner;';
    END LOOP;
END$$;

나는 그것을위한 편리한 스크립트를 만들었습니다. pg_change_db_owner.sh . 이 스크립트는 데이터베이스 스키마의 모든 테이블, 뷰, 시퀀스 및 함수에 대한 소유권을 변경하고 스키마 자체의 소유자도 변경합니다.

특정 데이터베이스 역할이 소유하고있는 특정 데이터베이스에서 모든 개체의 소유권을 변경하려면 REASSIGN OWNED 명령을 대신 사용할 수 있습니다.


나는 최근에 데이터베이스의 모든 객체의 소유권을 변경해야했습니다. 테이블, 뷰, 트리거 및 시퀀스가 ​​다소 쉽게 변경되었지만 서명이 함수 이름의 일부이기 때문에 위의 방법은 함수에 대해 실패했습니다. 필자는 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

매우 간단하다.

  1. 스와 포스트 그레스
  2. psql
  3. [old_user] 님 에의 한 REASSIGN TO [new_user];
  4. \ c [귀하의 데이터베이스]
  5. [old_user] 님 에의 한 REASSIGN TO [new_user];

끝난.


수용된 솔루션은 솔루션이 모든 것을 처리 한 후에 함수 소유권을 처리하지 않습니다 (위의 @magiconair와 비슷한 것으로 판단하면서 검토 함)

echo "Database: ${DB_NAME}"
echo "Schema: ${SCHEMA}"
echo "User: ${NEW_OWNER}"

pg_dump -s -c -U postgres ${DB_NAME} | egrep "${SCHEMA}\..*OWNER TO"| sed -e "s/OWNER TO.*;$/OWNER TO ${NEW_OWNER};/" | psql -U postgres -d ${DB_NAME}
# do following as last step to allow recovery
psql -U postgres -d postgres -c "ALTER DATABASE ${DB_NAME} OWNER TO ${NEW_OWNER};"

이 : http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php 또한 좋은 빠른 솔루션이며, 하나의 데이터베이스에서 여러 스키마에 사용할 수 있습니다.

테이블

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;

구체화 된 뷰

이 대답을 바탕으로

SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;

이렇게하면 필요한 모든 ALTER TABLE / ALTER SEQUENCE / ALTER VIEW 문이 생성되고이를 복사하여 plsql에 다시 붙여 넣어 실행할 수 있습니다.

다음을 수행하여 psql에서 작업을 확인하십시오.

\dt *.*
\ds *.*
\dv *.*

필자는 함수를 만들지 않고 특정 스키마테이블 , , 시퀀스함수 소유자를 하나의 sql 문에서 수정 하고 PgAdmin IIIpsql 에서 직접 사용할 수 있으므로이 코드를 좋아합니다 .

(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, @AlannaRose, @SharoonThomas, @ user3560574 그리고이 대답 은 @a_horse_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$$;

함수에 대한 @ 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

REASSIGN OWNED 명령을 사용할 수 있습니다.

개요:

REASSIGN OWNED BY old_role [, ...] TO 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 버전 간의 마이그레이션에도 좋은 접근 방법이 될 수 있음을 읽었습니다.





postgresql