Techniques 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';
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;
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;
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$;
$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 ;
$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 07/01/2021 - Quillevere.net
Commentaires
Aucune inscription n'est requise si vous souhaitez
Rechercher sur le site
  |  RSS |  Informations |