Подключение к базе данных в кластере PostgreSQL

1. Настройте все группы безопасности кластера так, чтобы они разрешали входящий трафик из группы безопасности, в которой находится ВМ, на порт 6432. Для этого в этих группах создайте следующее правило для входящего трафика:

   • протокол: TCP;
   • диапазон портов: 6432;
   • тип источника: Группа безопасности;
   • источник: группа безопасности, в которой находится ВМ. Если она совпадает с настраиваемой группой, то укажите Текущая.

2. Настройте группу безопасности, в которой находится ВМ так, чтобы можно было подключаться к ВМ и был разрешен трафик между ВМ и хостами кластера.

   Пример правил для ВМ:

   • Для входящего трафика:

     • протокол: TCP;
     • диапазон портов: 22;
     • тип источника: CIDR;
     • источник: 0.0.0.0/0.

     Это правило позволяет подключаться к ВМ по протоколу SSH.

   • Для исходящего трафика:

     • протокол: Any;
     • диапазон портов: 0-65535;
     • тип назначения: CIDR;
     • назначение: 0.0.0.0/0.

     Это правило разрешает любой исходящий трафик, что позволяет не только подключаться к кластеру, но и устанавливать сертификаты и утилиты, необходимые для подключения к кластеру.

1. Создайте новое соединение с БД:
   1. Выберите в меню База данных пункт Новое соединение.
   2. Выберите из списка БД PostgreSQL.
   3. Нажмите кнопку Далее.
   4. Укажите параметры подключения на вкладке Главное:
      • Хост — FQDN хоста или один из особых FQDN;
      • Порт — 6432;
      • База данных — имя БД для подключения;
      • В блоке Аутентификация укажите имя и пароль пользователя БД.
   5. На вкладке SSL:
      1. Включите настройку Использовать SSL.
      2. В поле Корневой сертификат укажите путь к файлу SSL-сертификата для подключения.
2. Нажмите кнопку Тест соединения ... для проверки подключения. При успешном подключении будет выведен статус подключения, информация о СУБД и драйвере.
3. Нажмите кнопку Готово, чтобы сохранить настройки соединения с БД.

1. Подключитесь к базе данных:

   psql "host=<FQDN одного или нескольких хостов PostgreSQL> \
       port=6432 \
       sslmode=verify-full \
       dbname=<имя БД> \
       user=<имя пользователя> \
       target_session_attrs=read-write"
   

   После выполнения команды введите пароль пользователя для завершения процедуры подключения.

2. Для проверки успешности подключения выполните запрос:

   SELECT version();
   

1. Пример кода:

   connect.go

   package main
   
   import (
       "context"
       "crypto/tls"
       "crypto/x509"
       "fmt"
       "io/ioutil"
       "os"
   
       "github.com/jackc/pgx/v4"
   )
   
   const (
       host     = "<FQDN одного или нескольких хостов PostgreSQL>"
       port     = 6432
       user     = "<имя пользователя>"
       password = "<пароль пользователя>"
       dbname   = "<имя БД>"
       ca       = "/home/<домашняя директория>/.postgresql/root.crt"
   )
   
   func main() {
   
       rootCertPool := x509.NewCertPool()
       pem, err := ioutil.ReadFile(ca)
       if err != nil {
           panic(err)
       }
   
       if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
           panic("Failed to append PEM.")
       }
   
       connstring := fmt.Sprintf(
           "host=%s port=%d dbname=%s user=%s password=%s sslmode=verify-full target_session_attrs=read-write",
           host, port, dbname, user, password)
   
       connConfig, err := pgx.ParseConfig(connstring)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to parse config: %v\n", err)
           os.Exit(1)
       }
   
       connConfig.TLSConfig = &tls.Config{
           RootCAs:            rootCertPool,
           InsecureSkipVerify: true,
       }
   
       conn, err := pgx.ConnectConfig(context.Background(), connConfig)
       if err != nil {
           fmt.Fprintf(os.Stderr, "Unable to connect to database: %v\n", err)
           os.Exit(1)
       }
   
       defer conn.Close(context.Background())
   
       var version string
   
       err = conn.QueryRow(context.Background(), "select version()").Scan(&version)
       if err != nil {
           fmt.Fprintf(os.Stderr, "QueryRow failed: %v\n", err)
           os.Exit(1)
       }
   
       fmt.Println(version)
   }
   

   При этом способе подключения в коде необходимо указывать полный путь к сертификату root.crt для PostgreSQL в переменной ca.

