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

# Connecting to a PostgreSQL cluster from applications

You can connect to a PostgreSQL cluster using [command line tools](#command-line-tools), [graphical IDEs](#connection-ide), [Yandex WebSQL](#websql), [pgAdmin 4](#connection-pgadmin), [Looker Studio](#connection-google-looker), or a [Docker container](#connection-docker). To learn how to connect from the code of your application, see [Code examples](code-examples.md).

You can connect to ClickHouse® cluster hosts using [SSL certificates](index.md#get-ssl-cert). The examples below assume that the `root.crt` certificate is located in this directory:

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

Before connecting, [configure security groups](index.md#configuring-security-groups) for the cluster, if required.

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

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  1. Connect to a database:

      ```bash
      psql "host=<list_of_cluster_hosts> \
            port=6432 \
            sslmode=disable \
            dbname=<DB_name> \
            user=<username> \
            target_session_attrs=read-write"
      ```
 
      In the `host` parameter, specify the cluster hosts, comma-separated, in `<availability_zone>-<host_ID>.<DNS_zone>` format (e.g., `rc1a-goh2a9tr********.mdb.yandexcloud.net`).    
 
      After you run this command, enter the user password to complete connecting to the database.

  1. To check the connection, run the following query:

      ```bash
      SELECT version();
      ```

- Connecting with SSL {#with-ssl}

  1. Connect to a database:

      ```bash
      psql "host=<list_of_cluster_hosts> \
            port=6432 \
            sslmode=verify-full \
            dbname=<DB_name> \
            user=<username> \
            target_session_attrs=read-write"
      ```
  
      In the `host` parameter, specify the cluster hosts, comma-separated, in `<availability_zone>-<host_ID>.<DNS_zone>` format (e.g., `rc1a-goh2a9tr********.mdb.yandexcloud.net`).

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

  1. To check the connection, run the following query:

      ```bash
      SELECT version();
      ```

{% endlist %}

### Windows (PowerShell) {#powershell}

Before connecting, install [PostgreSQL for Windows](https://www.postgresql.org/download/windows/) using the same version that is installed in the cluster. Install only the *Command Line Tools*.

{% list tabs group=connection %}

- Connecting without SSL {#without-ssl}

  1. Set the environment variables for the connection:

     ```powershell
     $Env:PGSSLMODE="disable"; $Env:PGTARGETSESSIONATTRS="read-write"
     ```

  1. Connect to a database:

     ```powershell
     & "C:\Program Files\PostgreSQL\<version>\bin\psql.exe" `
           --host=<list_of_hosts> `
           --port=6432 `
           --username=<username> `
           <DB_name>
     ```

     In the `host` parameter, specify the cluster hosts, comma-separated, in `<availability_zone>-<host_ID>.<DNS_zone>` format (e.g., `rc1a-goh2a9tr********.mdb.yandexcloud.net`).

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

  1. To check the connection, run the following query:

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

- Connecting with SSL {#with-ssl}

  1. Set the environment variables for the connection:

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

  1. Connect to a database:

      ```powershell
      & "C:\Program Files\PostgreSQL\<version>\bin\psql.exe" `
        --host=<list_of_hosts> `
        --port=6432 `
        --username<username> `
        <DB_name>
      ```

      In the `host` parameter, specify the cluster hosts, comma-separated, in `<availability_zone>-<host_ID>.<DNS_zone>` format (e.g., `rc1a-goh2a9tr********.mdb.yandexcloud.net`).

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

  1. To check the connection, run the following query:

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

{% endlist %}


### Connecting with IAM authentication {#iam}

You can connect to a PostgreSQL database via the [Yandex Cloud CLI](../../../cli/quickstart.md#install) using IAM authentication. This method is available to [Yandex accounts](../../../iam/concepts/users/accounts.md#passport), [federated accounts](../../../iam/concepts/users/accounts.md#saml-federation), and [local users](../../../iam/concepts/users/accounts.md#local). When connecting with IAM authentication, you do not need to obtain an SSL certificate or specify the cluster hosts’ FQDNs.

Before connecting, install the PostgreSQL client:

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

Set up your PostgreSQL cluster for connection:

{% list tabs group=instructions %}

- Management console {#console}

  1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
  1. Click the cluster name.
  1. Enable public access for the cluster hosts:
     1. Select the **Hosts** tab.
     1. Click ![image](../../../_assets/console-icons/ellipsis.svg) in the first host's row and select **Edit**.
     1. Enable **Public access**.
     1. Repeat the same for the remaining hosts in the cluster.
  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-postgresql.clusters.connector` role.
     1. Click **Save**.
  1. Create a user named PostgreSQL:
     1. Select the **Users** tab.
     1. Click **Create user**.
     1. Select **IAM** as the authentication method.
     1. Select the account with the `managed-postgresql.clusters.connector` role.
     1. In the **Database** field, click ![image](../../../_assets/console-icons/plus.svg).
     1. Select the database from the drop-down list.
     1. Click **Save**.

{% endlist %}

To connect to the PostgreSQL database, run this command:

```bash
yc managed-postgresql 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`

From graphical IDEs, you can only connect to public cluster hosts using an SSL certificate.

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** → **PostgreSQL**.
     1. Specify the connection settings on the **General** tab:
        * **User**, **Password**: Database user name and password.
        * **URL**: Connection string:

          ```http
          jdbc:postgresql://<special_FQDN>:6432/<DB_name>
          ```

          You can also use a list of all cluster host [FQDNs](fqdn.md) in the connection string:

          ```http
          jdbc:postgresql://<PostgreSQL_host_1>:6432,...,<PostgreSQL_host_N>:6432/<DB_name>
          ```

        * 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](index.md#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 **PostgreSQL** from the DB list.
     1. Click **Next**.
     1. Specify the connection settings on the **Main** tab:
        * **Host**: [Master FQDN](fqdn.md#fqdn-master) or [regular host FQDN](fqdn.md).
        * **Port**: `6432`.
        * **Database**: Target database name.
        * 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](index.md#get-ssl-cert) file.
  1. Click **Test Connection ...**. If the connection is successful, you will see the connection status and information about the DBMS and driver.
  1. Click **Done** to save the database connection settings.

{% endlist %}




## Connecting from Yandex WebSQL {#websql}

You can use [Yandex WebSQL](../../../websql/index.md) to send SQL queries to Managed Service for PostgreSQL cluster databases.

WebSQL is a Yandex Cloud service that enables you to connect to managed database clusters, work with databases, tables, and schemas, and run queries. This service runs in the browser, requires no extra login, and simplifies work by providing hints for SQL commands.

To connect from WebSQL, activate the **WebSQL access** option in the cluster settings. You can enable it when [creating](../cluster-create.md) or [updating](../update.md#change-additional-settings) a cluster.

The Managed Service for PostgreSQL cluster automatically creates a [Connection Manager](../../../metadata-hub/concepts/connection-manager.md) connection for each database user, enabling database access from WebSQL. You can also [create a new connection](../../../websql/operations/create-connection.md#connect-cluster) if needed.

To connect to the database from WebSQL:

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the folder containing your cluster.
  1. Navigate to **Managed Service for&nbsp;PostgreSQL**.
  1. Click the cluster name and navigate to the **WebSQL** tab.
  1. Select the required connection.

      The connection will open in WebSQL. To open the query editor, select the required database. 
      
      Check [this PostgreSQL](https://www.postgresql.org/docs/current/sql.html) reference for supported queries.

      {% note info %}
      
      You cannot use SQL statements to perform operations requiring superuser privileges.
      
      {% endnote %}
    
{% endlist %}

For more information on WebSQL, see [this guide](../../../websql/operations/index.md).




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

Connection testing was performed for [pgAdmin 4](https://www.pgadmin.org) version 7.0 on Ubuntu 20.04.

Connections from pgAdmin 4 are only permitted to publicly accessible cluster hosts and require an [SSL certificate](index.md#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**: [Master FQDN](fqdn.md#fqdn-master) or regular host [FQDN](../../concepts/network.md#hostname).
    * **Port**: `6432`.
    * **Maintenance database**: Target database name.
    * **Username**: Username used to establish the 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.

Your cluster will appear in the server list located in the navigation menu.


## Connecting from Looker Studio {#connection-google-looker}


Connections from [Looker Studio](https://lookerstudio.google.com/overview) are only permitted to publicly accessible hosts.


1. Save the `CA.pem` [server certificate](https://storage.yandexcloud.net/cloud-certs/CA.pem) to a local directory.
1. In the same directory, generate a client certificate with a private key:

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

    During certificate creation, the program will ask you to modify several settings. Press **Enter** to keep the default values.

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

1. On the [Looker Studio](https://lookerstudio.google.com/navigation/reporting) navigation page, select **Create** → **Data source**.
1. Select PostgreSQL.
1. Fill out the fields as follows:

    * **Host name or IP address**: [Master FQDN](fqdn.md#fqdn-master) or regular host [FQDN](../../concepts/network.md#hostname).
    * **Port**: `6432`.
    * **Database**: DB to connect to.
    * **Username**: Username used to establish the connection.
    * **Password**: User password.

1. Check **Enable SSL** and **Enable client authentication**.
1. Specify the certificate files and the client private key in the appropriate fields:

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

1. Click **Authenticate**.

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

To connect to a Managed Service 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
    ```

    See the Yandex Serverless Containers connection example in [this tutorial](../../../serverless-containers/tutorials/pg-connect.md).

- 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 %}