Connecting to a database in a PostgreSQL cluster

You can connect to Managed Service for PostgreSQL cluster hosts:

• Over the internet, if you configured public access for the appropriate host. You can only connect to such hosts over an SSL connection.

• From Yandex Cloud virtual machines located in the same cloud network. If there is no public access to a host, using SSL for connections from such virtual machines is not required.

• From the Serverless Containers container. If the host is not publicly accessible, the container must be located on the same cloud network.

Configuring security groupsConfiguring security groups

To connect to a cluster, security groups must include rules allowing traffic from certain ports, IP addresses, or from other security groups.

Rule settings depend on the connection method you select:

For more information about security groups, see Security groups.

Getting an SSL certificateGetting an SSL certificate

PostgreSQL hosts with public access only support encrypted connections. To use them, get an SSL certificate:

mkdir -p ~/.postgresql && \
wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
     --output-document ~/.postgresql/root.crt && \
chmod 0655 ~/.postgresql/root.crt

mkdir $HOME\.postgresql; curl.exe -o $HOME\.postgresql\root.crt https://storage.yandexcloud.net/cloud-certs/CA.pem

To use graphical IDEs, save a certificate to a local folder and specify the path to it in the connection settings.

PostgreSQL host FQDNPostgreSQL host FQDN

To connect to a host, you need its fully qualified domain name (FQDN). You can obtain it in one of the following ways:

• Request a list of cluster hosts.

• In the management console, copy the command for connecting to the cluster. This command contains the host FQDN. To get the command, go to the cluster page and click Connect.

• Look up the FQDN in the management console:

  1. Go to the cluster page.
  2. Go to Hosts.
  3. Copy the Host FQDN column value.

Cluster hosts also use special FQDNs.

Special FQDNsSpecial FQDNs

Alongside regular FQDNs, Managed Service for PostgreSQL provides several special FQDNs, which can also be used when connecting to a cluster.

Current masterCurrent master

An FQDN in c-<cluster_ID>.rw.mdb.yandexcloud.net format always points to the current master host in the cluster. You can get the cluster ID with a list of clusters in the folder.

When connecting to this FQDN, both read and write operations are allowed.

Here is an example of connecting to a master host for a cluster with the c9qash3nb1v9******** ID:

psql "host=c-c9qash3nb1v9********.rw.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB_name> \
      user=<username> \
      target_session_attrs=read-write"

Most recent replicaMost recent replica

An FQDN in c-<cluster_ID>.ro.mdb.yandexcloud.net format points to a replica that is most up-to-date with the master host. You can get the cluster ID with a list of clusters in the folder.

Specifics:

• When connecting to this FQDN, only read operations are allowed.
• If there are no active replicas in the cluster, this FQDN will point to the current master host.

Here is an example of connecting to the most recent replica for a cluster with the c9qash3nb1v9******** ID:

psql "host=c-c9qash3nb1v9********.ro.mdb.yandexcloud.net \
      port=6432 \
      sslmode=verify-full \
      dbname=<DB_name> \
      user=<username> \
      target_session_attrs=any"

Automatic master host selectionAutomatic master host selection

To guarantee a connection to the master host:

1. In the host argument, provide one of the following:

   • Special master host FQDN as shown in the examples below.
   • FQDNs of all cluster hosts.

2. Provide the target_session_attrs=read-write parameter. This parameter is supported by the libpq library starting from version 10.

To upgrade the library version used by the psql utility:

• For Debian-based Linux distributions, install the postgresql-client-10 package or higher (e.g., using an APT repository).
• For operating systems that use RPM packages, use the PostgreSQL distribution available from the yum repository.

Connecting from graphical IDEsConnecting from graphical IDEs

Connections were tested in the following environment:

• Ubuntu 20.04, DBeaver: 22.2.4
• MacOS Monterey 12.7:
  • JetBrains DataGrip: 2023.3.4
  • DBeaver Community: 24.0.0

You can only use graphical IDEs to connect to public cluster hosts using an SSL certificate.

To avoid connection errors, save the certificate to a local folder that does not require administrator rights to access.

Connecting from Yandex WebSQLConnecting from Yandex WebSQL

You can send SQL queries to databases in a Managed Service for PostgreSQL cluster using Yandex WebSQL.

WebSQL is a Yandex Cloud service that allows you to connect to managed database clusters, work with DBs, tables, and schemas, and run queries. It is a web-based tool that does not require additional authorization and simplifies working with SQL queries by suggesting prompts.

To connect from WebSQL to a Managed Service for PostgreSQL cluster, create a connection:

1. Go to the folder page and select Managed Service for PostgreSQL.
2. Click the cluster name.
3. Enable the WebSQL access option in the cluster settings if it is not enabled yet.
4. Select the WebSQL tab.
5. Click Create connection and specify the connection parameters:
   • Connection name.
   • Database type: PostgreSQL.
   • Cluster: Defaults to your current PostgreSQL cluster.
   • Username you will use to connect to the database in the cluster.
   • User password.
   • Databases you want to connect to. You can only connect to the databases that exist in this cluster. The user you specified must have access to them configured.
6. Click Create.

