PostgreSQL: Ändern Sie OWNER in allen Tabellen gleichzeitig in PostgreSQL

Question

Wie kann ich den Eigentümer aller Tabellen in einer PostgreSQL-Datenbank ändern?

Ich habe versucht ALTER TABLE * OWNER TO new_owner aber es unterstützt nicht die Sternchensyntax.

Answer 1

Ich mag diese Variante, da sie die Tabellen , Ansichten , Sequenzen y Funktionen Besitzer eines bestimmten Schema en auf einen Schlag (in einer Sql-Anweisung), ohne eine Funktion zu erstellen, und Sie können sie direkt in PgAdmin III y psql :

(Getestet in 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$$;

Basierend auf den Antworten von @rkj, @AlannaRose, @SharoonThomas, @user3560574 und 本答 by @a_horse_with_no_name

Herzlichen Dank.

---

Besser noch: Ändern Sie auch Datenbank y Schema Eigentümer.

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

Answer 2

Wenn der aktuelle Besitzer nicht Postgres ist, können Sie dies verwenden:

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

Aber wenn der aktuelle Besitzer Postgres ist, erhalten Sie definitiv Fehler, so dass Sie @dvanrensburg Antwort verwenden müssen, aber wenn Sie Befehle in der gleichen Sql ausführen möchten, verwenden Sie diesen Befehl basierend auf Bedarf:

Datenbank

ALTER DATABASE target_database OWNER TO new_onwer;

Tische

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

Sequenzen

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

Ansichten

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

Schemata

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

Beachten Sie auch die Funktionen und anderen Komponenten der Datenbank, die möglicherweise Mitgliedschaften ändern müssen

Funktionen und Auslösefunktionen

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

Answer 3

Vor kurzem musste ich die Eigentumsverhältnisse aller Objekte in einer Datenbank ändern. Obwohl sich Tabellen, Ansichten, Trigger und Sequenzen relativ leicht ändern ließen, scheiterte der obige Ansatz bei Funktionen, da die Signatur Teil des Funktionsnamens ist. Zugegeben, ich habe einen MySQL-Hintergrund und bin nicht so vertraut mit Postgres.

Allerdings, pg_dump ermöglicht es Ihnen, nur das Schema zu dumpen, und dieses enthält die ALTER xxx OWNER TO yyy; Angaben, die Sie benötigen. Hier ist mein bisschen Shell-Magie zu diesem Thema

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

Answer 4

Sehr einfach, probieren Sie es aus...

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

Answer 5

Ich musste die Eigentümerschaft von Tabellen, Ansichten und Sequenzen ändern und habe festgestellt, dass die von @rjk gepostete großartige Lösung gut funktioniert - bis auf ein Detail: Wenn die Objektnamen in gemischter Groß- und Kleinschreibung vorliegen (z.B. "Tabellenname"), schlägt dies mit einem "not found"-Fehler fehl.
Um dies zu umgehen, umschließen Sie die Objektnamen mit ' " ' ein, etwa so:

Tische

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;

Sequenzen

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;

Ansichten

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;