Jak zmodyfikować właściciela wszystkich tabel w bazie danych PostgreSQL?

Próbowałem, ALTER TABLE * OWNER TO new_ownerale nie obsługuje składni gwiazdki.

Zobacz REASSIGN OWNEDpolecenie

Uwaga: Jak wspomina @trygvis w odpowiedzi poniżej , REASSIGN OWNEDpolecenie jest dostępne od wersji co najmniej 8.2 i jest znacznie łatwiejszą metodą.

Ponieważ zmieniasz własność wszystkich tabel, prawdopodobnie chcesz też widoków i sekwencji. Oto co zrobiłem:

Stoły:

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

Sekwencje:

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

Wyświetlenia:

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

Prawdopodobnie możesz to trochę przesuszyć , ponieważ instrukcje alter są identyczne dla wszystkich trzech.

Możesz użyć REASSIGN OWNEDpolecenia.

Streszczenie:

REASSIGN OWNED BY old_role [, ...] TO new_role

Spowoduje to zmianę wszystkich obiektów należących old_roledo nowej roli. Nie musisz myśleć o tym, jakie obiekty ma użytkownik, wszystkie zostaną zmienione. Pamiętaj, że dotyczy to tylko obiektów w jednej bazie danych. Nie zmienia to również właściciela samej bazy danych.

Jest dostępny w wersji co najmniej 8.2. Ich dokumentacja online sięga tak daleko wstecz.

To: http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php jest również dobrym i szybkim rozwiązaniem, które działa na wielu schematach w jednej bazie danych:

Stoły

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;

Sekwencje

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;

Wyświetlenia

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;

Widoki zmaterializowane

Na podstawie tej odpowiedzi

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

Generuje to wszystkie wymagane instrukcje ALTER TABLE/ ALTER SEQUENCE/ ALTER VIEW, kopiuje je i wkleja z powrotem do plsql, aby je uruchomić.

Sprawdź swoją pracę w psql, wykonując:

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

Jeśli chcesz to zrobić w jednej instrukcji SQL, musisz zdefiniować funkcję exec (), jak wspomniano w http://wiki.postgresql.org/wiki/Dynamic_DDL

CREATE FUNCTION exec(text) returns text language plpgsql volatile
  AS $f$
    BEGIN
      EXECUTE $1;
      RETURN $1;
    END;
$f$;

Następnie możesz wykonać to zapytanie, zmieni ono właściciela tabel, sekwencji i widoków:

SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' ||
            quote_ident(s.relname) || ' OWNER TO $NEWUSER')
  FROM (SELECT nspname, relname
          FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid) 
         WHERE nspname NOT LIKE E'pg\\_%' AND 
               nspname <> 'information_schema' AND 
               relkind IN ('r','S','v') ORDER BY relkind = 'S') s;

$ NEWUSER to nowa nazwa nowego właściciela postgresql.

W większości przypadków musisz być superużytkownikiem, aby to wykonać. Można tego uniknąć, zmieniając właściciela z własnego użytkownika na grupę ról, do której należysz.

Dzięki RhodiumToad na #postgresql za pomoc w tym.

Niedawno musiałem zmienić własność wszystkich obiektów w bazie danych. Chociaż tabele, widoki, wyzwalacze i sekwencje zostały nieco zmienione, powyższe podejście nie powiodło się dla funkcji, ponieważ podpis jest częścią nazwy funkcji. To prawda, że ​​mam doświadczenie w MySQL i nie znam się na Postgresie.

Jednak pg_dump pozwala na zrzucenie tylko schematu, a zawiera on ALTER xxx WŁAŚCICIEL DO rrr; oświadczenia, których potrzebujesz. Oto moja odrobina magii powłoki na ten temat

pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB

bardzo proste, spróbuj ...

 select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';

jest bardzo proste

1. su - postgres
2. psql
3. PONOWNIE PRZEKŁADAJ SIĘ PRZEZ [old_user] DO [new_user];
4. \ c [twoja baza danych]
5. PONOWNIE PRZEKŁADAJ SIĘ PRZEZ [old_user] DO [new_user];

gotowy.

Podoba mi się ten, ponieważ modyfikuje on tabele , widoki , sekwencje i funkcje właściciela określonego schematu za jednym razem (w jednej instrukcji SQL), bez tworzenia funkcji i można go używać bezpośrednio w PgAdmin III i psql :

