[Yandex Cloud documentation](../../../index.md) > [Yandex DataSphere](../../index.md) > [Step-by-step guides](../index.md) > Connecting to data sources > Connecting to a PostgreSQL database

# Connecting to a PostgreSQL database

Managed Service for PostgreSQL enables deploying and maintaining [PostgreSQL](https://www.postgresql.org/) server clusters in the Yandex Cloud infrastructure.

## Getting started {#before-begin}

1. [Create](../../../managed-postgresql/operations/cluster-create.md) a new Managed Service for PostgreSQL cluster with public host access. You can also use an existing cluster with publicly accessible hosts.
1. [Configure](../../../managed-postgresql/operations/connect/index.md#configuring-security-groups) cluster security groups.
1. Open the DataSphere project:
   
   1. Select the project in your community or on the DataSphere [home page](https://datasphere.yandex.cloud) in the **Recent projects** tab.
   1. Click **Open project in JupyterLab** and wait for the loading to complete.
   1. Open the notebook tab.

## Connecting to a host {#connect-to-host}

{% list tabs group=connection %}

- Connecting with SSL {#with-ssl}

  To connect to Managed Service for PostgreSQL cluster hosts:

  1. Get an SSL certificate. To do this, enter this command in a notebook cell:

      ```bash
      #!:bash
      mkdir ~/.postgresql
      wget "https://storage.yandexcloud.net/cloud-certs/CA.pem" -O ~/.postgresql/root.crt && \
      chmod 0600 ~/.postgresql/root.crt
      ```

  1. Establish a database connection. To do this, enter this command in a notebook cell:

      ```python
      %pip install psycopg2-binary
      import psycopg2
      conn = psycopg2.connect("""
          host=<PostgreSQL_host_FQDN>
          port=6432
          sslmode=verify-full
          dbname=<DB_name>
          user=<DB_username>
          password=<DB_user_password>
          target_session_attrs=read-write
      """)
      q = conn.cursor()
      q.execute('SELECT version()')
      print(q.fetchone())
      ```

      If you succeed connecting to the cluster, you will get the PostgreSQL version in response to your test request:

      ```text
      ('PostgreSQL 14.6 (Ubuntu 14.6-201-yandex.52665.7e82983c2c) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit',)
      ```

- Connecting without SSL {#without-ssl}

  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.

  1. [Configure your project](../projects/update.md). To do this, add or modify the following fields on the project edit page as appropriate:

     * **Default folder** hosting the deployed Managed Service for PostgreSQL cluster.
     * **Service account** with the `managed-postgresql.editor` [role](../../../managed-postgresql/security/index.md#required-roles) or higher.
     * **Subnet** where the database host resides. The subnet must be in the same [availability zone](../../../overview/concepts/geo-scope.md) where you created the community.

       {% note info %}

       To enable internet access, [specify a subnet with a NAT gateway](../../../vpc/operations/create-nat-gateway.md).

       {% endnote %}

  1. Establish a database connection. To do this, enter this command in a notebook cell:

      ```python
      %pip install psycopg2-binary
      import psycopg2

      conn = psycopg2.connect("""
          host=rc1a-<PostgreSQL_cluster_ID>.mdb.yandexcloud.net
          port=6432
          sslmode=disable
          dbname=<DB_name>
          user=<DB_username>
          password=<DB_user_password>
          target_session_attrs=read-write
      """)

      q = conn.cursor()
      q.execute('SELECT version()')

      print(q.fetchone())

      conn.close()
      ```

      If you succeed connecting to the cluster, you will get the PostgreSQL version in response to your test request:

      ```text
      ('PostgreSQL 14.6 (Ubuntu 14.6-201-yandex.52665.7e82983c2c) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit',)
      ```

{% endlist %}