Se connecter à PostgreSQL

En tant qu'administrateur BigQuery, vous pouvez créer une connexion permettant d'accéder aux données PostgreSQL. Cette connexion permet aux analystes de données d'interroger des données dans PostgreSQL.

Environnements et versions PostgreSQL compatibles

La fédération BigQuery est compatible avec les environnements de déploiement PostgreSQL suivants :

  • Instances PostgreSQL sur site connectées à Google Cloudvia Cloud VPN ou Cloud Interconnect
  • Instances PostgreSQL autogérées sur Compute Engine
  • Instances PostgreSQL hébergées dans le cloud accessibles via des adresses IP publiques ou des rattachements de réseau

La fédération BigQuery est compatible avec les versions de PostgreSQL suivantes :

  • PostgreSQL 16 (recommandé)
  • PostgreSQL 15

Avant de commencer

Activez les API nécessaires, configurez votre base de données PostgreSQL et attribuez des rôles IAM (Identity and Access Management) qui donnent aux utilisateurs les autorisations nécessaires pour effectuer chaque tâche de ce document.

  1. Activez l'API BigQuery Connection.

    Activer l'API

  2. Si vous prévoyez d'acheminer le trafic via des réseaux privés à l'aide de rattachements de réseau, activez également l'API Compute Engine.

    Activer l'API Compute Engine

  3. Dans votre base de données PostgreSQL, créez un utilisateur de base de données dédié disposant de droits SELECT sur les tables ou le schéma que vous souhaitez interroger :

    CREATE USER USER_NAME WITH PASSWORD 'PASSWORD';
GRANT USAGE ON SCHEMA SCHEMA_NAME TO USER_NAME;
GRANT SELECT ON ALL TABLES IN SCHEMA SCHEMA_NAME TO USER_NAME;

    Remplacez les éléments suivants :

    • USER_NAME : nom de l'utilisateur de base de données.
    • PASSWORD : mot de passe de l'utilisateur de la base de données.
    • SCHEMA_NAME : schéma des tables que vous souhaitez interroger.

  4. Configurez la connectivité réseau à votre instance de base de données PostgreSQL en effectuant l'une des opérations suivantes :

    • Si vous acheminez le trafic via des réseaux privés, créez un rattachement de réseau dans la même région que votre connexion BigQuery. L'appairage DNS n'est pas accepté. Connectez-vous à l'aide d'une adresse IP ou d'un nom d'hôte pouvant être résolu dans votre réseau VPC.
    • Si vous vous connectez via l'Internet public, ajoutez toutes les plages d'adresses IP Google aux règles de pare-feu de votre base de données PostgreSQL.

Rôles requis

Pour obtenir les autorisations nécessaires pour vous connecter à PostgreSQL, demandez à votre administrateur de vous accorder les rôles IAM suivants :

Pour en savoir plus sur l'attribution de rôles, consultez Gérer l'accès aux projets, aux dossiers et aux organisations.

Vous pouvez également obtenir les autorisations requises avec des rôles personnalisés ou d'autres rôles prédéfinis.

Connecter BigQuery à PostgreSQL

Vous pouvez connecter BigQuery à PostgreSQL dans la consoleGoogle Cloud ou l'outil de ligne de commande bq.

Console

  1. Dans la console Google Cloud , accédez à la page BigQuery.

    Accéder à BigQuery

  2. Dans le volet Explorateur, cliquez sur  Ajouter des données.

    La page Ajouter des données s'ouvre.

  3. Dans le volet Filtrer par, dans la section Type de source de données, sélectionnez Bases de données.

    Vous pouvez également saisir postgresql dans le champ Rechercher des sources de données.

  4. Dans la section Sources de données recommandées, cliquez sur PostgreSQL.

  5. Dans la section Accéder aux données externes en place, cliquez sur la fiche de solution Fédération BigQuery avec la description Requêtes fédérées vers PostgreSQL (et non sur les fiches Cloud SQL ou AlloyDB pour PostgreSQL).

    Le volet Source de données externe s'ouvre.

  6. Dans le volet Source de données externe, configurez les éléments suivants :

    • Dans le champ ID de connexion, saisissez un ID de connexion unique (par exemple, postgres_prod_conn).
    • Dans la section Type d'emplacement, sélectionnez Région, puis sélectionnez la région dans laquelle se trouve votre ensemble de données BigQuery. Les requêtes utilisant cette connexion doivent être exécutées à partir de cette région.
    • (Facultatif) Dans le champ Nom descriptif, saisissez un nom descriptif pour la connexion.
    • (Facultatif) Dans le champ Description, saisissez une description pour la ressource de connexion.
    • Dans la section Chiffrement, sélectionnez Google-managed encryption key ou Clé Cloud KMS pour utiliser une clé de chiffrement gérée par le client (CMEK).
    • Dans le champ Hôte:port, saisissez l'hôte et le port du serveur de base de données PostgreSQL au format HOST:PORT (par exemple, 10.10.2.2:5432).
    • Dans le champ Nom d'utilisateur, saisissez l'utilisateur de base de données que vous avez créé dans les conditions préalables.
    • Dans le champ Mot de passe, saisissez le mot de passe de l'utilisateur de la base de données.
    • Dans le champ Base de données, saisissez le nom de la base de données PostgreSQL.
    • (Facultatif) Dans le champ Rattachement de réseau, si vous utilisez la connectivité privée, saisissez le chemin d'accès à la ressource du rattachement de réseau au format projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME.
    • Dans la liste Mode TLS, sélectionnez l'un des modes TLS compatibles (par exemple, Chiffrer les données, et valider la CA et le nom d'hôte (recommandé)).
    • Dans la liste Autorité de certification (CA), sélectionnez Autorité de certification publique (Web PKI) ou sélectionnez une autorité de certification personnalisée, puis collez le certificat au format PEM.

  7. Cliquez sur Créer une connexion.

