Créer automatiquement un DSN ODBC vers PostgreSQL avec Access

Notre besoin : nous avons une base de données Access avec des tables liées vers PostgreSQL et nous ne voulons pas devoir déployer un script de création de DSN ODBC sur les machines des utilisateurs avant de déployer cette BD.

Solution :  intégrer la fonctionnalité de création automatique de DSN dans vos base de données.

Comment :

• Créer le DSN

Étape 1 : Ajoutez un module à votre BD Access.

Étape 2: Ajouter les constantes nécessaires.

1. Constante avec le nom de la BD :

Const nom_base_donnees = "ma_base"

2. Constantes techniques du serveur
Const nom_serveur = "nom_du_serveur_postgresql" 'ou IP !
Const port_serveur = "5432" 'Valeur par défaut

3. Constante du nom du DSN qui sera créé.
Const nom_dsn = "nom_du_dsn_odbc"

4. Constantes concaténant les autres 
Const chaine_connexion_ODBC = _
"Description=" & nom_dsn & vbCr & _
"DATABASE=" & nom_base_donnees & vbCr & _
"SERVER=" & nom_serveur & vbCr & _
"PORT=" & port_serveur

5. Constante avec le nom du pilote OBDC PostgreSQL.
Vu que la version 32bits d'office n'utilise pas le même pilote que la version 64bits, nous devons en tenir compte

#If Win64 Then
Const nom_pilote_odbc = "PostgreSQL Unicode(x64)"
#Else
Const nom_pilote_odbc = "PostgreSQL Unicode"
#End If

6. Ajouter la méthode de création du DSN
Public Sub CreerLeDSNPostgreSQL()

    Dim modeSilencieux
    modeSilencieux = true   

    DBEngine.RegisterDatabase nom_dsn , _ 
        nom_pilote_odbc, _ 
        modeSilencieux , _ 
        chaine_connexion_ODBC 

End Sub

Étape 3 : planifier l'exécution de la méthode CreerLeDSNPostgreSQL à l'ouverture de la BD.
Solution la plus simple, ajouter une macro dont la première étape est ExécuterCode avec le paramètre CreerLeDSNPostgreSQL().
Cette macro doit être enregistrée sous le nom AutoExec.

Parfait !
Mais maintenant, il faut s'assurer que toutes les tables liées soient bien connectées à ce nouveau DSN

• Remplacer les informations de connexion de toutes les tables liées

Étape 1 : ajouter les autres constantes
Pour une raison mystique, la chaine d'une connexion d'une table est différente d'une chaine de connexion utilisée pour la création d'un ODBC... nous devons donc redéfinir une constante pour la nouvelle chaine.
Attention, cette constante doit être ajoutée avant la méthode CreerLeDSNPostgreSQL.


Const chaine_connexion_table = "ODBC" & _
";DSN=" & nom_dsn & _
";DATABASE=" & nom_base_donnees & _
";SERVER=" & nom_serveur & _
";PORT=" & port_serveur & ";" & _
"MaxVarcharSize=255;TextAsLongVarchar=0;" 'voir Access et 'Text' 

Étape 2 : Ajouter les méthodes suivantes dans votre module


Public Sub MettreDSNAjourDansTablesLiees()
    Dim indiceTable As Integer
    
    For indiceTable = 0 To CurrentDb.TableDefs.Count - 1
    
        If Not EstUneTableSysteme(CurrentDb.TableDefs(indiceTable).Name) Then
        
            MettreChaineConnexionAJour CurrentDb.TableDefs(indiceTable)
        
        End If
        
    Next
End Sub

Private Sub MettreChaineConnexionAJour(objetTableDefinition As Variant)

    Dim chaineConnexion As String
        
    chaineConnexion = objetTableDefinition.Connect
    
    If ChaineRepresenteTableLieeODBC(chaineConnexion) Then
        
        chaineConnexion = chaine_connexion_table
        
        objetTableDefinition.Connect = chaineConnexion
        
        objetTableDefinition.RefreshLink
           
    End If
End Sub

Private Function ChaineRepresenteTableLieeODBC(chaineConnexion As String)
    ChaineRepresenteTableLieeODBC = (Left(chaineConnexion, 4) = "ODBC")
End Function

Private Function EstUneTableSysteme(nomTable As Variant)
    EstUneTableSysteme = (Left(nomTable, 4) = "MSys") Or InStr(nomTable, "~") <> 0
End Function

Étape 3 : ajouter une étape ExécuterCode avec le paramètre MettreDSNAjourDansTablesLiees dans la macro AutoExec.

PostgreSQL, Access et le champs TEXT

