Modyfikuj właściciela na wszystkich tabelach jednocześnie w PostgreSQL
Jak zmodyfikować właściciela wszystkich tabel w bazie danych PostgreSQL?
Próbowałem ALTER TABLE * OWNER TO new_owner
ale nie obsługuje składni gwiazdki.
19 answers
Zobacz REASSIGN OWNED
polecenie
Uwaga: Jak wspomina @trygvis W Odpowiedzi poniżej ,REASSIGN OWNED
polecenie jest dostępne co najmniej od wersji 8.2 i jest o wiele łatwiejszą metodą.
Ponieważ zmieniasz własność dla wszystkich tabel, prawdopodobnie chcesz również widoki i sekwencje. Oto co zrobiłem:
Tabele:
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 table \"$tbl\" owner to NEW_OWNER" YOUR_DB ; done
Odsłon:
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
Mógłbyś prawdopodobnie wyschnąć , że trochę w górę od zdania alter są identyczne dla wszystkich trzech.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-10-02 11:45:54
Możesz użyć REASSIGN OWNED
dowództwo.
Synopsis:
REASSIGN OWNED BY old_role [, ...] TO new_role
To zmienia wszystkie obiekty należące do old_role
na nową rolę. Nie musisz myśleć o tym, jakie obiekty posiada użytkownik, Wszystkie zostaną zmienione. Zauważ, że dotyczy to tylko obiektów wewnątrz pojedynczej bazy danych. Nie zmienia również właściciela samej bazy danych.
Jest dostępny z powrotem do co najmniej 8.2. Ich dokumentacja online sięga aż tak daleko wstecz.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-12-05 12:55:24
To: http://archives.postgresql.org/pgsql-bugs/2007-10/msg00234.php jest również miłym i szybkim rozwiązaniem i działa dla wielu schematów w jednej bazie danych:
Tabele
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;
Views
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;
Zmaterializowane Widoki
Na podstawie tej odpowiedzi
SELECT 'ALTER TABLE '|| oid::regclass::text ||' OWNER TO my_new_owner;'
FROM pg_class WHERE relkind = 'm'
ORDER BY oid;
To generuje wszystkie wymagane ALTER TABLE
/ ALTER SEQUENCE
/ ALTER VIEW
wypowiedzi, skopiuj je i wklej z powrotem do plsql do ich uruchomienia.
Sprawdź swoją pracę w psql wykonując:
\dt *.*
\ds *.*
\dv *.*
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:55:10
Jeśli chcesz to zrobić w jednym poleceniu 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 jest nową nazwą postgresql nowego właściciela.
W większości przypadków musisz być superużytkownikiem, aby to wykonać. Możesz tego uniknąć, zmieniając właściciela z własnego użytkownika na Grupę ról, którą jesteś członek.
Dzięki RhodiumToad na # postgresql za pomoc w tym.Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-04-02 14:11:35
Ostatnio musiałem zmienić własność wszystkich obiektów w bazie danych. Chociaż tabele, widoki, wyzwalacze i sekwencje zostały nieco łatwo zmienione, powyższe podejście nie powiodło się dla funkcji, ponieważ podpis jest częścią nazwy funkcji. Przyznam, że mam tło MySQL i nie jestem tak obeznany z Postgresem.
Jednakże, pg_dump pozwala na zrzut tylko schematu, który zawiera ALTER XXX OWNER to yyy; instrukcje, których potrzebujesz. Oto mój kawałek magii muszli na temat
pg_dump -s YOUR_DB | grep -i 'owner to' | sed -e 's/OWNER TO .*;/OWNER TO NEW_OWNER;/i' | psqL YOUR_DB
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-08-21 01:29:05
Bardzo proste, spróbuj...
select 'ALTER TABLE ' || table_name || ' OWNER TO myuser;' from information_schema.tables where table_schema = 'public';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-02-06 13:02:32
Podoba mi się ten, ponieważ modyfikuje tabele, widoki, sekwencje i Funkcje właściciel pewnego schematu w one go (w jednym poleceniu 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 ta odpowiedź przez @a_horse_with_no_name
Dziękuję bardzo.Jeszcze lepiej: również zmienić bazę danych i Schemat właściciela.
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$$;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 11:47:25
Jest bardzo proste
- su-postgres
- psql
- resign OWNED by [old_user] TO [new_user];
- \C [twoja baza danych]
- resign OWNED by [old_user] TO [new_user];
Zrobione.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-04-11 02:40:50
Możesz spróbować 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$$;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-04-22 13:07:27
Nie ma takiej komendy w PostgreSQL. Ale można obejść go za pomocą metody I opisane jakiś czas temu dla dotacji.
Na podstawie odpowiedzi autorstwa 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
$$;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:18:22
Odpowiedź @Alex Soto jest właściwa, a gist przesłany przez @ Yoav Aner działa również pod warunkiem, że nie ma znaków specjalnych w nazwach tabeli/widoku(które są legalne w postgres).
Musisz uciec od nich do pracy, a ja wgrałem do tego gist: https://gist.github.com/2911117
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-06-15 05:46:20
Począwszy od PostgreSQL 9.0, masz możliwość GRANT [priv name] ON ALL [object type] IN SCHEMA
gdzie [priv name]
jest typowym SELECT, INSERT, UPDATE, DELETE, etc
i {[3] } może być jednym z:
TABLES
SEQUENCES
FUNCTIONS
PostgreSQL ' s docs on GRANT
oraz REVOKE
przejdź do szczegółów na ten temat. W niektórych sytuacjach nadal wymagane jest stosowanie sztuczek związanych z katalogami systemowymi (pg_catalog.pg_*
), ale nie jest to tak powszechne. Często wykonuję następujące czynności:
-
BEGIN
transakcja aby zmodyfikować privs - zmiana własności
DATABASES
na "rolę DBA" - zmiana własności
SCHEMAS
na "rolę DBA" -
[[13]} privs na wszystkich
TABLES
,SEQUENCES
iFUNCTIONS
ze wszystkich ról -
GRANT SELECT, INSERT, UPDATE, DELETE
na odpowiednich / odpowiednich tabel do odpowiednich ról -
COMMIT
transakcja DCL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-02-09 17:11:04
pg_dump as insert statements
pg_dump -d -O database filename
-d ( data as inserts ) -O ( capital O is no owner )
Następnie wrzuć plik kopii zapasowej z powrotem do PostgreSQL używając:
psql -d database -U username -h hostname < filename
Ponieważ nie ma właściciela, wtedy wszystkie utworzone tabele, Schematy itp. są tworzone pod podanym użytkownikiem logowania.
Czytałem, że może to być dobre podejście do migracji między wersjami PostgreSQL.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-09-14 10:48:04
Przyjęte rozwiązanie nie dba o własność funkcji po rozwiązaniu dba o wszystko (podczas przeglądania zauważyłem, że jest podobne 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};"
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2012-10-07 20:58:58
Stworzyłem do tego wygodny skrypt; pg_change_db_owner.sh . skrypt ten zmienia właściciela 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 zmienić własność wszystkich obiektów, w określonej bazie danych,
należy do konkretnej roli bazy danych, wtedy możesz po prostu użyć polecenia REASSIGN OWNED
.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-08-14 20:42:56
Następujący prostszy skrypt powłoki dział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
Where input $1-username (baza danych) $2 = istniejący schemat $3 = do nowego schematu.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-02-19 21:57:37
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-09-06 09:49:36
Docker: Modyfikuj właściciela wszystkich tabel + sekwencje
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-04 15:43:20