Blog

Blog

Dropper les objets d'un user

Sous Oracle, un user possède son propre schéma dans lequel il peut stocker ses objets (principalement des vues et des fonctions/procédures stockées). Certains gèrent les droits ainsi. La suppression du user supprime alors les objets en cascade.
Sous PostgreSQL, il n'est pas possible de supprimer un rôle qui est encore propriétaire d'objets.

Suppression de tous les objets

Il est possible de supprimer tous les objets d'un user (par base de données) en faisant cette simple commande:
DROP OWNED BY doomed_role;

Changement de propriétaire pour tous les objets

On peut aussi vouloir conserver les objets dont le rôle est propriétaire tout en supprimant le rôle.
REASSIGN OWNED BY doomed_role TO successor_role;

Suppression d'objets particuliers

Si on souhaite supprimer toutes les fonctions, toutes les vues ou toutes les tables possédées par un user tout en conservant les autres objets, il va falloir utiliser un bout de code.

Les fonctions

Il est possible de supprimer toutes les fonctions détenues par un user en utilisant ce code:
create function drop_functions_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s(%s)', 
            nspname, proname, pg_get_function_identity_arguments(p.oid))
        from pg_proc p
        join pg_namespace n on n.oid = pronamespace
        where p.proowner = $1
    loop
        if do_drop then
            execute format('drop function %s', r);
        end if;
        return next r;
    end loop;
end $$;

Les vues

Pour les vues détenues par un user, c'est cette fonction qu'on utilisera:
create function drop_views_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s', nspname, relname)
        from pg_class c
        join pg_namespace n on n.oid = relnamespace
        where c.relowner = $1
        and relkind = 'v'
    loop
        if do_drop then
            execute format('drop view %s', r);
        end if;
        return next r;
    end loop;
end $$;

Les tables

Il suffit d'adapter le code du dessus:
create function drop_views_by_owner(owner regrole, do_drop boolean default true)
returns setof text language plpgsql as $$
declare r text;
begin
    for r in 
        select format('%s.%s', nspname, relname)
        from pg_class c
        join pg_namespace n on n.oid = relnamespace
        where c.relowner = $1
        and relkind = 'r'
    loop
        if do_drop then
            execute format('drop table %s cascade', r);
        end if;
        return next r;
    end loop;
end $$;

Pour en savoir plus 

La documentation prévoit une page sur la suppression des rôles : https://www.postgresql.org/docs/current/static/role-removal.html