(Testowane w 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$$;

Na podstawie odpowiedzi udzielonych przez @rkj, @AlannaRose, @SharoonThomas, @ user3560574 i tę odpowiedź przez @a_horse_with_no_name

Wielkie dzięki.

Jeszcze lepiej: zmień także właściciela bazy danych i schematu .

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$$;

Musiałem zmienić własność tabel, widoków i sekwencji i stwierdziłem, że świetne rozwiązanie opublikowane przez @rjk działa dobrze - pomimo jednego szczegółu: jeśli nazwy obiektów mają różne litery (np. „TableName”), to nie powiedzie się z „ nie znaleziono błędu.
Aby to obejść, zawiń nazwy obiektów następującymi znakami „”:

Stoły

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;

Sekwencje

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;

Wyświetlenia

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;

Możesz spróbować wykonać następujące czynności w 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$$;

W PostgreSQL nie ma takiego polecenia. Ale możesz obejść to przy użyciu metody, którą opisałem jakiś czas temu dla GRANT.

Na podstawie odpowiedzi elysch , oto rozwiązanie dla wielu schematów:

DO $$
DECLARE 
  r record;
  i int;
  v_schema text[] := '{public,schema1,schema2,schema3}';
  v_new_owner varchar := 'my_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 = ANY (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 = ANY (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 = ANY (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 = ANY (v_schema)
        union all
        select 'ALTER DATABASE "' || current_database() || '" OWNER TO ' || v_new_owner 
    LOOP
        EXECUTE r.a;
    END LOOP;
    FOR i IN array_lower(v_schema,1) .. array_upper(v_schema,1)
    LOOP
        EXECUTE 'ALTER SCHEMA "' || v_schema[i] || '" OWNER TO ' || v_new_owner ;
    END LOOP;
END
$$;

Odpowiedź @Alex Soto jest prawidłowa, a treść przesłana przez @Yoav Aner również działa, pod warunkiem, że nie ma żadnych znaków specjalnych w nazwach tabel / widoków (które są legalne w postgresie).

Musisz uciec od nich, aby pracować, a ja przesłałem do tego listę: https://gist.github.com/2911117

pg_dump as insert statements 
pg_dump -d -O database filename
-d ( data as inserts ) -O ( capital O is no owner )

Następnie przenieś plik kopii zapasowej z powrotem do PostgreSQL, używając:

psql -d database -U username -h hostname < filename

Ponieważ nie ma w nim właściciela, wszystkie utworzone tabele, schematy itp. Są tworzone w ramach określonego użytkownika logowania.

Przeczytałem, że może to być również dobre podejście do migracji między wersjami PostgreSQL.

Stworzyłem do tego wygodny skrypt; pg_change_db_owner.sh . Ten skrypt zmienia własność wszystkich tabel, widoków, sekwencji i funkcji w schemacie bazy danych, a także właściciela samego schematu.

Pamiętaj, że jeśli chcesz tylko zmienić własność wszystkich obiektów w określonej bazie danych, należących do określonej roli bazy danych, możesz po prostu użyć polecenia REASSIGN OWNEDzamiast tego.

Począwszy od PostgreSQL 9.0, możesz określić, GRANT [priv name] ON ALL [object type] IN SCHEMAgdzie [priv name]jest typowy SELECT, INSERT, UPDATE, DELETE, etci [object type]może być jednym z:

• TABLES
• SEQUENCES
• FUNCTIONS

Dokumenty PostgreSQL GRANTi REVOKEprzejdź do bardziej szczegółowych informacji na ten temat. W niektórych sytuacjach nadal wymagane jest stosowanie sztuczek obejmujących katalogi systemowe ( pg_catalog.pg_*), ale nie jest to tak powszechne. Często wykonuję następujące czynności:

1. BEGIN transakcja modyfikacji prywatny
2. Zmień właściciela DATABASESna „rolę DBA”
3. Zmień właściciela SCHEMASna „rolę DBA”
4. REVOKE ALLprivs na wszystko TABLES, SEQUENCESa FUNCTIONSze wszystkich ról
5. GRANT SELECT, INSERT, UPDATE, DELETE na odpowiednich / odpowiednich tabelach do odpowiednich ról
6. COMMIT transakcja DCL.

Zaakceptowane rozwiązanie nie dba o własność funkcji po rozwiązaniu dba o wszystko (podczas przeglądu zauważyłem, że jest podobny do @magiconair powyżej)

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};"

Poniższy prostszy skrypt powłoki zadziałał dla mnie.

#!/bin/bash
for i in  `psql -U $1  -qt -c  "select tablename from pg_tables where schemaname='$2'"`
do
psql -U $1 -c  "alter table $2.$i set schema $3"
done

Gdzie wpisz 1 $ - nazwa użytkownika (baza danych) 2 $ = istniejący schemat 3 USD = nowy schemat.

To samo, co podejście @ AlexSoto do funkcji:

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

Docker: modyfikuj właściciela wszystkich tabel i sekwencji

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