[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for Sharded PostgreSQL](../index.md) > [Step-by-step guides](index.md) > Connecting to a database

# Connecting to a Sharded PostgreSQL cluster

You can connect to Managed Service for Sharded PostgreSQL cluster hosts:

* Via the internet, if you configured public access for these hosts. You can only access such hosts over an SSL connection.

* From Yandex Cloud VMs located in the same [cloud network](../../vpc/concepts/network.md) For hosts without public access, SSL is not required to connect to them from these virtual machines.

You can connect to all [host types](../concepts/index.md): `INFRA`, `ROUTER`, `COORDINATOR`, PostgreSQL cluster hosts. The connection is established through port `6432`. In the case of `COORDINATOR` hosts and the Sharded PostgreSQL admin console, you must connect as the `spqr-console` user to the `spqr-console` database. You can use the admin console to configure sharding rules. For more information, see [this SPQR guide](https://pg-sharding.tech/welcome/get_started).


## Configuring security groups {#configuring-security-groups}

To connect to a cluster, [security groups](../../vpc/concepts/security-groups.md) must include rules allowing traffic from certain ports, IP addresses, or from other security groups.

Security group settings will vary depending on the connection method you choose:

{% list tabs group=connection_method %}

- Over the internet {#internet}

    [Configure all cluster security groups](../../vpc/operations/security-group-add-rule.md) to allow incoming traffic on port `6432` from any IP address. To do this, create the following inbound rule:

    * **Port range**: `6432`.
    * **Protocol**: `TCP`.
    * **Source**: `CIDR`.
    * **CIDR blocks**: `0.0.0.0/0`.

    If the shared cluster and shard security group has rules configured to connect the router to shard hosts, no additional configuration of security groups is required to access the cluster over the internet.

- From a Yandex Cloud VM {#cloud}

    1. [Configure all cluster security groups](../../vpc/operations/security-group-add-rule.md) to allow incoming traffic on port `6432` from your VM’s security group. To do this, create the following rule for incoming traffic in these groups:

        * **Port range**: `6432`.
        * **Protocol**: `TCP`.
        * **Source**: `Security group`.
        * **Security group**: If your cluster and VM share the same security group, select `Current` (`Self`). Otherwise, specify the VM security group.

    1. [Configure the VM security group](../../vpc/operations/security-group-add-rule.md) to allow VM connections and traffic between the VM and cluster hosts.

        For example, you can set the following rules for your VM:

        * For incoming traffic:
            * **Port range**: `22`.
            * **Protocol**: `TCP`.
            * **Source**: `CIDR`.
            * **CIDR blocks**: `0.0.0.0/0`.

            This rule allows inbound VM [connections](../../compute/operations/vm-connect/ssh.md#vm-connect) over SSH.

        * For outgoing traffic:
            * **Port range**: `0-65535`.
            * **Protocol**: `Any` (`Any`).
            * **Destination name**: `CIDR`.
            * **CIDR blocks**: `0.0.0.0/0`.

            This rule permits all outbound traffic, allowing you to install any necessary certificates and tools on your VM.

{% endlist %}

{% note info %}

You can specify more granular security group rules, such as allowing traffic only in specific subnets.

Make sure to properly configure security groups for all subnets where the cluster hosts will reside.

{% endnote %}


## Obtaining an SSL certificate {#get-ssl-cert}

Publicly accessible Sharded PostgreSQL hosts support only encrypted connections. To assess them, get an SSL certificate:

{% list tabs group=operating_system %}

- Linux (Bash)/macOS (Zsh) {#linux-macos}

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

   The certificate will be saved to the `~/.postgresql/root.crt` file.

- Windows (PowerShell) {#windows}

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

   The certificate will be saved to the `$HOME\.postgresql\root.crt` file.
   
   Your corporate security policies and antivirus software may block the certificate download. For more information, see [FAQ](../../managed-postgresql/qa/connection.md#get-ssl-error).

{% endlist %}

## Sharded PostgreSQL host FQDN {#fqdn}

To connect to a host, you need its fully qualified domain name (FQDN).

You can view the FQDN in the management console:

1. Navigate to the cluster page.
1. Navigate to **Hosts**.
1. Copy the **Host FQDN** column value.

## Connecting with 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=<host_FQDN> \
              port=6432 \
              sslmode=disable \
              dbname=<DB_name> \
              user=<username> \
              target_session_attrs=read-write"
        ```

        Where `target_session_attrs` defines the type of request to the host. For example, `read-write` enables both reading and writing. For more information, see [this SPQR guide](https://docs.pg-sharding.tech/routing/hints#__spqr__target_session_attrs).

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

    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=<host_FQDN> \
              port=6432 \
              sslmode=verify-full \
              dbname=<DB_name> \
              user=<username> \
              target_session_attrs=read-write"
        ```

        Where `target_session_attrs` defines the type of request to the host. For example, `read-write` enables both reading and writing. For more information, see [this SPQR guide](https://pg-sharding.tech/routing/hints#spqr-target-session-attrs).

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

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

The connection was tested for [pgAdmin 4](https://www.pgadmin.org) version 9.1 on Windows 10.

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**: [Host FQDN](#fqdn).
    * **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.

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