PostgreSQL

Notes à propos du SGBD Postgres SQL.

POSTGRES, développé à l'Université de Californie Berkeley à partir de 1986, projet piloté initialement par Michael Stonebraker. 20 ans de maturité, une des bases de données objets relationnelle de référence de l'opensource. Fut sponsorisée par:

• DARPA: Defense Advanced Research Project Agency
• ARO: Army Research Office
• NSF: National Science Fondation

En 94, Andrew Yu et Jolly Chen implementent le SQL, POSTGRES devient Postgres95 sous licence Opensource. Reecriture en C ANSI, reduction du code 25% amélioration perf et maintenabilité. En 1996, adoption du nom PostgreSQL

1. Création du groupe de base de données = groupe de catalogues;
2. Initialiser un emplacement de stockage pour les bases de données avec la commande initdb
3. commande à invoquer sous l'utlisateur executant le serveur PostgreSQL
4. La commande se charge de créer la base systeme pg_database et le super utilisateur postgres

$ sudo mkdir /var/pgsql/data
$ sudo chown postgres /var/pgsql/data
$ su postgres
$ initdb -D /var/pgsql/data

#lancer le serveur en ligne de commande postgres -D /var/postgres/data > /var/log/postgresql.log 2>&1 &

#utiliser le script de gestion du serveur

pg_ctl start -l /var/log/postgresql.log

Dans PostgreSQL les droits d'acces aux bases de données sont gérés régis via le concept de roles. Les roles sous PostgreSQL correspondent a la notions d'utilisateur et de groues sur le systeme de fichiers. Les roles sont une logique interne de PostgreSQL; ils n'existent pas sur le systeme de fichier (pas d'utilisateurs ou groupes).

Un rôle possède des objet et peut affecter des droits d'utilisation sur ces objets a d'autres rôles. Les rôles définis sont communs au groupe de catalogue (groupe de base de données).

Lister les rôles existants:

\du

Les rôles sont enregistrés dans la table pg_roles, on peut également les lister via une requête SQL:

SELECT rolname FROM pg_roles;

Pour créer un nouveau rôle

CREATE ROLE mon_role;

Pour supprimer un rôle existant:

DROP ROLE mon_role;

# Par convention un rôle est propriétaire du groupe de catalogues créé, postgres (équivalent super utilisateur)

psql -U postgres

Les attributs des rôles doivent être explicitement définis lors de la création (CREATE) ou modification (ALTER)

Yoann est un rôle avec autorisation d’établir une connexion a la base de données (équivaut à un utilisateur)

CREATE ROLE yoann LOGIN
ALTER ROLE yoann SET PASSWORD TO 'toto';

# Principaux attributs des roles # SUPERUSER: le role a tous les droits creation d'un superutilisateur # CREATEDB: le role peut creer de nouvelles bases de données # LOGIN: le role peut initer une connexion au serveur de base de données. # CREATEROLE: le role peut creer d'autres roles # PASSWORD: authentification

Droits des roles SELECT, INSERT, UPDATE, DELETE, REFERENCES, TRIGGER, CREATE, CONNECT, TEMPORARY, EXECUTE, USAGE Les commandes GRANT et REVOKE permettent de donner ou de retirer les droits aux roles Tout objet créé a un proprietaire, par defaut le role qui execute la commande de creation. Le mot clé ALL désigne tous les droits et PUBLIC tous le roles utilisateurs.

#creation d'un groupe (role sans attribut connexion CREATE ROLE users;

# Affectation d'utilisateurs au groupe

GRANT users TO yoann, thomas, olivier, nicolas

Creer une base de données

CREATE DATABASE nom_base OWNER nom_role;

Une base de données est un ensemble nommé d'objets SQL. Chaque objet appartient à une et une seule base. Cependant certains catalogues tels que pg_database appartiennent à tout le groupe de base de données et sont accessible dans toutes les bases.

Hiérarchie: Serveur » bases » Schemas » objets

# Détruire une base de données avec:

DROP DATABASE nom_database

Quelques commandes élémentaires a exécuter depuis le client postgres psql après authentification.

On liste les base via la commande \list

\list

\connect database

\dt

Terminer l' exécution du client:

\q

• https://severalnines.com/database-blog/postgresql-privileges-user-management-what-you-should-know