> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Accessing S3 data securely

> This article demonstrates how ClickHouse Cloud customers can leverage role-based access to authenticate with Amazon Simple Storage Service(S3) and access their data securely.

export const Image = ({img, alt, size}) => {
  return <Frame>
      <img src={img} alt={alt} />
    </Frame>;
};

This guide demonstrates how ClickHouse Cloud customers can leverage role-based access to authenticate with Amazon Simple Storage Service (S3) and access their data securely.
Before diving into the setup for secure S3 access, it is important to understand how this works. Below is an overview of how ClickHouse services can access private S3 buckets by assuming a role within customers' AWS account.

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/pb3p2qvhHWkIWhRw/images/cloud/security/secures3.png?fit=max&auto=format&n=pb3p2qvhHWkIWhRw&q=85&s=48d196f71544fc84ee9cc783f53b0280" size="lg" alt="Overview of Secure S3 Access with ClickHouse" width="1218" height="674" data-path="images/cloud/security/secures3.png" />

<br />

This approach allows customers to manage all access to their S3 buckets in a single place (the IAM policy of the assumed-role) without having to go through all of their bucket policies to add or remove access.
In the section below, you will learn how to set this up.

<h2 id="obtaining-the-clickhouse-service-iam-role-arn">
  Obtain the IAM role ARN of your ClickHouse service
</h2>

1. Login to your ClickHouse cloud account.

2. Select the ClickHouse service you want to create the integration

3. Select the **Settings** tab

4. Scroll down to the **Network security information** section at the bottom of the page

5. Copy the **Service role ID (IAM)** value belong to the service as shown below.

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/pb3p2qvhHWkIWhRw/images/cloud/security/secures3_arn.png?fit=max&auto=format&n=pb3p2qvhHWkIWhRw&q=85&s=8f9e668058fb087a51c04fc7c39f5bea" size="lg" alt="Obtaining ClickHouse service IAM Role ARN" border width="1222" height="254" data-path="images/cloud/security/secures3_arn.png" />

<h2 id="setting-up-iam-assume-role">
  Set up IAM assume role
</h2>

The IAM assume role can be setup in one of two ways:

