Cambiare Proprietario DB postgresql

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;