Lorsque vous connectez une base Access à PostgreSQL via le driver ODBC, les paramètres par défaut sont de considérer les champs de type TEXT de PostgreSQL comme des LongVarChar, des champs MÉMO dans le jargon Access.

Access ne permet pas de faire des jointures sur des champs MEMO, de plus si votre table ne comprend qu'un identifiant et un champ MEMO, Access perd les pédales lorsque vous ajoutez des données et affiche le célèbre '#supprimé' ou '#deleted' dans les champs.

Il existe plusieurs solutions pour éviter ce problème :

1 - décocher l'option 'Text as LongVarChar'. 

Avec cette option décochée, Access fonctionne normalement mais par contre, ne permet l'encodage que de 255 caractères. 

Si jamais, essayer de modifier la valeur 'Max Varchar' de la section Miscellaneous avec une valeur supérieure à 255 ne fonctionne pas. Access considère un champ texte de plus de 255 comme un champ MEMO. (Croyez-moi, j'ai essayé !)

2 - ne pas utiliser de champ TEXT, mais utiliser un champ de type VARCHAR(x) avec x<255

3 - Faire un Me.Refresh dans l'événement Form_AfterUpdate.

Fonctionne très bien, mais vous perdrez l'élément sélectionné. Tolérable pour quelques éléments, inacceptables pour des grandes listes

4 - plusieurs autres solutions broche à foin que je déconseille...

Créer des utilisateurs SSPI

Pour créer des utilisateurs pouvant se connecter en sécurité intégrée Windows, rien de plus simple, il vous suffit de créer des rôles de type login ayant le même nom que vos identifiant windows.

Exemple :
Login windows = PDC2008.corp\GLAGAFFE ou glagaffe@pdc2008.corp
Login postgresql = glagaffe

CREATE ROLE glagaffe LOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

Il faut ensuite adapter le fichier PG_HBA.CONF pour spécifier que l'usager GLAGAFFE se connecte avec la sécurité windows :
# TYPE  BaseDonnées   Utilisateur  Adresse    Méthode

host    all           glagaffe     0.0.0.0/0  sspi

! Attention, le fichier pg_hba.conf est sensible à la casse !

Dans notre situation, tout le monde se connecte en sspi, sauf les exceptions que nous avons placés dans un role 'groupe'. (Admin, développeurs, comptes techniques)

Dans ce cas spécifique, le pg_hba.conf est :

# TYPE  BaseDonnées   Utilisateur  Adresse    Méthode

host    all           +groupe_md5  0.0.0.0/0  md5

host    all           all          0.0.0.0/0  sspi

Si vous voulez éviter de créer un compte PostgreSQL pour chaque utilisateur, vous pouvez modifier le fichier pg_ident.conf qui vous permet de faire une association entre un compte du domaine et un compte PostgreSQL. Cette technique est très puissante ... mais requière un redémarrage de la machine à chaque modification du fichier pg_ident.conf.

Plus de détails : Correspondances d'utilisateurs (postgresql.fr)

SetSPN pour intégrer un serveur PostgreSQL dans un domaine windows

Notre besoin : permettre de se connecter à PostgreSQL sans devoir retenir un mot de passe supplémentaire et probablement différent de notre mot de passe du domaine Windows.

Solution : Il est possible d'intégrer un serveur PostgreSQL au sein d'un domaine windows afin d'utiliser l'authentification SSPI.
Ceci permet d'utiliser des comptes de l'ActiveDirectory pour authentifier des utilisateurs sans devoir maintenir des comptes différents avec login/pwd dans le serveur PostgreSQL.

Comment :
Vu que le service PostgreSQL est configuré pour être exécuté avec le compte NETWORK SERVICE, (depuis la version 9.2), il est possible de directement l'intégrer avec la commande SetSPN.

L'instruction à utiliser est setspn -S POSTGRES/FQDN nomserveur

Si, par exemple, votre serveur s'appelle serveur1 et est membre du domaine pdc2008.corp.
L'instruction à utiliser est :
 setspn -S POSTGRES/serveur1.pdc2008.corp serveur1

Cette action doit être exécutée dans une fenêtre "En tant qu'administrateur".
Selon la configuration de votre domaine, elle risque de devoir être exécutée par un compte ayant les droits d'administration du domaine.

Et c'est tout !

Attention qu'avec cette solution, tous les services exécutés sur la machine avec le compte NETWORK SERVICE ont accès aux fichiers de PostgreSQL.
Si cela va à l'encontre de vos besoins en sécurité, veuillez suivre la deuxième démarche exposée sur le wiki de PostgreSQL : Configuring for single sign-on using SSPI on Windows.

À suivre : créer les utilisateurs et adapter pg_hba.conf