To open the SQL editor, click the created connection on the WebSQL tab. See a reference list of supported queries in the PostgreSQL documentation.

For more information about working with WebSQL, see its documentation.

Connecting from pgAdmin 4Connecting from pgAdmin 4

The connection was tested for pgAdmin 4 ver. 7.0 on Ubuntu 20.04.

You can only use pgAdmin 4 to connect to public cluster hosts using an SSL certificate.

Create a new server connection:

1. Select Object → Register → Server...

2. On the General tab, in the Name field, specify the name for the cluster. This name will be shown in the pgAdmin 4 interface. You can set any name.

3. In the Connection tab, specify the connection parameters:

   • Host name/address: Special master host FQDN or regular host FQDN.
   • Port: 6432.
   • Maintenance database: DB you want to connect to.
   • Username: Username for connection.
   • Password: User password.

4. In the Parameters tab:

   • Set the SSL mode parameter to verify-full.
   • Add a new Root certificate parameter and specify the path to the saved SSL certificate file in it.

5. Click Save to save the server connection settings.

As a result, the cluster appears in the server list in the navigation menu.

Connecting from Looker StudioConnecting from Looker Studio

You can only use Looker Studio to connect to public cluster hosts.

1. Save the CA.pem server certificate to a local directory.

2. In the same directory, generate a client certificate with a private key:

   openssl req -newkey rsa:2048 -nodes -keyout private.pem -out cert.pem
   

   When creating a certificate, you will be prompted to change some settings. Press Enter to use their default values.

   You will see two files in your local directory: cert.pem and private.pem.

3. On the Looker Studio navigation page, select Create → Data source.

4. Select PostgreSQL.

5. Fill in the fields as follows:

   • Host name or IP address: Special master host FQDN or regular host FQDN.
   • Port: 6432.
   • Database: DB to connect to.
   • Username: Username for connection.
   • Password: User password.

6. Select Enable SSL and Enable client authentication.

7. Specify the certificate files and the client private key in the respective fields:

   • Server certificate: Select the CA.pem file.
   • Client certificate: Select the cert.pem file.
   • Client private key: Select the private.pem file.

8. Click Authenticate.

Before you connect from a Docker containerBefore you connect from a Docker container

To connect to a Managed Service for PostgreSQL cluster from a Docker container, add the following lines to the Dockerfile:

RUN apt-get update && \
    apt-get install postgresql-client --yes

RUN apt-get update && \
    apt-get install wget postgresql-client --yes && \
    mkdir --parents ~/.postgresql && \
    wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" \
         --output-document ~/.postgresql/root.crt && \
    chmod 0655 ~/.postgresql/root.crt

Examples of connection stringsExamples of connection strings

Examples were tested in the following environment:

• Yandex Cloud virtual machine running Ubuntu 20.04 LTS:
  • Bash: 5.0.16.
  • Python: 3.8.2; pip3: 20.0.2.
  • PHP: 7.4.3.
  • OpenJDK: 11.0.8; Maven: 3.6.3.
  • Node.JS: 10.19.0, npm: 6.14.4.
  • Go: 1.13.8.
  • Ruby: 2.7.0p0.
  • unixODBC: 2.3.6.
• Virtual machine in Yandex Cloud running Windows Server 2019 Datacenter:
  • PostgreSQL: 13.
  • PowerShell: 5.1.17763.1490 Desktop.
  • .NET 5
  • Microsoft.EntityFrameworkCore 5.0.9
  • Npgsql.EntityFrameworkCore.PostgreSQL 5.0.7

You can only connect to public PostgreSQL hosts using an SSL certificate. Prior to connecting to such hosts, generate a certificate.

The examples below assume that the root.crt SSL certificate is located in the following directory:

• /home/<home_directory>/.postgresql/ for Ubuntu.
• $HOME\AppData\Roaming\postgresql for Windows.

Connecting without an SSL certificate is only supported for non-public hosts. If this is the case, internal cloud network traffic will not be encrypted when connecting to a database.

You can connect to a cluster using both regular host FQDNs (you can send a comma-separated list consisting of several such FQDNs) and special FQDNs. The examples use a special FQDN of the current master host.

To see code examples with the host FQDN filled in, open the cluster page in the management console and click Connect.

1C:Enterprise1C:Enterprise

If the cluster uses a PostgreSQL version optimized to work with 1C:Enterprise, specify the following in the settings:

• Secure connection: Disabled.
• DBMS type: PostgreSQL.
• Database server: c-<cluster_ID>.rw.mdb.yandexcloud.net port=6432.
• Database name: <DB_name>.
• Database user: <username>.
• User password: <password>.
• Create database if none present: Disabled.

BashBash

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes postgresql-client

C++ (userver framework)C++ (userver framework)

The asynchronous userver framework provides a rich set of abstractions for creating utilities, services, and microservices in C++. Among other things, the framework provides opportunities to work with PostgreSQL.

Before connecting, access the framework in one of the following ways:

• Create a Yandex Compute Cloud virtual machine from the userver image. This image already contains the framework and all required dependencies.
• Manually install the framework and all required dependencies.

