Post date: 7-nov-2016 14.25.35
Modificare proprietario database (non modifica il proprietario delle tabelle)
ALTER DATABASE name OWNER TO new_owner;
Rinomina database (non devi essere dentro lo stesso db)
ALTER DATABASE myolddbname_here RENAME TO mynewdbname_here;
\dt *.* visualizza tabelle
oppure
elenco tabelle
SELECT table_name from information_schema.tables WHERE table_schema = 'public';
\ds *.* visualizza sequenze
\dv *.* visualizza viste
Cambiare il proprietario delle tabelle, sequenze, viste e funzioni del singolo db
----da psql--
DO $$DECLARE r record;
DECLARE
v_schema varchar := 'public';
v_new_owner varchar := 'nuovo-proprietario';
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$$;
--------------
mentre il comando REASSIGN OWNED BY (modifica il proprietario di tutti i db e oggetti a cui è assegnato)
REASSIGN OWNED BY vecchioproprietario TO nuovoproprietario;