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.
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.
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$$;
Zwei Fragen: 1) Es sieht so aus, als ob die erste und dritte "ALTER TABLE"-Zeile Duplikate sind. Ist das beabsichtigt (müssen Sie z.B. zwei Durchläufe über die Tabellen machen, um die Besitzverhältnisse zu ändern?). 2) Wir stellen fest, dass information_schema.sequences
leer ist, obwohl SELECT c.* FROM pg_class c WHERE c.relkind = 'S';
listet Sequenzen auf. Warum könnten sie nicht übereinstimmen?
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;
$$;
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
Ich bin mir nicht sicher, warum Sie die grep
Befehl. Ich bin selbst neu in Linux, aber nach meinem Verständnis scheint es, dass sed
zu verwenden, zumal Sie ohnehin eine Übereinstimmung ohne Berücksichtigung der Groß- und Kleinschreibung angeben.
Sie könnten einen Hinweis hinzufügen, dass die entsprechenden Zeichenfolgen kopiert und ausgeführt werden müssen. Nicht, dass es nicht offensichtlich wäre :p
Dazu gehört auch das Entfernen aller Anführungszeichen um die alter-Anweisungen. Multi-Cursor oder replace hilft in diesem Fall.
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:
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;
CodeJaeger ist eine Gemeinschaft für Programmierer, die täglich Hilfe erhalten..
Wir haben viele Inhalte, und Sie können auch Ihre eigenen Fragen stellen oder die Fragen anderer Leute lösen.