Once started, the service will wait for a POST request from the user. While waiting for a request, the service will periodically check the PostgreSQL cluster's availability by running the SELECT 1 as ping request. You can find this information in the service logs.

tskv ... level=INFO      module=MakeQuerySpan ( userver/postgresql/src/storages/postgres/detail/connection_impl.cpp:647 )
...
db_statement=SELECT 1 AS ping
db_type=postgres
db_instance=********
peer_address=c-********.rw.mdb.yandexcloud.net:6432
...

C# EF CoreC# EF Core

To connect to a cluster, you need the Npgsql package.

using Npgsql;

namespace ConsoleApp
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var host       = "c-<cluster_ID>.rw.mdb.yandexcloud.net";
            var port       = "6432";
            var db         = "<db_name>";
            var username   = "<username>";
            var password   = "<user_password>";
            var connString = $"Host={host};Port={port};Database={db};Username={username};Password={password};Ssl Mode=VerifyFull;";

            await using var conn = new NpgsqlConnection(connString);
            await conn.OpenAsync();

            await using (var cmd = new NpgsqlCommand("SELECT VERSION();", conn))
            await using (var reader = await cmd.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    Console.WriteLine(reader.GetInt32(0));
                }
            }
        }
    }
}

GoGo

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes golang git && \
go mod init example && go get github.com/jackc/pgx/v4

JavaJava

Before connecting:

1. Install the dependencies:

   sudo apt update && sudo apt install --yes default-jdk maven
   

2. Create a folder for the Maven project:

   cd ~/ && mkdir -p project/src/java/com/example && cd project/
   

3. Create a configuration file for Maven:

   pom.xml

   <?xml version="1.0" encoding="utf-8"?>
   <project xmlns="http://maven.apache.org/POM/4.0.0"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
   
     <modelVersion>4.0.0</modelVersion>
     <groupId>com.example</groupId>
     <artifactId>app</artifactId>
     <packaging>jar</packaging>
     <version>0.1.0</version>
     <properties>
       <maven.compiler.source>1.8</maven.compiler.source>
       <maven.compiler.target>1.8</maven.compiler.target>
     </properties>
     <dependencies>
       <dependency>
         <groupId>org.postgresql</groupId>
         <artifactId>postgresql</artifactId>
         <version>42.2.16</version>
       </dependency>
     </dependencies>
     <build>
       <finalName>${project.artifactId}-${project.version}</finalName>
       <sourceDirectory>src</sourceDirectory>
       <resources>
         <resource>
           <directory>src</directory>
         </resource>
       </resources>
       <plugins>
         <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-assembly-plugin</artifactId>
           <executions>
             <execution>
               <goals>
                 <goal>attached</goal>
               </goals>
               <phase>package</phase>
               <configuration>
                 <descriptorRefs>
                   <descriptorRef>
                   jar-with-dependencies</descriptorRef>
                 </descriptorRefs>
                 <archive>
                   <manifest>
                     <mainClass>com.example.App</mainClass>
                   </manifest>
                 </archive>
               </configuration>
             </execution>
           </executions>
         </plugin>
         <plugin>
           <groupId>org.apache.maven.plugins</groupId>
           <artifactId>maven-jar-plugin</artifactId>
           <version>3.1.0</version>
           <configuration>
             <archive>
               <manifest>
                 <mainClass>com.example.App</mainClass>
               </manifest>
             </archive>
           </configuration>
         </plugin>
       </plugins>
     </build>
   </project>
   

   Current dependency version for Maven: postgresql.

Node.jsNode.js

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes nodejs npm && \
npm install pg

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

const config = {
    connectionString:
        "postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<db_name>"
};

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();
});

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

const config = {
    connectionString:
        "postgres://<username>:<user_password>@c-<cluster_ID>.rw.mdb.yandexcloud.net:6432/<db_name>",
    ssl: {
        rejectUnauthorized: true,
        ca: fs
            .readFileSync("/home/<home_directory>/.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();
});

You can get the cluster ID with a list of clusters.

Connecting:

node app.js

ODBCODBC

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes unixodbc odbc-postgresql

The PostgreSQL ODBC driver will be registered automatically in /etc/odbcinst.ini.

PHPPHP

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes php php-pgsql

PowerShellPowerShell

Before connecting, install the same version of PostgreSQL for Windows that is used in the cluster. Select the Command Line Tools installation only.

PythonPython

Before connecting, install the dependencies:

sudo apt update && sudo apt install -y python3 python3-pip && \
pip3 install psycopg2-binary

RR

Before connecting:

1. Install the dependencies:

   sudo apt update && sudo apt install libpq-dev r-base --yes
   

2. Install the RPostgres library:

   sudo R --interactive
   install.packages("RPostgres")
   quit()
   

RubyRuby

Before connecting, install the dependencies:

sudo apt update && sudo apt install --yes ruby ruby-pg

If the connection to the cluster and the test query are successful, the PostgreSQL version will be shown. The userver framework example is one exception, where the SELECT 1 as ping test query will be made from time to time to check the PostgreSQL cluster for availability.

ClickHouse® is a registered trademark of ClickHouse, Inc.