changer le propriétaire (owner) d'une base postgresql

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

-- Creation d'une fonction pour exécuter les commandes a chaque occurrence

create or replace function exec(text) returns void 

as '

begin

execute $1;  

end   '                            

language plpgsql;

-- Modification du proprietaire de la base

alter database mabase owner to mongroupe_owner;

-- Modification du propriétaire des tables

select table_schema||'.'||table_name || exec('alter table '||table_schema||'."'||table_name||'" owner to mongroupe_owner')as "Changed ownership to table" 

from information_schema.tables 

where table_schema not in ('information_schema', 'pg_catalog');

-- Modification du proprietaire des sequences

select sequence_schema||'.'||sequence_name || exec('alter table '||sequence_schema||'.'||sequence_name||' owner to mongroupe_owner') 

    as "Changed sequence ownership" 

from information_schema.sequences where sequence_schema not in ('information_schema', 'pg_catalog');

-- modification du propriétaire des schemas

select distinct table_schema || exec ('alter schema '||table_schema||' owner to mongroupe_owner') as "Changed schema ownership"

from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog');

-- attribution des droits d’exécution des fonctions

select distinct table_schema || exec('grant all on all functions in schema '||table_schema|| 

' to group mongroupe_owner') as "granted all to all functions in schema" 

from information_schema.tables where table_schema not in ('information_schema', 'pg_catalog');

-- Modification du propriétaire des fonctions

select proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ') ' || exec('alter function ' || n.nspname || '.' || proname || '(' || pg_catalog.pg_get_function_identity_arguments(pr.oid) || ') owner to mongroupe_owner') as "Changed function ownership"

 from pg_proc pr join pg_namespace n on pr.pronamespace = n.oid

where n.nspname not in ('information_schema', 'pg_catalog');