Vous appréciez mon travail ?
Je serais ravi de prendre un café !

Vous prenez du plaisir à lire mes articles ? Vous apprenez de nouvelles choses ? Je serais ravis que vous supportiez mon travail avec une petite participation

1 café Merci, vous financez ma dose quotidienne de théïne (oui, en vrai je ne bois pas de café).
5 cafés Génial, ça couvre mes frais de serveur mensuels.
10 cafés Fantastique, avec ça je peux investir dans du matériel et approfondir mes connaissances.
BazinGa's - Tips & tuto IT

PostgreSQL – Vade-mecum SQL – 2/8 – Gérer les objets

Les requêtes

Les bases de données

Créer une base de données

CREATE DATABASE nom_de_ma_base			-- Nom de la base
WITH
	OWNER = nom_user			-- Nom du user propriétaire de la base
	ENCODING = 'UTF8'			-- Encoding de la base : l'idéal est l'UTF8
	TABLESPACE = pg_default			-- Nom de l'espace de stockage des données : pg_defaut par défaut
	LC_COLLATE = 'French_France.1252'	--
	LC_CTYPE = 'French_France.1252'		--
	CONNECTION LIMIT = -1			-- Nombre de connexions simultanées maximal : -1 = infinit
;

Commenter une base

COMMENT ON DATABASE nom_de_ma_base
	IS 'Mon commentaire'
;

Supprimer une base

DROP DATABASE nom_de_ma_base
;

Les tablespace

Créer un tablespace

CREATE TABLESPACE pgraster_data
	OWNER postgres
	LOCATION '/mon_repertoire/de_stockage'
;

Les schémas

Créer un schéma

CREATE SCHEMA mon_schema
;

Supprimer un schéma

DROP SCHEMA mon_schema
;

Les tables

Créer une table