* [Using CloudFormation stack](#option-1-deploying-with-cloudformation-stack)
* [Manually creating an IAM role](#option-2-manually-create-iam-role)

<h3 id="option-1-deploying-with-cloudformation-stack">
  Deploying with CloudFormation stack
</h3>

1. Login to your AWS Account in the web browser with an IAM user that has permission to create & manage IAM role.

2. Visit the following [CloudFormation URL](https://us-west-2.console.aws.amazon.com/cloudformation/home?region=us-west-2#/stacks/quickcreate?templateURL=https://s3.us-east-2.amazonaws.com/clickhouse-public-resources.clickhouse.cloud/cf-templates/secure-s3.yaml\&stackName=ClickHouseSecureS3) to populate the CloudFormation stack.

3. Enter (or paste) the **service role ID (IAM)** for your service that you obtained earlier into the input titled "ClickHouse Instance Roles"
   You can paste the service role ID exactly as it appears in Cloud console.

4. Enter your bucket name in the input titled "Bucket Names". If your bucket URL is `https://ch-docs-s3-bucket.s3.eu-central-1.amazonaws.com/clickhouseS3/` then the bucket name is `ch-docs-s3-bucket`.

<Note>
  Don't put the full bucket ARN but instead just the bucket name only.
</Note>

5. Configure the CloudFormation stack. Below is additional information about these parameters.

| Parameter                 |     Default Value    | Description                                                                                                                      |
| :------------------------ | :------------------: | :------------------------------------------------------------------------------------------------------------------------------- |
| RoleName                  | ClickHouseAccess-001 | The name of the new role that ClickHouse Cloud will use to access your S3 bucket.                                                |
| Role Session Name         |          \*          | Role Session Name can be used as a shared secret to further protect your bucket.                                                 |
| ClickHouse Instance Roles |                      | Comma-separated list of ClickHouse service IAM roles that can use this secure S3 integration.                                    |
| Bucket Access             |         Read         | Sets the level of access for the provided buckets.                                                                               |
| Bucket Names              |                      | Comma-separated list of bucket names that this role will have access to. **Note:** use the bucket name, not the full bucket ARN. |

6. Select the **I acknowledge that AWS CloudFormation might create IAM resources with custom names.** checkbox

7. Click the **Create stack** button at the bottom right

8. Make sure the CloudFormation stack completes with no error.

9. Select the newly created Stack then select the **Outputs** tab of the CloudFormation stack

10. Copy the **RoleArn** value for this integration, which is what you need to access your S3 bucket.

<Image img="https://mintcdn.com/private-7c7dfe99-fix-nav-issues/pb3p2qvhHWkIWhRw/images/cloud/security/secures3_output.png?fit=max&auto=format&n=pb3p2qvhHWkIWhRw&q=85&s=6b4b1606573053b0c679594c2ad8c9f1" size="lg" alt="CloudFormation stack output showing IAM Role ARN" border width="2020" height="422" data-path="images/cloud/security/secures3_output.png" />

<h3 id="option-2-manually-create-iam-role">
  Manually create IAM role
</h3>

1. Login to your AWS Account in the web browser with an IAM user that has permission to create & manage IAM role.

2. Browse to the IAM Service Console

3. Create a new IAM role with the following IAM & Trust policy. Replace `{ClickHouse_IAM_ARN}` with the IAM Role arn belong to your ClickHouse instance.

```json theme={null}
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "{ClickHouse_IAM_ARN}"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}
```

**IAM policy**

```json theme={null}
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "s3:GetBucketLocation",
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::{BUCKET_NAME}"
            ],
            "Effect": "Allow"
        },
        {
            "Action": [
                "s3:Get*",
                "s3:List*"
            ],
            "Resource": [
                "arn:aws:s3:::{BUCKET_NAME}/*"
            ],
            "Effect": "Allow"
        }
    ]
}
```

4. Copy the new **IAM Role Arn** after creation, which is what's needed to access your S3 bucket.

<h2 id="access-your-s3-bucket-with-the-clickhouseaccess-role">
  Access your S3 bucket with the ClickHouseAccess role
</h2>

ClickHouse Cloud allows you to specify `extra_credentials` as part of the S3 table function.
Below is an example of how to run a query using the newly created role copied from above.

```sql theme={null}
DESCRIBE TABLE s3('https://s3.amazonaws.com/BUCKETNAME/BUCKETOBJECT.csv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001'))
```

Below is an example query that uses the `role_session_name` as a shared secret to query data from a bucket.
If the `role_session_name` isn't correct, this operation will fail.

```sql theme={null}
DESCRIBE TABLE s3('https://s3.amazonaws.com/BUCKETNAME/BUCKETOBJECT.csv','CSVWithNames',extra_credentials(role_arn = 'arn:aws:iam::111111111111:role/ClickHouseAccessRole-001', role_session_name = 'secret-role-name'))
```

<Note>
  We recommend that your source S3 is in the same region as your ClickHouse Cloud Service to reduce on data transfer costs.
  For more information, refer to [S3 pricing](https://aws.amazon.com/s3/pricing/)
</Note>

<h2 id="advanced-action-control">
  Advanced action control
</h2>

For stricter access control, it's possible to restrict the bucket policy to only accept requests that originate from ClickHouse Cloud's VPC endpoints using the [`aws:SourceVpce` condition](https://docs.aws.amazon.com/AmazonS3/latest/userguide/example-bucket-policies-vpc-endpoint.html#example-bucket-policies-restrict-accesss-vpc-endpoint). To obtain the VPC endpoints for your ClickHouse Cloud region, open a terminal and run:

```bash theme={null}
# Replace <your-region> with your ClickHouse Cloud region
curl -s https://api.clickhouse.cloud/static-ips.json | jq -r '.aws[] | select(.region == "<your-region>") | .s3_endpoints[]'
```

Then, add a deny rule to the IAM policy with the returned endpoints:

```json theme={null}
{
        "Version": "2012-10-17",
        "Statement": [
            {
                "Sid": "VisualEditor0",
                "Effect": "Allow",
                "Action": [
                    "s3:List*",
                    "s3:Get*"
                ],
                "Resource": [
                    "arn:aws:s3:::{BUCKET_NAME}",
                    "arn:aws:s3:::{BUCKET_NAME}/*"
                ]
            },
            {
                "Sid": "VisualEditor3",
                "Effect": "Deny",
                "Action": [
                    "s3:GetObject"
                ],
                "Resource": "*",
                "Condition": {
                    "StringNotEquals": {
                        "aws:SourceVpce": [
                            "{ClickHouse VPC ID from your S3 region}",
                            "{ClickHouse VPC ID from your S3 region}",
                            "{ClickHouse VPC ID from your S3 region}"
                        ]
                    }
                }
            }
        ]
}
```

For more details on accessing endpoints for ClickHouse Cloud services, see [Cloud IP Addresses](/products/cloud/guides/data-sources/cloud-endpoints-api).