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

Où sont mes logs (la suite) ?

Dans l'article Où sont mes logs je vous avais indiqué comment trouver les logs d'une instance PostgreSQL qui utilise le log collector (paramètre log_collector =on).
Mais que se passe-t-il si le log collector n'est pas activé ?

Stderr

Restons dans le cas par défaut où les logs sont envoyés sur stderr. Il s'agit du cas par défaut car la valeur par défaut du paramètre log_destination est stderr.
Ce n'est plus forcément la meilleure valeur, mais j'espère avoir le temps d'un autre article pour parler de ça...

Pid

Pour pouvoir trouver où sont vos logs, vous allez devoir récupérer le pid de votre process postgres principal.
Voici deux manières de le faire:
  1. Comme un ingénieur système
    ps -ef | grep postgres
    postgres 5109 1 0 16:25 ? 00:00:00 /usr/lib/postgresql/9.6/bin/postgres -D /var/lib/postgresql/9.6/main -c config_file=/etc/postgresql/9.6/main/postgresql.conf
    postgres 5116 5109 0 16:25 ? 00:00:00 postgres: checkpointer process 
    postgres 5117 5109 0 16:25 ? 00:00:00 postgres: writer process 
    postgres 5134 5109 0 16:25 ? 00:00:00 postgres: wal writer process 
    postgres 5135 5109 0 16:25 ? 00:00:00 postgres: autovacuum launcher process 
    postgres 5136 5109 0 16:25 ? 00:00:00 postgres: archiver process  last was 00000008.history
    postgres 5137 5109 0 16:25 ? 00:00:00 postgres: stats collector process
  2. Comme un DBA
    SELECT pg_backend_pid();
    pg_backend_pid
    ----------------
    5109
    (1 row)
    

Trouver les logs

Une fois le PID récupéré, vous trouverez les logs dans le répertoire /proc/<PID>/fd/2.
C'est là que vous verrez éventuellement si la sortie d'erreur est redirigée.
Par exemple sur un postgreSQL lancé avec le wrapper perl pg_ctlcluster, vous trouverez ceci:
sudo ls -l /proc/5109/fd/2
l-wx------ 1 postgres postgres 64 May 10 18:35 /proc/5109/fd/2 -> /var/log/postgresql/postgresql-9.6-main.log

Pour en savoir plus 

La documentation officielle prévoit une page complète sur le reporting d'erreur et le logging. https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html

Passer des options à pg_createcluster

J'adore les wrappers perl intégrés dans les distributions Debian (ou Debian-based). Mais il n'est pas toujours aisé de donner les paramètres que l'on souhaite.
J'ai voulu utiliser pg_createcluster en ajoutant l'option permettant d'avoir un checksum des données sur chaque page.

Createcluster.conf

Le fichier /etc/postgresql-common/createcluster.conf permet de spécifier ce paramètre dans la section:
initdb_options = '--data-checksums'
Si on ne souhaite pas modifier le fichier de paramètre, il est possible de spécifier ce paramètre dans la ligne de commande.

Dans la ligne de commande

Il est possible aussi de spécifier ce paramètre directement dans la ligne de commande ainsi:
sudo pg_createcluster 9.6 main -- '--data-checksums'

Vérifier

On peut ensuite vérifier que le paramètre a bien été pris en compte:
postgres=# show data_checksums;
data_checksums
----------------
on
(1 row)

 Pour en savoir plus

 Vous pouvez toujours lire la page man de pg_createcluster ou aller voir le code du wrapper.