CREATE TABLE mon_schema.ma_table
(
-- Définition des colonnes : nom_colonne type_de_colonne option

	colonne_num_1 smallserial,       -- Valeur autoincrémentée (sur 2 bytes, de 1 à 32767)
	colonne_num_2 serial,            -- Valeur autoincrémentée (sur 4 bytes, de 1 à 2147483647)
	colonne_num_3 bigserial,         -- Valeur autoincrémentée (sur 8 bytes, de 1 à 9223372036854775807)
	colonne_num_4 real,              -- Réel (précision à 6 décimales)
	colonne_num_5 double precision,  -- Réel (précision à 15 décimales)
	colonne_num_6 numeric (10, 2),   -- Valeur décimale (précision : 10 chiffres, échelle : 2 décimales (inclu dans l'échelle)) (Jusqu'à 131072 chiffres avant le point et jusqu'a 16383 chiffres après)
	colonne_num_7 decimal (10, 2),   -- Valeur décimale (précision : 10 chiffres, échelle : 2 décimales (inclu dans l'échelle)) (Jusqu'à 131072 chiffres avant le point et jusqu'a 16383 chiffres après)
	colonne_num_8 smallint,          -- Entier (sur 2 bytes, de -32768 à 32767)
	colonne_num_9 integer,           -- Entier (sur 4 bytes, de -2147483648 à 2147483647)
	colonne_num_10 bigint,           -- Entier (sur 8 bytes, de -9223372036854775808 à 9223372036854775807)

	colonne_txt_1 text,                   -- Texte à longueur variable
	colonne_txt_2 character(10),          -- Texte de 10 caractères de longueur fixe. Si le texte fait moins de 10 caractère, alors PostgreSQL comble avec des espaces.
	colonne_txt_3 char(10),               -- Alias de "character"
	colonne_txt_4 character varying(10),  -- Texte de 10 caractères de longueur variable sans comblement par des espaces.
	colonne_txt_5 varchar(10),            -- Alias de "character varying"

	colonne_date_1 date,          -- Date (année, mois, jour), de -4713 à 5874897. Précision : 1 jour
	colonne_date_2 time (2),      -- Heure du jour (avec choix du nombre de décimal après la seconde) (heure:minute:seconde), de 00:00:00 à 24:00:00. Précision : 1 microseconde
	colonne_date_3 timestamp(2),  -- Marquage temporel (avec choix du nombre de décimal après la seconde) de -4713 à 294276. Précision : 1 microseconde
	colonne_date_4 interval(2),   -- Interval de temps (avec choix du nombre de décimal après la seconde) de -178000000 à 178000000. Précision : 1 microseconde

	colonne_autre_1 money,         -- Valeur monétaire
	colonne_autre_2 boolean,       -- Valeur booléenne (vrai/faux)
	colonne_autre_3 uuid,          -- Universally Unique Identifier
	colonne_autre_4 bytea,         -- Données binaires
	colonne_autre_5 cidr,          -- Adresse IP v4 ou IP v6
	colonne_autre_6 macaddr,       -- Adresse MAC

	-- En règle générale, les colonnes géométriques sont nommée "geom" mais rien empêche d'utiliser un autre nom
	geom_1 geometry(Point,2154),              -- Données géométrique (géométrie de type point, projection = 2154)
	geom_2 geometry(MultiPoint,2154),         -- Données géométrique (géométrie de type multipoint, projection = 2154)
	geom_3 geometry(Linestring,2154),         -- Données géométrique (géométrie de type ligne, projection = 2154)
	geom_4 geometry(MultiLinestring,2154),    -- Données géométrique (géométrie de type multiligne, projection = 2154)
	geom_5 geometry(Polygon,2154),            -- Données géométrique (géométrie de type polygone, projection = 2154)
	geom_6 geometry(MultiPolygon,2154),       -- Données géométrique (géométrie de type multipolygone, projection = 2154)
	geom_7 geometry(PolygonWithHole,2154),    -- Données géométrique (géométrie de type polygone à trou, projection = 2154)
	geom_8 geometry(GeometryCollection,2154), -- Données géométrique (géométrie de n'importe quel type, projection = 2154)

	colonne_option_1 text DEFAULT 'Texte par défaut',    -- Définir une valeur par défaut
	colonne_option_2 integer DEFAULT nextval('mon_schema.ma_sequence')                           -- Paramétrer une valeur par défaut issue d'une séquence (ici la prochaine valeur d'une séquence)
)

-- Pour ne pas utiliser l'OIDS (sorte de clé primaire automatique) ajoutez ceci :
WITH (
	OIDS=FALSE
)
;

Créer un table à partir d’une autre table

CREATE TABLE mon_schema.ma_table AS
	SELECT                              -- SELECT = sélection des données à reprendre
		id,
		colonne_3 as colonne_1,     -- Le "as" permet de redéfinir le nom de la colonne, le type est conservé
		colonne_7 as colonne_2
	FROM                                 -- FROM = désignation de la table à reprendre
		mon_schema.ma_table_initiale
;

Modifier les tables

Modifier le propriétaires

ALTER TABLE mon_schema.ma_table
	OWNER TO mon_user
;

Changer la table de schéma

ALTER TABLE mon_schema.ma_table
	SET SCHEMA nouveau_schem
;

Ajouter une colonne

ALTER TABLE mon_schema.ma_table 
	ADD COLUMN nom_colonne type_de_colonne
;

Renommer une colonne

ALTER TABLE mon_schema.ma_table
	RENAME COLUMN nom_colonne TO nouveau_nom_colonne 
;

Modifier le type d’une colonne

ALTER TABLE mon_schema.ma_table
     ALTER COLUMN nom_colonne
         TYPE nouveau_type_de_colonne
         USING ma_fonction(ma_colonne_de_donnée)       -- Transformation à effectuer sur la donnée s'il faut la changer de type également
;

Quelques exemples

-- Modifier la gémométrie d'une colonne
ALTER TABLE mon_schema.ma_table
	ALTER COLUMN geom TYPE geometry(MultiLineString, 2154)
		USING ST_Force2D(geom) -- On force les coordonnées en 2D
;

-- Reprojeter en 3945
ALTER TABLE mon_schema.ma_table 
	ALTER COLUMN geom 
		TYPE Geometry(MultiPolygon, 3945) 
		USING ST_Transform(geom, 3945)
;

-- Tout mettre dans une geometrycollection
ALTER TABLE mon_schema.ma_table 
	ALTER COLUMN geom 
		TYPE Geometry(GeometryCollection, 2154) 
		USING ST_ForceCollection(geom)
;

-- Tout retirer d'une geometrycollection
ALTER TABLE mon_schema.ma_table 
	ALTER COLUMN geom 
		TYPE Geometry(Geometry, 2154) 
		USING ST_CollectionHomogenize(geom);
;

-- Changer une colonne texte en integer
ALTER TABLE mon_schema.ma_table 
	 ALTER COLUMN ma_colonne 
	 TYPE integer 
	 USING (ma_colonne::integer)
;

-- Changer une colonne integer en texte
ALTER TABLE mon_schema.ma_table 
	ALTER COLUMN ma_colonne 
	TYPE character varying(8) 
	USING (ma_colonne::varchar(8))
;

-- Changer une colonne multigéométrie en simple géométrie en ne conservant que la première géométrie
ALTER TABLE mon_schema.ma_table
	ADD geom_simple geometry(Linestring,2154);

UPDATE mon_schema.ma_table
	SET geom_simple = (ST_Dump(geom)).geom;

ALTER TABLE mon_schema.ma_table
	DROP COLUMN geom;

ALTER TABLE mon_schema.ma_table
	RENAME COLUMN geom_simple TO geom;

CREATE INDEX ma_table_idx_geom
	ON mon_schema.ma_table
	USING gist
	(geom);

Supprimer une table

DROP TABLE mon_schema.ma_table
;

Les autres objets

Créer une séquence

CREATE SEQUENCE mon_schema.ma_sequence
	INCREMENT 1                     -- Valeur d'incrémentation
	MINVALUE 1                      -- Valeur minimale
	MAXVALUE 9223372036854775807    -- Valeur minimale
	START 1                         -- Valeur actuelle (s'incrémente au fur et a mesure)
	CACHE 1
;

Modifier une séquence

-- Changer la prochaine valeur
ALTER SEQUENCE mon_schema.ma_sequence
    RESTART WITH n;

Créer un index

CREATE INDEX nom_index
	ON mon_schema.ma_table
		USING gist(geom)     -- Méthode d'indexation (ici géométrique : gist, btree pour l'alphanumérique)
;

Les utilisateurs ou « rôle »

Créer un rôle

-- Cas général
CREATE ROLE nom_utilisateur
;


-- Créer un rôle avec des options
CREATE ROLE nom ATTRIBUT
;

-- Les attribut :
SUPERUSER 				-- Le rôle sera superutilisateur
PASSWORD 'mot_de_passe' 		-- Ajoute un mot de passe au rôle
LOGIN 					-- Droit de connexion à une base
CREATEDB 				-- Droit de création d'une BDD
CREATEROLE 				-- Droit de création d'un autre rôle
VALID UNTIL 'dateheure' 		-- Validité limit du mot de passe
IN ROLE nom_role 			-- Ajoute l'appartenance à un rôle group
ROLE nom_role 				-- Cette clause liste les rôles membres du nouveau rôle. Le nouveau rôle devient ainsi un « groupe ».
ADMIN nom_role 				-- Comme ROLE mais ajoute l'option WITH ADMIN OPTION
CONNECTION LIMIT limiteconnexion 	-- Le nombre maximum de connexions concurrentes possibles pour le rôle
INHERIT               			-- Le rôle héritera des droits du groupe auquel il appartient
NOINHERIT             			-- Le rôle n'héritera d'aucun droits des rôles auxquels il appartient

-- CREATE USER est identique à CREATE ROLE mais utilise l'option LOGIN par défaut :
CREATE USER nom
;

Modifier un rôle

ALTER ROLE nom_role ATTRIBUT
;

Opérations sur les objets d’un rôle

-- Réassigner les objets d'un rôle à un autre rôle
REASSIGN OWNED BY ancien_role_proprietaire TO nouveau_role_proprietaire
;

-- Supprimer tous les objets détenus par un rôle
DROP OWNED BY doomed_role
;

Supprimer un rôle

DROP ROLE nom_utilisateur           -- Attention, pour supprimer un rôle, il faut supprimer ou réattribuer tous les objet lui appartenant.
;

Groupe rôle
Dans PostgreSQL, les groupes sont des rôles comme les autres auxquels on ne donne généralement pas le droit de connexion (mais c’est tout à fait possible).

-- Ajouter un utilisateur à un rôle groupe : 
GRANT mon_role_groupe TO mon_role_1, mon_role_2 ;


-- Retirer un utilisateur d'un rôle groupe :
REVOKE mon_role_groupe FROM mon_role_1 ;


-- Prendre la place d'un rôle groupe auxquel on appartient pendant la session courante
SET ROLE nom_role_groupe
;

Gérer les droits

Droits par défaut

Il s’agit des droits qui vont s’appliquer sur tout objet créé après définition de ces droits par défaut.

Pour les nouvelles tables

ALTER DEFAULT PRIVILEGES
FOR USER "mon_user_1"            -- On indique l'utilisateur (ou le rôle avec "ROLE") qui va créer les objets (et donc qui attribuera les droits à ce moment là)
IN SCHEMA
	"mon_schema"
GRANT
	SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ALL
                                 -- Il faut choisir une ou plusieurs option (séparées par des virgules)
ON 
	TABLES
TO
	"mon_user_2"             -- On indique les utilisateurs (ou rôles) qui recevront les droits lors de la création d'un objet par user_1
;

Pour les nouvelles séquences

ALTER DEFAULT PRIVILEGES
FOR USER "mon_user_1"
IN SCHEMA
	"mon_schema"
GRANT
	USAGE | SELECT | UPDATE | ALL
ON 
	SEQUENCES
TO
	"mon_user_2"
;

Pour les nouvelles fonctions

ALTER DEFAULT PRIVILEGES
FOR USER "mon_user_1"
IN SCHEMA
	"mon_schema"
GRANT
	EXECUTE | ALL
ON 
	FUNCTIONS
TO
	"mon_user_2"
;

Voir les droits par défaut

SELECT 
	nspname as "Schéma",
	CASE defaclobjtype 
		WHEN 'r' THEN 'Table'::text
		WHEN 'S' THEN 'Sequences'::text
		WHEN 'f' THEN 'Fonction'::text
		ELSE defaclobjtype
	END as "Type",
	defaclacl as "Droit"
FROM
	pg_default_acl a
JOIN
	pg_namespace b 
	ON a.defaclnamespace = b.oid
;

Droits actuels

Sur une table

GRANT
	SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | ALL
ON 
	TABLE mon_schema.ma_table | ALL TABLES IN SCHEMA "mon_schema"
TO
	"mon_user"
;

Sur une séquence

GRANT
	USAGE | SELECT | UPDATE | ALL
ON 
	SEQUENCE mon_schema.ma_sequence | ALL SEQUENCES IN SCHEMA "mon_schema"
TO
	"mon_user"
;

Sur une fonction

GRANT
	EXECUTE | ALL
ON 
	FUNCTION mon_schema.ma_fonction | ALL FUNCTIONS IN SCHEMA "mon_schema"
TO
	"mon_user"
;

Sur un schéma

GRANT
	CREATE | USAGE | ALL    -- CREATE permet de créer des objets dans le schéma
ON 
	SCHEMA mon_schema
TO
	"mon_user"
;

Sur une base

GRANT
	CREATE | CONNECT | TEMPORARY | TEMP | ALL    -- CREATE permet de créer des schéma
ON 
	DATABASE ma_base
TO
	"mon_user"
;

Retirer des droits

REVOKE
	XXX | ALL PRIVILEGES      -- Privilège que l'on souhaite retirer
ON 
	XXX                       -- Objet sur lequel porte le privilège (DATABASE, SCHEMA, TABLE)
FROM
	"mon_user"
;

Les concepts clés

Les valeurs par défaut

Vous pouvez définir une valeur par défaut pour vos colonnes. Celle-ci sera donc utilisée si aucune valeur n’est renseignée lors de l’enregistrement de la ligne.

Les séquences

Les séquences sont des objets auto-incrémentés qui sont utilisés avec les fonctions suivantes : nextval(), currval() et setval().

On peut ainsi définir la valeur initiale, la valeur maximale et le pas d’incrémentation.

Une séquence s’utilise généralement comme valeur par défaut d’une colonne. Il est également possible d’utiliser une seule et même séquence pour plusieurs colonnes.

Les contraintes

PostgreSQL intègre un système de contraintes que l’on peut appliquer (ou pas) sur chaque colonne afin de restreindre les valeurs que l’on peut y mettre.

Contrainte de vérification

La valeur doit satisfaire à une expression renvoyant une valeur booléenne (par exemple ma_colonne > 2) .

-- Lors de la définition de la table V1
CREATE TABLE ma_table (
	colonne_1 text CHECK (colonne_1 > 1),
	colonne_2 text,
	colonne_3 text
)
;

-- Lors de la définition de la table V2
CREATE TABLE ma_table (
	colonne_1 integer,
	colonne_2 integer,
	colonne_3 text,

	CONSTRAINT nom_contrainte CHECK (colonne_1 > 1)
)
;

-- Via une requête spécifique
ALTER TABLE ma_table 
	ADD CONSTRAINT nom_contrainte CHECK (colonne_1 > 1)
;

-- Il est possible d'utiliser des combinaisons
ALTER TABLE ma_table 
	ADD CONSTRAINT nom_contrainte CHECK (colonne_1 > 1 AND colonne_2 > colonne_1)
;

Contrainte d’unicité

La valeur doit être unique (peut fonctionner avec une combinaison de colonne).

-- Lors de la définition de la table V1
CREATE TABLE ma_table (
	colonne_1 text UNIQUE,
	colonne_2 text,
	colonne_3 text
)
;

-- Lors de la définition de la table V2
CREATE TABLE ma_table (
	colonne_1 text,
	colonne_2 text,
	colonne_3 text,

	CONSTRAINT nom_contrainte UNIQUE (colonne_1)
)
;

-- Via une requête spécifique
ALTER TABLE ma_table 
	ADD CONSTRAINT nom_contrainte UNIQUE (colonne_1)
;

-- Il est possible d'utiliser deux colonnes comme clé unique
ALTER TABLE ma_table 
	ADD CONSTRAINT nom_contrainte UNIQUE (colonne_1, colonne_2)
;

Contrainte de nullité/non-nullité

La valeur ne doit pas être nulle.

-- Lors de la définition de la table
CREATE TABLE ma_table (
	colonne_1 text NOT NULL,
	colonne_2 text,
	colonne_3 text
)
;

Par défaut, les colonnes possèdent la contrainte inverse : NULL. Elles peuvent recevoir des valeurs nulles.

Clé primaire

Le but de cette contrainte est d’indiquer que la colonne répond aux contraintes de non nullité et d’unicité et peut donc être utilisée comme identifiant.

Pour créer cette contrainte :

-- Lors de la définition de la table V1
CREATE TABLE ma_table (
	colonne_1 text PRIMARY_KEY,
	colonne_2 text,
	colonne_3 text
)
;

-- Lors de la définition de la table V2
CREATE TABLE ma_table (
	colonne_1 text,
	colonne_2 text,
	colonne_3 text,

	CONSTRAINT nom_clé_primaire PRIMARY KEY (colonne_1)
)
;

-- Via une requête spécifique
ALTER TABLE ma_table 
	ADD CONSTRAINT nom_cle_primaire PRIMARY KEY (colonne_1)
;

-- Il est possible d'utiliser deux colonnes comme clé unique
ALTER TABLE ma_table 
	ADD CONSTRAINT nom_cle_primaire PRIMARY KEY (colonne_1, colonne_2)
;

Clé étrangère

Le but de cette contrainte est d’imposer que la valeur soit présente dans une table de référence.

Ces valeurs « mères » sont stockées dans une table qui sera référencée dans la définition de la clé étrangère.

Cette définition précise également quoi faire avec les données « filles » lorsqu’une des valeurs mère est mise à jour.

  • ON UPDATE (à la mise à jour de la mère) :
    • CASCADE : on met à jour toutes les valeurs filles pour avoir la même valeur.
    • RESTRICT : on interdit la mise à jour de la mère tant qu’il existe des filles.
    • NO ACTION (défaut) : on ne fait rien : les filles deviennent alors invalides.
  • ON DELETE (à la suppression de la mère) :
    • CASCADE : on supprime toutes les lignes contenant les valeurs filles.
    • RESTRICT : on interdit la suppression de la mère tant qu’il existe des filles
    • NO ACTION (défaut) : on ne fait rien : les filles deviennent alors invalides.

Pour créer cette contrainte :

-- Lors de la définition de la table portant les valeurs filles
CREATE TABLE ma_table_enfant (
	colonne_1 text,
	colonne_2 text REFERENCES schema.table_reference(colonne_liste_valeur) ON UPDATE CASCADE ON DELETE RESTRICT,
	colonne_3 text
)
;

-- Lors de la définition de la table portant les valeurs filles
CREATE TABLE ma_table_enfant (
	colonne_1 text,
	colonne_2 text,
	colonne_3 text,

	CONSTRAINT nom_cle_etrangere FOREIGN KEY (colonne_1) REFERENCES schema.table_reference(colonne_liste_valeur) ON UPDATE CASCADE ON DELETE RESTRICT
)
;

-- Via une requête spécifique
ALTER TABLE ma_table_enfant 
	ADD CONSTRAINT nom_cle_etrangere FOREIGN KEY (colonne_1) REFERENCES schema.table_reference(colonne_liste_valeur) ON UPDATE CASCADE ON DELETE RESTRICT
;

Les id et oids

Il est courant (mais pas obligatoire) d’utiliser une colonne dédiée à l’identification de chaque objet. Couramment appelée « id », cette colonne doit remplir des conditions d’unicité et de non nullité. Pour cela, n’hésitez pas à utiliser les contraintes. Vous pouvez utiliser le type de donnée « serial » qui s’auto-incrémente et respecte automatiquement les 2 contraintes mentionnées.

PostgreSQL intègre un système d’identifiant automatique : l’OID. Cependant, il n’est pas adapté aux tables de très grande taille car il est limité par une valeur maximale.

Vous pouvez tout de même utiliser cette colonne automatiquement créée et nommée oid.

Les index

Il faut savoir que PostgreSQL stocke l’information telle qu’elle vient. Si vous ajoutez la valeur « jean » puis « paul » puis « jacques », PostgreSQL les stockera dans cet ordre, sans tri. Lorsque vous ferez une requête pour lui demander de ressortir l’enregistrement correspondant à « jacques », le serveur va parcourir toutes les lignes afin de trouver votre information. Un index permet donc de trier les données et donc d’accélérer PostgreSQL. Retenez les deux types suivants :

  • Les index BTREE : pour les données alphanumériques.
  • Les index GIST : pour les données géométriques.

La suppression en cascade

Ce type de suppression élimine la donnée mais va en plus détruire celles qui lui sont liées.

C’est, par exemple, très pratique dans le cas d’utilisation des clés étrangères. En effet, les clés étrangères lient les données d’une table de référence à celles d’une autre. Il devient alors impossible de supprimer une donnée « référence » car cela briserait la contrainte de clé étrangère. Une suppression en cascade va permettre de supprimer la donnée de la table de référence mais également les lignes liées dans les autres tables.

Ce type de suppression est donc pratique mais dangereux.

Les rôles, les groupes et les users

Dans PostgreSQL, l’accès aux données et à la gestion de la BDD est géré grâce aux rôles.

Qu’est-ce qu’un rôle ? Il s’agit tout simplement d’une entité qui peux recevoir des droits. Des droits pour accéder aux données, des droits pour modifier la données mais également des droits pour gérer la BDD.

Vous avez tout de suite un mot qui vous vient à la bouche : « un utilisateur ». Si je parle « d’entité » c’est que le concept de rôle est un peu plus puissant qu’un simple « utilisateur ». En effet, un rôle c’est à la fois un utilisateur et à la fois un groupe d’utilisateur. Un rôle peut être l’un ou l’autre ou les deux.

On parlera alors de :

  • user pour un rôle qui possède le droit de se connecter à la BDD.
  • role ou role group pour les rôles dont la fonction est de regrouper plusieurs user.

Attention, il est tout à fait possible d’avoir un role (ou role group) qui possède le droit de connexion. Il est alors à la fois user et role group.
C’est là qu’est toute l’ambigüité des rôles : qu’ils aient la fonction de regrouper d’autres rôles ou de se connecter, se sont les mêmes objets qui possèdent simplement des options différentes.

Pour créer un rôle voici la requête :

CREATE ROLE nom_utilisateur
;

Pour créer un rôle user :

-- Il suffit d'ajouter l'option "LOGIN" qui permet de se connecter à la base avec le rôle
CREATE ROLE nom_utilisateur LOGIN
;
-- Une autre syntaxe le fait automatiquement mais l'objet créé est bien un rôle
CREATE USER nom_utilisateur
;

Pour rattacher un rôle à un autre :

-- On utilise GRANT : on accorde l'accès au groupe à l'utilisateur
GRANT mon_role_groupe TO mon_role_user_1
;
-- Il est possible de rattacher plusieurs rôle d'un coup
GRANT mon_role_groupe TO mon_role_user_2,mon_role_user_3
;
-- Il est possible de rattacher un groupe à un autre
GRANT mon_role_super_groupe TO mon_role_groupe
;

Pour détacher un rôle d’un autre :

REVOKE mon_role_groupe FROM mon_role_1
;

Ainsi, il est possible d’avoir :

  • Un rôle auquel sont rattaché plusieurs autres rôles (principe du groupe).
  • Un rôle rattaché à plusieurs rôles en même temps (principe d’un utilisateur dans plusieurs groupes)
  • Un rôle rattaché à un rôle lui-même rattaché à un autre rôle (principe d’un groupe dans un groupe).

Il est donc tout à fait possible de créer un rôle pour accéder à votre BDD puis de lui rattachez d’autres rôles : votre premier rôle aura alors la fonction de groupe (mais pourra toujours être utilisé pour se connecter).

En règle général, il est recommander de distinguer les rôles de connexion des rôles groupes (pour mieux s’y retrouver).
Les premiers servent à accéder à la BDD, les seconds servent à accorder les droits.

Le rôle postgres est créé lors de l’initialisation de la base, il s’agit d’un user qu’il vaut mieux ne pas utiliser comme role group car il possède tous les droits.

Les droits

Dans PostgreSQL, il faut autoriser les utilisateurs à accéder au données. Pour cela, on accorde ou on révoque le droit de lecture/écriture/mise à jour… sur les différents objets de la base au différents rôles.

Un rôle n’a accès qu’aux ressources :

  • Qui lui appartiennent.
  • Qui sont publiques : dont le droit d’accès à été accordé au rôle public.
  • Qui lui sont permises directement : dont le droit d’accès lui a été accordé directement
  • Qui lui sont permises indirectement : dont le droit d’accès lui est hérité via l’appartenance à un rôle auquel un droit d’accès a été accordé.

Vous trouverez plus haut les différents droits qu’il est possible d’accorder aux rôles. Vous pouvez accorder des droits sur les ressources existantes mais également des droits « par défaut » qui seront attribués lors de la création des différents objets.

Search path

Votre BDD est structurée en « schéma ». Se sont des sortes de répertoires dans lesquels se trouvent vos objets.

Pour créer ou lire un objet, vous devez spécifier dans quel schéma celui se trouve(ra). Si aucun schéma n’est spécifié, l’objet sera d’abord cherché dans le schéma qui possède le même nom que l’utilisateur avec lequel vous êtes connecté puis s’il ne le trouve pas, dans le schéma public.

Ce comportement est dû à la variable search_path qui précise dans quel schéma sont placés les objets de la requête lancée si leur schéma d’appartenance ne sont pas spécifiés.

Pour visualiser le contenu de la variable search_path utilisez la requête suivante :

SHOW search_path
;
-- Le résultat :
search_path   
-----------------
 "$user", public

Comme il est très rare que vous possédiez un schéma portant le même nom que celui d’un de vos utilisateurs, la plupart du temps, les recherches d’objets (ou les créations) se feront dans le schéma public.

Vous pouvez modifier ce comportement et spécifier d’autres schémas en utilisant la requête suivante :

SET search_path TO mon_schema_de_recherche_1,mon_schema_de_recherche_2
;

Attention, ceci n’est valable que pour la session en cours mais cela vous permet de créer des scripts sans avoir besoin de préfixer vos objets.

Vous avez dit public ?

Un rôle virtuel nommé public est créé lors de l’initialisation de toute BDD.

Ce rôle est spécial car tout rôle créé dans la base appartiendra automatiquement à ce rôle public (il s’agit donc d’un rôle groupe si vous avez bien suivit).

Par défaut, le schéma public est créé lors de l’initialisation d’une BDD. Ce dernier appartient au rôle public. Ainsi, grâce au principe d’héritage des droits, tout rôle de la BDD peut utiliser le schéma public.


Maintenant que vous savez créer des objets, concentrons nous sur les données

Sommaire général

Voici le sommaire général du cours :


Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostgreSQLProgrammationSQL tuto

50%