[Yandex Cloud documentation](../../index.md) > [Yandex Managed Service for Apache Airflow™](../index.md) > [Step-by-step guides](index.md) > Storing artifacts in Yandex Lockbox > Storing Apache Airflow™ connections in Yandex Lockbox

# Storing Apache Airflow™ connections in Yandex Lockbox

When working with Yandex Managed Service for Apache Airflow™, you can use [Yandex Lockbox](../../lockbox/index.md) to store artifacts that can be used in DAG files: connections, variables, and configuration data. Yandex Lockbox integrates into Managed Service for Apache Airflow™ via the [Yandex Lockbox Secret Backend](https://airflow.apache.org/docs/apache-airflow-providers-yandex/stable/secrets-backends/yandex-cloud-lockbox-secret-backend.html) provider. As a result, access to the secret storage is configured automatically.

Using a [directed acyclic graph (DAG)](../concepts/index.md#about-the-service), you can load a connection from Yandex Lockbox and run a `SELECT 1;` SQL query to a database in a Yandex Managed Service for PostgreSQL cluster. Data for connecting to the DB is stored in Yandex Lockbox and automatically inserted into the graph.

{% note tip %}

Clusters running Apache Airflow™ older than 3.0 use `apache-airflow-providers-postgres` 5.13.1 as a default provider. If using a newer provider version, use SQLExecuteQueryOperator instead of PostgresOperator. For more information, see [this official guide](https://airflow.apache.org/docs/apache-airflow-providers-postgres/6.0.0/operators/postgres_operator_howto_guide.html).

{% endnote %}

## Getting started {#before-begin}

1. [Create a Managed Service for PostgreSQL cluster](../../managed-postgresql/operations/cluster-create.md#create-cluster) with the following parameters:
   * **DB name**: `db1`
   * **Username**: `user1`
   * **Password**: `user1-password`

1. [Create](../../storage/operations/buckets/create.md) a Yandex Object Storage bucket to store the DAG file in.

1. [Configure the Managed Service for Apache Airflow™ cluster](cluster-update.md):

   1. Enable **Use Lockbox Secret Backend** to use [Yandex Lockbox](../../lockbox/concepts/index.md) secrets to [store Apache Airflow™ configuration data, variables, and connection parameters](../concepts/impersonation.md#lockbox-integration).
   1. Under **Dependencies**, add the `apache-airflow-providers-postgres` pip package.

      {% note warning %}

      You need to install a pip package for clusters with Apache Airflow™ version 3.0 or higher. This package comes installed by default on clusters with Apache Airflow™ versions below 3.0.

      {% endnote %}
      
   1. Under **DAG file storage**, select the Object Storage bucket you created earlier. Your DAG file will be fetched from it.

1. Issue the `lockbox.payloadViewer` [role](../../lockbox/security/index.md#lockbox-payloadViewer) to your service account.

   {% note info %}

   There is no need to assign the `lockbox.payloadViewer` role for the whole folder. It is enough to [assign it for a specific Yandex Lockbox](../../lockbox/operations/secret-access.md) secret once you [create it](#create-lockbox-secret).

   {% endnote %}

## Create a Yandex Lockbox secret {#create-lockbox-secret}

For the Apache Airflow™ cluster to work correctly, your Yandex Lockbox secret's name must have this format: `airflow/<artifact_type>/<artifact_ID>`, where:
   * `<artifact_type>`: Decides what data will be stored in the secret. The allowed values are:
     * `connections`: Connections.
     * `variables`: Variables.
     * `config`: Configuration data.
   * `<artifact_ID>`: ID to use to access the artifact in Apache Airflow™.

[Create a Yandex Lockbox secret](../../lockbox/operations/secret-create.md) with the following parameters:
   * **Name**: `airflow/connections/pg1`.
   * **Secret type**: `Custom`.
   * **Key**: `conn`.
   * **Value**: Select **Text** and specify the following contents:

      ```json
      {
        "conn_type": "postgres",
        "host": "<PostgreSQL_cluster_host_FQDN>",
        "port": 6432,
        "schema": "db1",
        "login": "user1",
        "password": "user1-password"
      }
      ```

The secret will store the data to connect to the database in the Managed Service for PostgreSQL cluster.

For more information on how to learn the FQDN of a PostgreSQL cluster host, see [FQDNs of PostgreSQL hosts](../../managed-postgresql/operations/connect/fqdn.md).

## Prepare the DAG file and run the graph {#dag}

1. Create a local file named `test_lockbox_connection.py` and paste the following script to it:

   {% list tabs group=instructions %}
   
   - Apache Airflow™ version below 3.0 {#version-2}

     ```python
     from airflow import DAG
     from airflow.providers.postgres.operators.postgres import PostgresOperator
     from datetime import datetime


     with DAG(
       dag_id='test_lockbox_connection',
       start_date=datetime(2024, 4, 19),
       schedule="@once",
       catchup=False,
     ) as dag:
       check_conn = PostgresOperator(
           task_id="check_conn",
           postgres_conn_id='pg1',
           sql="SELECT 1;",
       )
     ```

   - Apache Airflow™ version 3.0 or higher {#version-3}
   
     ```python
     from airflow import DAG
     from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator
     from datetime import datetime


     with DAG(
       dag_id='test_lockbox_connection',
       start_date=datetime(2024, 4, 19),
       schedule="@once",
       catchup=False,
     ) as dag:
       check_conn = SQLExecuteQueryOperator(
           task_id="check_conn",
           conn_id='pg1',
           sql="SELECT 1;",
       )
     ```
   
   {% endlist %}

1. Upload the `test_lockbox_connection.py` DAG file to the bucket you created earlier. This will automatically create a graph with the same name in the Apache Airflow™ web interface.
1. [Open the Apache Airflow™ web interface](af-interfaces.md#web-gui).
1. Make sure there is a new graph named `test_lockbox_connection` in the **DAGs** section.

   It may take a few minutes to load a DAG file from the bucket.

1. To run the graph, click ![image](../../_assets/managed-airflow/trigger-dag.png =18x) in the line with its name.

## Check the result {#check-result}

To check the result in the Apache Airflow™ web interface:

{% list tabs group=instructions %}
   
- Apache Airflow™ version below 3.0 {#version-2}

  1. In the **DAGs** section, click the `test_lockbox_connection` graph.
  1. Go to the **Graph** section.
  1. Select **check_conn**.
  1. Go to **Logs**.
  1. Make sure the logs contain the `Rows affected: 1` line. This means the query was successful.

- Apache Airflow™ version 3.0 or higher {#version-3}

  1. In the **DAGs** section, click the `test_lockbox_connection` graph.
  1. Go to **Tasks**.
  1. Select **check_conn**.
  1. Go to **Tasks Instances**.
  1. Select the task instance.
  1. The **Logs** section will open.
  1. Make sure the logs contain the `Rows affected: 1` line. This means the query was successful.

{% endlist %}