bq

Exécutez la commande bq mk avec l'option --connection :

bq mk --connection \
  --location=LOCATION \
  --project_id=PROJECT_ID \
  --connector_configuration '{
    "connector_id": "postgresql",
    "endpoint": {
      "host_port": "HOST_PORT"
    },
    "asset": {
      "database": "DATABASE"
    },
    "authentication": {
      "username_password": {
        "username": "USERNAME",
        "password": {
          "plaintext": "PASSWORD"
        }
      }
    },
    "tls": {
      "mode": "TLS_MODE"
    },
    "network": {
      "private_service_connect": {
        "network_attachment": "projects/PROJECT_ID/regions/NA_REGION/networkAttachments/ATTACHMENT_NAME"
      }
    }
  }' \
  CONNECTION_ID

Remplacez les éléments suivants :

  • LOCATION : région BigQuery (par exemple, us-central1) correspondant à l'ensemble de données que vous souhaitez interroger avec PostgreSQL. Les requêtes utilisant cette connexion doivent être exécutées à partir de cette région.
  • PROJECT_ID : ID de votre projet Google Cloud .
  • HOST_PORT : hôte et port de votre serveur de base de données PostgreSQL au format HOST:PORT (par exemple, 10.10.2.2:5432).
  • DATABASE : nom de la base de données PostgreSQL.
  • USERNAME : utilisateur de la base de données PostgreSQL.
  • PASSWORD : mot de passe de l'utilisateur de la base de données.
  • TLS_MODE : mode de chiffrement TLS (ENCRYPT_VERIFY_CA_AND_HOST, ENCRYPT_VERIFY_CA, ENCRYPT_VERIFY_NONE ou DISABLE). Si vous utilisez un certificat CA personnalisé avec ENCRYPT_VERIFY_CA_AND_HOST ou ENCRYPT_VERIFY_CA, incluez l'objet "private_pki": { "trusted_certificates_pem": "CERT_CONTENT" } sous "tls".
  • NA_REGION : région où se trouve votre rattachement de réseau.
  • ATTACHMENT_NAME : nom de votre rattachement de réseau. Si vous vous connectez via une adresse IP publique, omettez l'objet "network".
  • CONNECTION_ID : identifiant unique de cette connexion.
  • (Facultatif) --kms_key_name=KMS_KEY_NAME : clé de chiffrement gérée par le client (CMEK). Si aucune valeur n'est spécifiée, les identifiants sont protégés par la valeur par défaut Google-owned and Google-managed encryption key.

Configuration de Transport Layer Security (TLS)

Le connecteur PostgreSQL est compatible avec Transport Layer Security (TLS) pour chiffrer le trafic des requêtes en transit entre BigQuery et votre base de données PostgreSQL.

Lorsque vous connectez BigQuery à PostgreSQL, sélectionnez l'un des modes TLS suivants :

  • Chiffrer les données, et valider l'autorité de certification (CA) et le nom d'hôte (mode ENCRYPT_VERIFY_CA_AND_HOST). Effectue une validation complète du serveur à l'aide de TLS. Chiffre toutes les données en transit, vérifie que le certificat du serveur de base de données est signé par une autorité de certification de confiance et vérifie que le nom d'hôte auquel vous vous connectez correspond au nom commun (CN) ou à l'autre nom de l'objet (SAN) du certificat. Obligatoire pour les connexions via des adresses IP publiques.

  • Chiffrer les données et valider la CA uniquement (mode ENCRYPT_VERIFY_CA). Chiffre toutes les données en transit et vérifie que le certificat du serveur est signé par une autorité de certification de confiance, mais ne vérifie pas le nom d'hôte du serveur. Utilisez ce mode si le certificat du serveur est signé par une autorité de certification de confiance, mais que vous vous connectez à l'aide d'une adresse IP ou d'un nom d'hôte qui ne correspond pas au nom commun ou au SAN du certificat.

  • Chiffrement uniquement (mode ENCRYPT_VERIFY_NONE). Chiffre toutes les données transférées entre BigQuery et le serveur. N'effectue pas de validation de certificat ni de nom d'hôte. Recommandé uniquement dans les VPC privés.

  • Aucun chiffrement ni aucune vérification (mode DISABLE). Ne chiffre pas les données et n'effectue pas de validation de certificat ou de nom d'hôte. Autorisé uniquement lors de la connexion via des rattachements de réseau privé dans des réseaux VPC protégés.

