====== Permissions sous postgresql ======
 --- //[[f.raynaud@arles-linux.org|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 [[https://docs.postgresql.fr/8.2/client-authentication.html#auth-pg-hba-conf|pg_hba.conf]]

[[http://www.i3s.unice.fr/~rueher/Cours/BD/DocPostgresSQL9-5_HTML/client-authentication.html#auth-pg-hba-conf|source sur pg_pha.conf]]

Avant toute modification, il est conseillé de recopier le fichier

<code>
sudo cp /etc/postgresql/11/main/pg_hba.conf /etc/postgresql/11/main/pg_hba.confold
</code>


====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

<code>
# Database administrative login by Unix domain socket
local   all             postgres                       md5
# TYPE  DATABASE        USER            ADDRESS        METHOD
</code>

<note>
  * 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
</note>

<note>
Sur les méthodes de connexion :
argument METHOD : 
  * 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é 
</note>

lors d'une tentative de connexion postgres

<code>
   
# 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#
</code>

Il est proposé de mettre la méthode trust et redémarrer apache :

<code>
# Database administrative login by Unix domain socket
local   all             postgres                       trust
# TYPE  DATABASE        USER            ADDRESS        METHOD

service apache2 restart

</code>


===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.
<code>
psql postgres
   le role deb n'existe pas
</code>
Il faut donc créer le role :
<code>
createuser -P deb -U postgres
    Saisir le mot de passe pour le nouveau rôle : deb
    Le saisir de nouveau :  deb
</code>

Il est possible de se connecter à la base postgres avec le rôle "deb" .

<code>
psql postgres
postgres=#
</code>


Par défaut, la méthode de connexion pour les autres utilisateurs est peer en local

<code>                      
# TYPE  DATABASE        USER            ADDRESS        METHOD
local   all             all                            peer     
</code>

Il est proposé de mettre la méthode md5

<code>                      
# TYPE  DATABASE        USER            ADDRESS        METHOD
local   all             all                            md5     
</code>

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 [[https://www.php.net/manual/fr/function.pg-connect.php|pg_connect]]

<code>
<?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);
?> 
</code>

<note>
Si il y a un message pg_connect, commande inconnue, c est que php_pgsql n'est pas installé ou il n'est pas pris en compte. Dans ce dernier cas redémarrer apache, dans l'autre voir [[install_debian|install_debian]]
</note>

<note>
Si il y a un message "erreur de connexion", c est qu'il y a un problème de droit (accès pg_hba.conf) ou de paramètre (voir le port si ce n est pas le 5432) ou d'acces à la base postgres 
</note>

===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. 
 
<code>
psql -U postgres
   alter role deb with superuser;
</code>

Nous allons autoriser maintenant le super utilisateur à créer des rôles et à créer des bases :

<code>
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

</code>


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.

<code>
# Database administrative login by Unix domain socket
local   all             postgres                       md5
</code>

====accès avec une ip externe (contrôle IP)====
{{ :connexion_pgadmin.png?400|}}
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 

<code>
# IPv4 local connections:
host    all             all             192.168.0.0/16          md5
</code>

Mettre * (ou l'adresseIP autorisé) à la place de localhost dans la directive listen_addresses  de /etc/postgresql/11/main/postgresql.conf

<code>
listen_addresses = '*'
</code>

redémarrer postgresql

<code>
service postgresql restart
</code>

<note>
  * 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 
</note>



=====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.

[[https://sqlpro.developpez.com/cours/sqlaz/dcl/#L2.1|sources pour les privilèges standard SQL2]]

Création d'un utilisateur
<code>
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
</code>


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

<code>
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;
</code>

Changer l'utilisateur et le mot de passe dans l'application openassociation


<code>
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
...
</code>

Affichage des privilèges sous postgres. voir [[psql_commande|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:

<code>
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
</code>

Par contre, il peut continuer à créer des tables dans le schéma public d'openstock et dans les autres schéma public.

<note>
Le super utilisateur PUBLIC ne vise personne en particulier ni en général. On ne peut donc retirer un privilège particulier à un utilisateur donné même si l'on a attribué des privilèges à "PUBLIC".
</note>

Il est proposé d'enlever la création de table à l'utilisateur PUBLIC sur le schéma public :  

<code>
REVOKE CREATE ON SCHEMA public FROM PUBLIC;
</code>

L'utilisateur deb_asso ne peut plus créer de table dans le schéma public.

Le super utilisateur "deb" peut continuer à le faire

[[structure_postgres|la structure de postgres]]