Utilitaires
Scripts
Divers
Jeux
Rechercher
Quillevere.net
Réflexions informatiques

Créer un utilisateur et attribuer des droits sur PostgreSQL

07/01/2021

Voici comment créer et supprimer un utilisateur sur PostgreSQL ainsi que lui attribuer en masse des droits sur tous les schémas. Dans les exemples ci-dessous, il faut remplacer les variables par les valeurs cibles :

  • utilisateur: identifiant du compte cible
  • passe : mot de passe du compte cible
  • nom_bdd : nom de la base de données cible
  • nom_schema : nom du schéma cible

Une autre approche, qui sera abordée ultérieurement, consiste à créer des rôles génériques et à donner certains rôles aux utilisateurs.

Créer un utilisateur PostgreSQL

CREATE USER utilisateur
WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'passe';

Attribution des droits de connexion et de requêtage seulement :

GRANT CONNECT ON DATABASE nom_bdd TO utilisateur;
GRANT USAGE ON SCHEMA nom_schema TO utilisateur;
GRANT SELECT ON ALL TABLES IN SCHEMA nom_schema TO utilisateur;

Supprimer un utilisateur PostgreSQL

Il faut commencer par supprimer les droits de l'utilisateur sur la base et les schémas :

REVOKE ALL PRIVILEGES ON DATABASE nom_bdd FROM utilisateur;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA nom_schema FROM utilisateur;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA nom_schema FROM utilisateur;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA nom_schema FROM utilisateur;
REVOKE ALL PRIVILEGES ON SCHEMA nom_schema FROM utilisateur;

Puis on peut supprimer l'utilisateur :

DROP USER utilisateur;

Créer un utilisateur avec des droits sur tous les schémas :

Pour gagner du temps, voici une routine permettant de créer un utilisateur et de lui donner accès à tous les schémas présents sur une base.

DO
$do$
BEGIN
 IF NOT EXISTS
 (SELECT rolname FROM pg_catalog.pg_roles WHERE rolname = 'utilisateur') THEN
        CREATE USER utilisateur
        WITH LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 PASSWORD 'passe';
END IF;
END

$do$;
GRANT CONNECT ON DATABASE nom_bdd TO utilisateur;
DO $do$
DECLARE sch text;
BEGIN
 FOR sch IN SELECT nspname FROM pg_namespace WHERE nspname LIKE '%'
 LOOP
    EXECUTE format($$ GRANT USAGE ON SCHEMA %I TO utilisateur $$, sch);
    EXECUTE format($$ GRANT SELECT ON ALL TABLES IN SCHEMA %I TO utilisateur $$, sch);
 END LOOP;
END;
$do$;

Supprimer un utilisateur et révoquer tous ses droits sur les schémas

DO
$do$
DECLARE sch text;
BEGIN
 FOR sch IN SELECT nspname FROM pg_namespace WHERE nspname LIKE '%'
 LOOP
    EXECUTE format($$ REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA %I FROM utilisateur ; $$, sch);
    EXECUTE format($$ REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA %I FROM utilisateur ; $$, sch);
    EXECUTE format($$ REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA %I FROM utilisateur ; $$, sch);
    EXECUTE format($$ REVOKE ALL PRIVILEGES ON SCHEMA %I FROM utilisateur ; $$, sch);
 END LOOP;
END;
$do$;
REVOKE ALL PRIVILEGES ON DATABASE nom_bdd FROM utilisateur ;
DROP USER utilisateur ;
Dernière modification le 18/06/2021 - Quillevere.net

Rechercher sur le site

fr en rss RSS info Informations