2. Подключение:

   go run connect.go
   

1. Пример кода:

   src/java/com/example/App.java

   package com.example;
   
   import java.sql.*;
   
   public class App {
     public static void main(String[] args) {
       String DB_URL     = "jdbc:postgresql://<хост 1 PostgreSQL:6432,...,хост N PostgreSQL:6432>/<имя БД>&  targetServerType=master&ssl=true&  sslmode=verify-full";
       String DB_USER    = "<имя пользователя>";
       String DB_PASS    = "<пароль пользователя>";
   
       try {
         Class.forName("org.postgresql.Driver");
   
         Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
         ResultSet q = conn.createStatement().executeQuery("SELECT version()");
         if(q.next()) {System.out.println(q.getString(1));}
   
         conn.close();
       }
       catch(Exception ex) {ex.printStackTrace();}
     }
   }
   

2. Сборка и подключение:

   mvn clean package && \
   java -jar target/app-0.1.0-jar-with-dependencies.jar
   

app.js

"use strict";
const fs = require("fs");
const pg = require("pg");

const config = {
    connectionString:
        "postgres://<имя пользователя>:<пароль пользователя>@c-<идентификатор кластера>.rw.mdb.yandexcloud.net:6432/<имя БД>",
    ssl: {
        rejectUnauthorized: true,
        ca: fs
            .readFileSync("/home/<домашняя директория>/.postgresql/root.crt")
            .toString(),
    },
};

const conn = new pg.Client(config);

conn.connect((err) => {
    if (err) throw err;
});
conn.query("SELECT version()", (err, q) => {
    if (err) throw err;
    console.log(q.rows[0]);
    conn.end();
});

При этом способе подключения в коде необходимо указывать полный путь к сертификату root.crt для PostgreSQL в переменной ca.

1. Пример кода:

   /etc/odbc.ini

   [postgresql]
   Driver=PostgreSQL Unicode
   Servername=<хост 1 PostgreSQL>,...,<хост N PostgreSQL>
   Username=<имя пользователя>
   Password=<пароль пользователя>
   Database=<имя БД>
   Port=6432
   Pqopt=target_session_attrs=read-write
   Sslmode= verify-full
   

2. Подключение:

   isql -v postgresql
   

   После подключения к СУБД выполните команду SELECT version();.

1. Пример кода:

   connect.php

   <?php
   $conn = pg_connect("
         host=<FQDN одного или нескольких хостов PostgreSQL>
         port=6432
         sslmode=verify-full
         dbname=<имя БД>
         user=<имя пользователя>
         password=<пароль пользователя>
         target_session_attrs=read-write
     ");
   
   $q = pg_query($conn, "SELECT version()");
   $result = pg_fetch_row($q);
   echo $result[0];
   
   pg_close($conn);
   ?>
   

2. Подключение:

   php connect.php
   

1. Установите переменные окружения для подключения:

   $Env:PGSSLMODE="verify-full"; $Env:PGTARGETSESSIONATTRS="read-write"
   

2. Подключитесь к базе данных:

   & 'C:\Program Files\PostgreSQL\<версия>\bin\psql.exe' `
         -h <FQDN одного или нескольких хостов PostgreSQL> `
         -p 6432 `
         -U <имя пользователя> `
         <имя БД>
   

   После выполнения команды введите пароль пользователя для завершения процедуры подключения.

3. Для проверки успешности подключения выполните запрос:

   SELECT version();
   

1. Пример кода:

   connect.py

   import psycopg2
   
   conn = psycopg2.connect("""
       host=<FQDN одного или нескольких хостов PostgreSQL>
       port=6432
       sslmode=verify-full
       dbname=<имя БД>
       user=<имя пользователя>
       password=<пароль пользователя>
       target_session_attrs=read-write
   """)
   
   q = conn.cursor()
   q.execute('SELECT version()')
   
   print(q.fetchone())
   
   conn.close()
   

2. Подключение:

   python3 connect.py
   

1. Пример кода:

   connect.rb

   require "pg"
   
   conn = PG.connect("
           host=<FQDN одного или нескольких хостов PostgreSQL>
           port=6432
           dbname=<имя БД>
           user=<имя пользователя>
           password=<пароль пользователя>
           target_session_attrs=read-write
           sslmode=verify-full
   ")
   
   q = conn.exec("SELECT version()")
   puts q.getvalue 0, 0
   
   conn.close()
   

2. Подключение:

   ruby connect.rb