Table des matières
Permissions sous postgresql
— François Raynaud 2020/06/08 15:50
Ces commandes fonctionnent avec la version postgresql 11.
Nous allons modifier l'installation de postgresql accéder en local avec psql et apache. Ensuite nous essaierons une connexion distante. Enfin nous formaliserons un utilisateur limité pour accéder à l'application openAssociation.
Nous allons travailler essentiellement sur le fichier pg_hba.conf
Avant toute modification, il est conseillé de recopier le fichier
sudo cp /etc/postgresql/11/main/pg_hba.conf /etc/postgresql/11/main/pg_hba.confold
Accès "local" (psql et apache)
Accès de l'utilisateur postgres en local
Par défaut la méthode de connexion à l'utilisateur postgres en local est md5
# Database administrative login by Unix domain socket local all postgres md5 # TYPE DATABASE USER ADDRESS METHOD
- TYPE : local : connexion du domaine unix
- DATABASE : all : toutes les bases de données
- USER : postgres
- ADRESS : vide car ne concerne que les adresses externes
- trust : autorise la connexion sans condition
- peer : recupère le nom d utilisateur linux et vérifie que cela correspond avec le SGBD
- md5 : demande au client un mot de passe chiffré
lors d'une tentative de connexion postgres
# method=md5 psql -U postgres authentification par mot de passe échouée pour l'utilisateur postgres # method=peer l utilisateur linux doit être postgres su postgres psql postgres# authentification peer ok # method trust -> ok psql -U postgres postgres#
Il est proposé de mettre la méthode trust et redémarrer apache :
# Database administrative login by Unix domain socket local all postgres trust # TYPE DATABASE USER ADDRESS METHOD service apache2 restart
accès de l'utilisateur linux
Il est proposé de se connecter à psql avec l'utilisateur deb qui est notre utilisateur système crée lors de la mise en place de debian.
psql postgres le role deb n'existe pas
Il faut donc créer le role :
createuser -P deb -U postgres
Saisir le mot de passe pour le nouveau rôle : deb
Le saisir de nouveau : deb
Il est possible de se connecter à la base postgres avec le rôle “deb” .
psql postgres postgres=#
Par défaut, la méthode de connexion pour les autres utilisateurs est peer en local
# TYPE DATABASE USER ADDRESS METHOD local all all peer
Il est proposé de mettre la méthode md5
# TYPE DATABASE USER ADDRESS METHOD local all all md5
Le mot de passe sera demandé en connexion à psql
Accès avec Apache/php
L'objectif est de pouvoir connecter apache/php avec la base :
Le script ci dessous permet de tester la connexion php_pgsql avec la commande php pg_connect
<?php
$con=pg_connect("host=localhost port=5432 dbname=postgres user=deb password=deb");
if(!$con){
echo "erreur de connexion";
exit;
}else
echo "connexion ok";
pg_close($con);
?>
Accès avec Apache/php à l'application openAssociation
Il faut que l'utilisateur postgres deb ait les privilèges pour sur la base “openstock” dont il n'est pas propriétaire (dans notre cas c'est l'utilisateur postgres). Il lui faut être super utilisateur et seul l'utilisateur postgres qui est super utilisateur peut donner un role de super utilisateur.
psql -U postgres alter role deb with superuser;
Nous allons autoriser maintenant le super utilisateur à créer des rôles et à créer des bases :
psql -U postgres postgres=# alter role deb with createdb; alter role deb with createrole; select rolname,rolsuper,rolcreaterole,rolcreatedb,rolcanlogin from pg_roles where rolcanlogin is true ; rolname | rolsuper | rolcreaterole | rolcreatedb | rolcanlogin ----------+----------+---------------+-------------+------------- postgres | t | t | t | t deb | t | t | t | t
Il peut être intéressant de remettre postgres à md5 dans le fichier pg_hba.conf vu que nous avons l'utilisateur deb qui a les mêmes privilèges.
# Database administrative login by Unix domain socket local all postgres md5
accès avec une ip externe (contrôle IP)
Il faut autoriser des IP externes dans les cas suivants (tests avec un raspberry et postgres 9.4) :
- le serveur web apache est sur un autre serveur que celui de postgres
- les développeurs accèdent via un client comme pgadmin sur le serveur postgres (image ci contre)
Rajouter la ligne suivante dans /etc/postgresql/9.4/main/pg_hba.conf pour que les adresses en 192.168 se connectent à la base
# IPv4 local connections: host all all 192.168.0.0/16 md5
Mettre * (ou l'adresseIP autorisé) à la place de localhost dans la directive listen_addresses de /etc/postgresql/11/main/postgresql.conf
listen_addresses = '*'
redémarrer postgresql
service postgresql restart
- TYPE host : intercepte les tentatives de connexion TCPIP
- TYPE hostnossl : intercepte les tentatives de connexion TCPIP qui ne sont pas ssl
- TYPE hostssl : intercepte les tentatives de connexion TCPIP qui sont ssl
- ADRESS
Accès limité au connecteur à postgresql pour l'application openAssociation
L'objectif est de limiter les privilèges du connecteur strictement au besoin de l'application openAssociation.
sources pour les privilèges standard SQL2
Création d'un utilisateur
createuser -P deb_asso Saisir le mot de passe pour le nouveau rôle : deb_asso Le saisir de nouveau : deb_asso Mot de passe : deb
Il faut donner les privilèges sur le schéma qui appartient à l'utilisateur postgres à l'utilisateur deb_asso (dans notre cas).
- il doit pouvoir utiliser le schéma openstock_association
- il doit pouvoir utiliser les commandes select,update,insert et delete sur les tables
- il doit pouvoir utiliser les commandes select et update sur les séquences
psql openstock grant usage on SCHEMA openstock_association to deb_asso; grant select,insert,update,delete on all tables in schema openstock_association to deb_asso; grant select,update on all sequences in schema openstock_association to deb_asso;
Changer l'utilisateur et le mot de passe dans l'application openassociation
root@deb:~# nano /var/www/html/openassociation_1.0.1/dyn/database.inc.php
...
// gestion association
$conn[1] = array(
"Gestion association", // Titre
"pgsql", // Type de base
"pgsql", // Type de base
"deb_asso", // Login
"deb_asso", // Mot de passe
...
Affichage des privilèges sous postgres. voir raccourci commandes en ligne
On peut enlever des permissions avec la commande REVOKE.
deb_asso ne peut pas créer de schéma, créer une table ou changer de propriétaire une table qui ne lui appartient pas dans le schéma openstock_association:
openstock=> create schema temp; ERREUR: droit refusé pour la base de données openstock openstock=> create table openstock_association.temp(id integer,libelle varchar(20)); ERREUR: droit refusé pour le schéma openstock_association openstock=> alter table openstock_association.article owner to deb_asso ; ERREUR: doit être le propriétaire de la table article
Par contre, il peut continuer à créer des tables dans le schéma public d'openstock et dans les autres schéma public.
Il est proposé d'enlever la création de table à l'utilisateur PUBLIC sur le schéma public :
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
L'utilisateur deb_asso ne peut plus créer de table dans le schéma public.
Le super utilisateur “deb” peut continuer à le faire