[Yandex Cloud documentation](../../index.md) > [Tutorials](../index.md) > [Building a data platform](index.md) > Using a Yandex Lockbox secret in a PySpark job to connect to Yandex Managed Service for PostgreSQL

# Using a Yandex Lockbox secret in a PySpark job to connect to Yandex Managed Service for PostgreSQL

# Using a Yandex Lockbox secret in a PySpark job to connect to Yandex Managed Service for PostgreSQL

You can use a [Yandex Lockbox](../../lockbox/concepts/secret.md) secret to connect to a [Yandex Managed Service for PostgreSQL](../../managed-postgresql/index.md) cluster from a PySpark job in [Yandex Managed Service for Apache Spark™](../../managed-spark/index.md). To do this, grant the Yandex Managed Service for Apache Spark™ cluster [service account](../../iam/concepts/users/service-accounts.md) access to the secret. Yandex Connection Manager creates the secret automatically when creating the Managed Service for PostgreSQL user.

For the PySpark job, we use a Python script stored in a Yandex Object Storage bucket. The script retrieves the user password from the secret and uses it to connect to the Managed Service for PostgreSQL cluster.

To get information from the secret and connect to the Managed Service for PostgreSQL cluster:

1. [Set up your infrastructure](#infra).
1. [Configure permissions for access to the user's secret](#set-up-roles).
1. [Prepare a PySpark job](#prepare-a-job).
1. [Run the job and check the result](#check-out).

If you no longer need the resources you created, [delete them](#clear-out).

## Getting started {#before-you-begin}

Sign up for Yandex Cloud and create a [billing account](../../billing/concepts/billing-account.md):
1. Navigate to the [management console](https://console.yandex.cloud) and log in to Yandex Cloud or create a new account.
1. On the **[Yandex Cloud Billing](https://center.yandex.cloud/billing/accounts)** page, make sure you have a billing account linked and it has the `ACTIVE` or `TRIAL_ACTIVE` [status](../../billing/concepts/billing-account-statuses.md). If you do not have a billing account, [create one](../../billing/quickstart/index.md) and [link](../../billing/operations/pin-cloud.md) a cloud to it.

If you have an active billing account, you can create or select a [folder](../../resource-manager/concepts/resources-hierarchy.md#folder) for your infrastructure on the [cloud page](https://console.yandex.cloud/cloud).

[Learn more about clouds and folders here](../../resource-manager/concepts/resources-hierarchy.md).


### Required paid resources {#paid-resources}

* Managed Service for Apache Spark™ cluster: computing resources allocated to the hosts (see [Managed Service for Apache Spark™ pricing](../../managed-spark/pricing.md)).
* Managed Service for PostgreSQL cluster: computing resources allocated to hosts, storage and backup size (see [Managed Service for PostgreSQL pricing](../../managed-postgresql/pricing.md)).
* Public IP addresses if public access is enabled for cluster hosts (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).
* Object Storage bucket: use of storage, data operations (see [Object Storage pricing](../../storage/pricing.md)).
* NAT gateway: hourly use of the gateway and its outgoing traffic (see [Virtual Private Cloud pricing](../../vpc/pricing.md)).

There is no charge for using Yandex Lockbox secrets created with Connection Manager.


## Set up your infrastructure {#infra}

1. [Create a service account](../../iam/operations/sa/create.md#create-sa) named `spark-agent` and assign it the following roles:
  
    * [managed-spark.integrationProvider](../../managed-spark/security.md#managed-spark-integrationProvider): For Yandex Managed Service for Apache Spark™ integration with other services.
    * [storage.editor](../../storage/security/index.md#storage-editor): For access to the PySpark job file in the Object Storage bucket.
  
1. [Create a bucket](../../storage/operations/buckets/create.md). 
1. [Grant](../../storage/operations/buckets/edit-acl.md) the `READ` permission to the `spark-agent` service account for the new bucket.

1. [Create a cloud network](../../vpc/operations/network-create.md) named `spark-network`.

    This will automatically create three subnets in different availability zones.

1. In `spark-network`, [create a security group](../../vpc/operations/security-group-create.md) named `spark-sg` for the Yandex Managed Service for Apache Spark™ cluster to allow outgoing TCP connections on:
    
    * Port `6432` to connect to PostgreSQL.
    * Port `443` to download Maven packages.

1. In `spark-network`, create a security group named `pg-sg` for the Managed Service for PostgreSQL cluster to allow incoming TCP connections on port `6432`.

1. [Create a Yandex Managed Service for Apache Spark™ cluster](../../managed-spark/operations/cluster-create.md) with the following settings:

    * **Service account**: `spark-agent`
    * **Network**: `spark-network`
    * **Security group**: `spark-sg`

1. [Set up a NAT gateway](../../vpc/operations/create-nat-gateway.md) for the subnet where the Yandex Managed Service for Apache Spark™ cluster was created. The NAT gateway is required to download the PostgreSQL JDBC driver from the Maven repository.

1. [Create a Managed Service for PostgreSQL](../../managed-postgresql/operations/cluster-create.md) cluster with the following settings:

    * **Network**: `spark-network`
    * **Security group**: `pg-sg`

## Configure access permissions for the Managed Service for PostgreSQL user secret {#set-up-roles}

{% list tabs group=instructions %}

- Management console {#console}

  1. In the [management console](https://console.yandex.cloud), select the folder where you created the infrastructure.
  1. Navigate to **Managed Service for PostgreSQL**.
  1. Click the name of your cluster and select the **Users** tab.
  1. Click the connection ID next to the user you need.
    
      This will take you to the Connection Manager connection page for the selected user.
  
  1. On the page that opens, click the secret ID.

      This will open the page of the Yandex Lockbox secret storing the PostgreSQL user password.

  1. On the left-hand panel, select ![image](../../_assets/console-icons/persons.svg) **Access bindings** and click **Assign roles**.
  1. Select the `spark-agent` service account you want to grant secret access to.
  1. Click ![image](../../_assets/console-icons/plus.svg) **Add role** and select `lockbox.payloadViewer`.
  1. Click **Save**.

- CLI {#cli}

  If you do not have the Yandex Cloud CLI yet, [install and initialize it](../../cli/quickstart.md#install).

  The folder used by default is the one specified when [creating](../../cli/operations/profile/profile-create.md) the CLI profile. To change the default folder, use the `yc config set folder-id <folder_ID>` command. You can also specify a different folder for any command using `--folder-name` or `--folder-id`. If you access a resource by its name, the search will be limited to the default folder. If you access a resource by its ID, the search will be global, i.e., through all folders based on access permissions.

  To configure access to the Managed Service for PostgreSQL user secret:
  
  1. Get the secret ID from the Connection Manager connection:

      ```bash
      yc metadata-hub connection-manager connection list \
        --mdb-cluster-id <PostgreSQL_cluster_ID>
      ```

      You can get the cluster ID with the [list of clusters in the folder](../../managed-postgresql/operations/cluster-list.md).
  
  1. Configure access to the user secret:

      ```bash
      yc lockbox secret add-access-binding <secret_ID> \
        --role lockbox.payloadViewer \
        --service-account-name spark-agent 
      ```

      The `spark-agent` service account will get access to the Managed Service for PostgreSQL user secret.

{% endlist %}

## Prepare a PySpark job {#prepare-a-job}

1. Create a local file named `job-mpg-connection-with-secret.py` and paste the following script to it:

    {% cut "job-mpg-connection-with-secret.py" %}

    ```python
    import json
    import os
    import urllib.request
    from pyspark.sql import SparkSession
    
    def get_secret(secret_id: str) -> dict:
        """Getting a secret from Yandex Lockbox."""
        # Getting an IAM token via metadata service
        metadata_host = os.environ.get("YC_METADATA_ADDR", "169.254.169.254")
        token_url = f"http://{metadata_host}/computeMetadata/v1/instance/service-accounts/default/token"
    
        req = urllib.request.Request(token_url)
        req.add_header("Metadata-Flavor", "Google")
        with urllib.request.urlopen(req, timeout=10) as resp:
            token = json.loads(resp.read())["access_token"]
    
        # Getting a Yandex Lockbox secret
        secret_url = f"https://payload.lockbox.api.cloud.yandex.net/lockbox/v1/secrets/{secret_id}/payload"
        req = urllib.request.Request(secret_url)
        req.add_header("Authorization", f"Bearer {token}")
        with urllib.request.urlopen(req, timeout=30) as resp:
            payload = json.loads(resp.read())
            return {e["key"]: e["textValue"] for e in payload["entries"]}
    
    
    # Using the secret retrieval function
    secrets = get_secret("<secret_ID>")
    
    # Initializing a Spark session
    spark = SparkSession.builder.appName("db-reader").getOrCreate()
    
    # Connecting to a Managed Service for PostgreSQL cluster
    df = spark.read \
        .format("jdbc") \
        .option("url", f"jdbc:postgresql://<host_FQDN>:6432/<DB_name>") \
        .option("driver", "org.postgresql.Driver") \
        .option("user", "<username>") \
        .option("password", secrets["postgresql_password"]) \
        .option("dbtable", "<table_name>") \
        .load()
    
    # Showing the first 20 rows of the table
    df.show()
    ```

    {% endcut %}

1. In the script file, specify the following settings:
    
    * Secret ID.
    * [Host FQDN](../../managed-postgresql/operations/connect/fqdn.md) to connect to the Managed Service for PostgreSQL cluster.
    * Database name.
    * Table name.

## Run the job and check the result {#check-out}

{% list tabs group=instructions %}

- Management console {#console}

  1. [Upload](../../storage/operations/objects/upload.md#simple) the `job-mpg-connection-with-secret.py` file to the bucket.
  1. In the Yandex Managed Service for Apache Spark™ cluster, [create a job](../../managed-spark/operations/jobs-pyspark.md) with the following settings:
      
      * **Job type**: **PySpark**
      * **Main python file**: `s3a://<bucket_name>/job-mpg-connection-with-secret.py`
      * **Packages**: `org.postgresql:postgresql:42.7.3`

  1. Wait for the PySpark job you created to change its status to **Done**.
  1. [Get job execution logs](../../managed-spark/operations/jobs-pyspark.md#get-logs).

      The job logs will display rows from the table specified in the script.

- CLI {#cli}
  
  1. Upload the `job-mpg-connection-with-secret.py` file to the bucket:

      ```bash
      yc storage s3api put-object \
        --body <local_file_path> \
        --bucket <bucket_name> \
        --key "job-mpg-connection-with-secret.py"
      ```
  
  1. Run the job:

      ```bash
      yc managed-spark job create-pyspark \
        --cluster-id <cluster_ID> \
        --name "<job_name>" \
        --main-python-file-uri s3a://<bucket_name>/job-mpg-connection-with-secret.py \
        --packages org.postgresql:postgresql:42.7.3
      ```

      You can get the cluster ID with the [list of clusters in the folder](../../managed-spark/operations/cluster-list.md#list-clusters).

      The command will return the job ID. Save it as you will need it to get job execution logs.

  1. Get job execution logs:

      ```bash
      yc managed-spark job log <job_ID> \
        --cluster-id <cluster_ID>
      ```

      The job logs will display rows from the table specified in the script.

{% endlist %}
 
## Delete the resources you created {#clear-out}

Some resources are not free of charge. Delete the resources you no longer need to avoid paying for them:

1. [Yandex Managed Service for Apache Spark™ cluster](../../managed-spark/operations/cluster-delete.md).
1. [Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/cluster-delete.md).
1. [Object Storage bucket](../../storage/operations/buckets/delete.md). Before deleting the bucket, [delete all its objects](../../storage/operations/objects/delete.md).
1. [NAT gateway](../../vpc/operations/delete-nat-gateway.md).