Interroger une base PostGreSQL en PowerShell

Bonjour à tous, aujourd’hui on va intéresser aux bases SQL et PowerShell. Et plus exactement comment interroger une base PostGreSQL en PowerShell. Pour cela on va devoir s’y connecter, faire une requête, lire nos résultats en PowerShell et clore la connexion.

Je choisit volontairement PostGreSQL car (j’ai eu besoin de le faire a boulot, déjà) il nécessite que l’on installe au préalable le driver ODBC pour se connecter à la base. En effet si vous utiliser un serveur MSSQL, les dépendances nécessaires sont (en général) déjà livrées avec Windows.

Pour obtenir ce pilote vous pouvez vous connecter sur le site de PostGreSQL ici, télécharger puis installer le fichier MSI. Vous pouvez vérifier que celui ci est bien déployé en allant dans le panneau de configuration->Outils d’administration->Sources de données ODBC (32 ou 64 bits). Les connecteurs PostgreSQL UNICODE et ANSI devrait être présent.

A partir de là, on peut démarrer le code de notre module PowerShell. La première étape consiste à établir la connexion à la base. Et pour cela j’ai développé la Cmd-Let Get-PSSQLDBConnexion ci dessous :

Function Get-PSSQLDBConnexion{
    param(
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()] [String] $Server,
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()] [String] $Port,
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()] [String] $DBName,
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()] [String] $Username,
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()] [SecureString] $Password
    )
    $DBConnectionString = "Driver={PostgreSQL UNICODE};Server=$Server;Port=$Port;Database=$DBName;Uid=$Username;Pwd=$(ConvertFrom-SecureString -SecureString $Password -AsPlainText);"
    Write-Log -Level 'DEBUG' -Message "connecting to DB."
    $DBConn = New-Object System.Data.Odbc.OdbcConnection;
    try {
        $DBConn.ConnectionString = $DBConnectionString ;
        $DBConn.Open();
    } catch {
        $ErrorMessage = $_.Exception.Message
        $ErrorType = $_.exception.GetType().fullname
        Write-Log -Level 'ERROR' -Message "Error in DB connection '$DBConnectionString' ; Error Details: $ErrorType - $ErrorMessage"
    }
    return $DBConn
}

Notez a passage que le module utilise la Cmd-Let Write-log du module Logging PowerShell disponible sur la PowerShellGallery. Module qui est donc un prérequis ici et que vous devrez spécifier en haut du module ou du script via la ligne suivante :

#Requires -Module logging

Une fois la connexion établie, vous pouvez simplement requêter votre base de donnée avec la Cmdlet « Get-SQLQuery » suivante :

function Get-SQLQuery{
    param(
    [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()][string] $SQLQuery,
    [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()] [System.Data.Odbc.OdbcConnection] $Connexion
    )
    try {
        $cmd = New-object System.Data.Odbc.OdbcCommand($SQLQuery, $Connexion)
        $ds = New-Object system.Data.DataSet 
        (New-Object system.Data.odbc.odbcDataAdapter($cmd)).fill($ds) | Out-Null
    } catch {
        $ErrorMessage = $_.Exception.Message
        $ErrorType = $_.exception.GetType().fullname
        Write-Log -Level 'ERROR' -Message "Error while Querying DB '$Connexion' with '$SQLQuery' ; Error Details: $ErrorType - $ErrorMessage"
    }
    return $ds.Tables[0]
 }

Voilà à partir de là on peut faire nos requêtes pour interroger une base PostGreSQL en PowerShell aussi simplement que ça :

$SQLQuerryString = "SELECT * FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';"

$SQLDataSet = Get-SQLQuery -Connexion $Connexion -SQLQuery $SQLQuerryString

Et voilà, à partir de là vous savez interroger une base PostGreSQL en PowerShell, les objets retournés par l’API sont du type DataSet. Ils présentent donc l’avantage d’être accessible en objet très simplement en PowerShell, ce qui est tout de même plus confortable qu’un retour SQL en mode « texte pur » (qui est aussi possible, je vous laisse chercher si vous avez envie de vous faire mal). Pas de besoin de s’attarder plus sur ce format.

Et à la fin du script, par bienveillance pour nos DBA préférés, on doit terminer la connexion proprement :

Function Close-PSSQLDBConnexion{
    param(
        [Parameter(Mandatory=$true)][ValidateNotNullOrEmpty()] [System.Data.Odbc.OdbcConnection] $Connexion
    )
 
    try {
        $Connexion.Close();
    } catch {
        $ErrorMessage = $_.Exception.Message
        $ErrorType = $_.exception.GetType().fullname
        Write-Log -Level 'ERROR' -Message "Error closing DB connection '$Connexion' ; Error Details: $ErrorType - $ErrorMessage"
    }
    return $DBConn
}

Voili-voilou, c’est tout pour aujourd’hui. Moi ça me fera une doc qui traine dans un coin quand j’aurais besoin de me connecter à une base PostGreSQL. J’espère que ça servira à l’un de vous également.

D’ici la prochaine : geekez bien !