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 fillesNO 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 :
userpour un rôle qui possède le droit de se connecter à la BDD.roleourole grouppour les rôles dont la fonction est de regrouper plusieursuser.
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
