[Yandex Cloud documentation](../../../index.md) > [Yandex MPP Analytics for PostgreSQL](../../index.md) > [Step-by-step guides](../index.md) > [Connection](index.md) > Connecting from applications

# Connecting to a Yandex MPP Analytics for PostgreSQL cluster from applications

This section provides settings for connecting to Yandex MPP Analytics for PostgreSQL cluster hosts using [command line tools](#command-line-tools), [graphical IDEs](#connection-ide), [pgAdmin 4](#connection-pgadmin), or a [Docker container](#connection-docker). To learn how to connect from your application code, see [Code examples](code-examples.md).

Creating a Yandex MPP Analytics for PostgreSQL cluster does not entail creating a user database. To test the connection, use the `postgres` service database.

To connect to a publicly accessible , [prepare an SSL certificate](index.md#get-ssl-cert). In these examples, the `root.crt` SSL certificate is located in the following directory:

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

You can connect to a cluster using either a master host's regular FQDN or a primary master host's [special FQDN](fqdn.md#fqdn-master). When connecting via _psql_ or using a JDBC connector, you can specify two master hosts at the same time. To learn how to get a host's FQDN, see [this guide](fqdn.md).


## Command line tools {#command-line-tools}

To see code examples with the host FQDN filled in, open the cluster page in the [management console](https://console.yandex.cloud) and click **Connect**.

### Linux (Bash) {#bash}

Before connecting, install the required dependencies:

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

Connect to a database:

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  ```bash
  psql "host=<primary_master_host_FQDN>,<standby_master_host_FQDN> \
        port=6432 \
        sslmode=disable \
        dbname=postgres \
        user=<username> \
        target_session_attrs=read-write"
  ```

- Connecting with SSL {#with-ssl}

  ```bash
  psql "host=<primary_master_host_FQDN>,<standby_master_host_FQDN> \
        port=6432 \
        sslmode=verify-full \
        dbname=postgres \
        user=<username> \
        target_session_attrs=read-write"
  ```

{% endlist %}

After runing this command, enter the user password to complete your connection.

To check the connection, run the following query:

```sql
SELECT version();
```

### Windows (PowerShell) {#powershell}

Before connecting, install the current version of [PostgreSQL for Windows](https://www.postgresql.org/download/windows/). Install only the *Command Line Tools*.

Connect to a database:

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  ```powershell
  & "C:\Program Files\PostgreSQL\<version>\bin\psql.exe" "`
      host=<primary_master_host_FQDN>,<standby_master_host_FQDN> `
      port=6432 `
      sslmode=disable `
      dbname=postgres `
      user=<username>"
  ```

- Connecting with SSL {#with-ssl}

  ```powershell
  & "C:\Program Files\PostgreSQL\<version>\bin\psql.exe" "`
      host=<primary_master_host_FQDN>,<standby_master_host_FQDN> `
      port=6432 `
      sslmode=verify-full `
      dbname=postgres `
      user=<username>"
  ```

{% endlist %}

After runing this command, enter the user password to complete your connection.

To check the connection, run the following query:

```sql
SELECT version();
```


### Connecting with authorization via IAM {#iam}

You can connect to a Yandex MPP Analytics for PostgreSQL cluster database from the [Yandex Cloud CLI](../../../cli/quickstart.md#install) via IAM authorization. To do this, associate a [Yandex account](../../../iam/concepts/users/accounts.md#passport) or [federated account](../../../iam/concepts/users/accounts.md#saml-federation) with the Yandex MPP Analytics for PostgreSQL user. You can use IAM authorization only when connecting to a public cluster, in which case you do not need an SSL certificate.

Before connecting, install the PostgreSQL client:

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

Set up your Yandex MPP Analytics for PostgreSQL cluster for connection:

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to **Yandex MPP Analytics&nbsp;for&nbsp;PostgreSQL**.

  1. Click the cluster name.

  1. [Enable public access to the cluster](../update.md#change-public-access).
 
  1. Assign a role to the user account connecting to the database:
     1. Select the **Access bindings** tab and click **Assign roles**.
     1. Enter the user account’s email.
     1. Click ![image](../../../_assets/console-icons/plus.svg) **Add role** and select the `managed-greenplum.clusters.connector` role.
     1. Click **Save**.

  1. Create a user in the Yandex MPP Analytics for PostgreSQL cluster and grant them access to a database of your choice:
     1. Connect to the Yandex MPP Analytics for PostgreSQL cluster using any method you like.
     1. Create a user with their account email as username:

        ```sql
        CREATE ROLE "<account_email>"
            LOGIN
            ENCRYPTED PASSWORD '<password>';
        ```
     1. Optionally, [configure privileges](../roles-and-users.md#privileges) and attributes of the new user.

  1. Add the authentication rule for the user you created:
     1. Select the **User authentication** tab.
     1. Click **Edit rules**.
     1. Click ![image](../../../_assets/console-icons/plus.svg) **Add rule** and specify its settings:

        * **Type**: Interconnect type.
        * **Database**: Database name.
        * **User**: User account's email.
        * **Address (CIDR/FQDN)**: IP range to connect to the database from.
        * **Method**: `iam`.

     1. Click **Save**.

{% endlist %}

To connect to a Yandex MPP Analytics for PostgreSQL cluster database, run this command:

```bash
yc managed-greenplum connect <cluster_name_or_ID> --db <DB_name>
```


## Connecting from graphical IDEs {#connection-ide}

**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 a public cluster using SSL certificates.


To avoid connection errors, [save the certificate](https://storage.yandexcloud.net/cloud-certs/RootCA.pem) to a local folder that does not require administrator rights to access.

{% list tabs group=ide %}

- DataGrip {#datagrip}

    1. Create a data source:

        1. Select **File** → **New** → **Data Source** → **Greenplum®**.
        1. On the **General** tab:

            1. Configure the connection as follows:

                * **User**, **Password**: DB user name and password.
                * **URL**: Connection string:

                    ```http
                    jdbc:postgresql://<primary_master_host_FQDN>:6432,<standby_master_host_FQDN>:6432/<DB_name>
                    ```

            1. Click **Download** to download the connection driver.
        1. On the **SSH/SSL** tab:
            1. Enable **Use SSL**.
            1. In the **CA file** field, specify the path to the [SSL certificate for your connection](#get-ssl-cert).
    1. Click **Test Connection**. If the connection is successful, you will see the connection status and information about the DBMS and driver.
    1. Click **OK** to save the data source.

- DBeaver {#dbeaver}

    1. Create a new DB connection:
        1. In the **Database** menu, select **New connection**.
        1. Select **Greenplum®** from the DB list.
        1. Click **Next**.
        1. Specify the connection settings on the **Main** tab:
            * **Host**: [Special FQDN of the primary master](fqdn.md#fqdn-master), `c-<cluster_ID>.rw.mdb.yandexcloud.net`.
            * **Port**: `6432`.
            * **Database**: DB to connect to.
            * Under **Authentication**, specify the DB user name and password.
        1. On the **SSL** tab:
            1. Enable **Use SSL**.
            1. In the **Root certificate** field, specify the path to the saved [SSL certificate](#get-ssl-cert) file.
    1. Click **Test Connection ...**. If the connection is successful, you will see the connection status, DBMS information, and driver details.
    1. Click **Done** to save the database connection settings.

{% endlist %}

## Connecting from pgAdmin 4 {#connection-pgadmin}

The connection has been checked for [pgAdmin 4](https://www.pgadmin.org) ver. 7.1 on macOS Ventura 13.0 and Microsoft Windows 10 Pro 21H1.


You can only use pgAdmin 4 to connect to public cluster hosts [using an SSL certificate](#get-ssl-cert).


Create a new server connection:

1. Select **Object** → **Register** → **Server...**.
1. On the **General** tab, in the **Name** field, specify the cluster name to be shown in the pgAdmin 4 interface. You can set any name.
1. In the **Connection** tab, specify the connection settings:

    * **Host name/address**: [Special master host FQDN](fqdn.md#fqdn-master) or regular host FQDN.
    * **Port**: `6432`.
    * **Maintenance database**: Name of the `postgres` maintenance database.
    * **Username**: Username for connection.
    * **Password**: User password.

1. 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.

1. Click **Save** to save the server connection settings.

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

To monitor the cluster status, use [Yandex Monitoring](../monitoring.md) rather than the **Dashboard** tab in pgAdmin 4, since the latter might generate an error:

```text
column "wait_event_type" does not exist LINE 10: wait_event_type || ': ' || wait_event AS wait_event, ^
```

This error does not occur in other pgAdmin 4 tabs.

## Before you connect from a Docker container {#connection-docker}

To connect to a Yandex MPP Analytics for PostgreSQL cluster from a Docker container, add the following lines to the Dockerfile:

{% list tabs group=connection %}


- Connecting without SSL {#without-ssl}

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


- Connecting with SSL {#with-ssl}

    ```bash
    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
    ```

{% endlist %}

_Greenplum® and Greenplum Database® are registered trademarks or trademarks of Broadcom Inc. in the United States and/or other countries._