Certificats de serveur approuvés au format PEM

Si vous utilisez le mode ENCRYPT_VERIFY_CA_AND_HOST ou ENCRYPT_VERIFY_CA, procédez comme suit :

  • Si le certificat de votre serveur de base de données est signé par une autorité de certification publique (comme celles incluses dans le programme d'autorité de certification racine Mozilla, y compris Let's Encrypt, DigiCert ou Google Trust Services), vous n'avez pas besoin de fournir de certificat. Les certificats racine des autorités de certification publiques sont approuvés par défaut.
  • Si vous utilisez un certificat signé par une autorité de certification interne ou privée au sein de votre organisation, ou un certificat autosigné, vous devez fournir la chaîne de certificats complète au format PEM (Privacy-Enhanced Mail) dans le champ Certificat PEM de confiance lors de la configuration de la connexion.

Partager des connexions avec les utilisateurs

Vous pouvez attribuer les rôles suivants pour permettre aux utilisateurs d'interroger des données et de gérer les connexions :

  • roles/bigquery.connectionUser permet aux utilisateurs de se connecter à des sources de données externes et d'y exécuter des requêtes.

  • roles/bigquery.connectionAdmin permet aux utilisateurs de gérer les connexions.

Pour en savoir plus sur les rôles et les autorisations IAM dans BigQuery, consultez la page Rôles prédéfinis et autorisations.

Sélectionnez l'une des options suivantes :

Console

  1. Accédez à la page BigQuery.

    Accéder à BigQuery

    Les connexions sont répertoriées dans votre projet, dans un groupe appelé Connexions.

  2. Dans le volet de gauche, cliquez sur Explorateur :

    Bouton du volet Explorateur mis en évidence.

    Si le volet de gauche ne s'affiche pas, cliquez sur Développer le volet de gauche pour l'ouvrir.

  3. Cliquez sur votre projet, puis sur Connexions, et sélectionnez une connexion.

  4. Dans le volet Détails, cliquez sur Partager pour partager une connexion. Ensuite, procédez comme suit :

    1. Dans la boîte de dialogue Autorisations de connexion, partagez la connexion avec d'autres comptes principaux en ajoutant ou en modifiant des comptes principaux.

    2. Cliquez sur Enregistrer.

bq

Vous ne pouvez pas partager de connexion avec l'outil de ligne de commande bq. Pour partager une connexion, utilisez la console Google Cloud ou la méthode de l'API BigQuery Connections permettant le partage de connexion.

API

Utilisez la méthode projects.locations.connections.setIAM dans la documentation de référence de l'API REST BigQuery Connections et fournissez une instance de la ressource policy.

Java

Avant d'essayer cet exemple, suivez les instructions de configuration pour Java du guide de démarrage rapide de BigQuery : Utiliser les bibliothèques clientes. Pour en savoir plus, consultez la documentation de référence de l'API BigQuery pour Java.

Pour vous authentifier auprès de BigQuery, configurez le service Identifiants par défaut de l'application. Pour en savoir plus, consultez la page Configurer l'authentification pour les bibliothèques clientes. 

import com.google.api.resourcenames.ResourceName;
import com.google.cloud.bigquery.connection.v1.ConnectionName;
import com.google.cloud.bigqueryconnection.v1.ConnectionServiceClient;
import com.google.iam.v1.Binding;
import com.google.iam.v1.Policy;
import com.google.iam.v1.SetIamPolicyRequest;
import java.io.IOException;

// Sample to share connections
public class ShareConnection {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    String projectId = "MY_PROJECT_ID";
    String location = "MY_LOCATION";
    String connectionId = "MY_CONNECTION_ID";
    shareConnection(projectId, location, connectionId);
  }

  static void shareConnection(String projectId, String location, String connectionId)
      throws IOException {
    try (ConnectionServiceClient client = ConnectionServiceClient.create()) {
      ResourceName resource = ConnectionName.of(projectId, location, connectionId);
      Binding binding =
          Binding.newBuilder()
              .addMembers("group:example-analyst-group@google.com")
              .setRole("roles/bigquery.connectionUser")
              .build();
      Policy policy = Policy.newBuilder().addBindings(binding).build();
      SetIamPolicyRequest request =
          SetIamPolicyRequest.newBuilder()
              .setResource(resource.toString())
              .setPolicy(policy)
              .build();
      client.setIamPolicy(request);
      System.out.println("Connection shared successfully");
    }
  }
}

Tarifs

Les requêtes et connexions fédérées sont soumises aux frais suivants :

Étapes suivantes