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

source sur pg_pha.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
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é

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

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) :

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

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.

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

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

la structure de postgres