Accessing GCS data securely

This article demonstrates how ClickHouse Cloud customers can access their GCS data securely

This guide demonstrates how to securely authenticate with Google Cloud Storage (GCS) and access your data from ClickHouse Cloud.

Introduction

ClickHouse Cloud connects to GCS using HMAC (Hash-based Message Authentication Code) keys associated with a Google Cloud service account. This approach provides secure access to your GCS buckets without embedding credentials directly in your queries.

How it works:

1. You create a Google Cloud service account with appropriate GCS permissions
2. You generate HMAC keys for that service account
3. You provide these HMAC credentials to ClickHouse Cloud
4. ClickHouse Cloud uses these credentials to access your GCS buckets

This approach allows you to manage all access to GCS buckets through IAM policies on the service account, making it easier to grant or revoke access without modifying individual bucket policies.

Prerequisites

For following this guide you will need:

• An active ClickHouse Cloud service
• A Google Cloud project with Cloud Storage enabled
• Permissions to create service accounts and generate HMAC keys in your GCP project

Setup

1. Create a Google Cloud service account

   1. In the Google Cloud Console, navigate to IAM & Admin → Service Accounts

   2. Click Service accounts from the left-hand menu, then click Create service account:

   Enter a name and description for your service account, for example:

   Service account name: clickhouse-gcs-access (or your preferred name)
   Service account description: Service account for ClickHouse Cloud to access GCS buckets
   

   Click Create and continue

   Grant the service account the Storage Object User role:

   This role provides read and write access to GCS objects

   For read-only access, use Storage Object Viewer instead For more granular control, you can create a custom role

   Click Continue, then Done

   Make note of the service account email address:

2. Grant bucket access to the service account

   You can grant access at either the project level or individual bucket level.

   Option 1: Grant access to specific buckets (recommended)

   1. Navigate to Cloud Storage → Buckets
   2. Click on the bucket you want to grant access to
   3. Go to the Permissions tab
   4. Under “Permissions” click Grant access for the principal created in the previous steps
   5. In the “New principals” field, enter your service account email
   6. Select the appropriate role:

   • Storage Object User for read/write access
   • Storage Object Viewer for read-only access

   7. Click Save
   8. Repeat for any additional buckets

   Option 2: Grant project-level access

   1. Navigate to IAM & Admin → IAM
   2. Click Grant access
   3. Enter your service account email in the New principals field
   4. Select Storage Object User (or Storage Object Viewer for read-only)
   5. Click SAVE

   Security best practice

   Grant access only to the specific buckets that ClickHouse needs to access, rather than project-wide permissions.

3. Generate HMAC keys for the service account

   Navigate to Cloud Storage → Settings → Interoperability:

   If you don’t see an “Access keys” section, click Enable interoperability access

   Under “Access keys for service accounts”, click Create a key for a service account:

   Select the service account you created earlier (e.g [e-mail])

   Click Create key:

   The HMAC key will be displayed. Save both the Access Key and Secret immediately - you won’t be able to view the secret again.

   Example keys are shown below:

   Access Key: GOOG1EF4YBJVNFQ2YGCP3SLV4Y7CMFHW7HPC6EO7RITLJDDQ75639JK56SQVD
   Secret: nFy6DFRr4sM9OnV6BG4FtWVPR25JfqpmcdZ6w9nV
   

   Important

   Store these credentials securely. The secret can’t be retrieved again after this screen is closed. You will need to generate new keys if you lose the secret.

   Use HMAC keys with ClickHouse Cloud

   Now you can use the HMAC credentials to access GCS from ClickHouse Cloud. For this, use the GCS table function:

   SELECT *
   FROM gcs(
       'https://storage.googleapis.com/clickhouse-docs-example-bucket/epidemiology.csv',
       'GOOG1E...YOUR_ACCESS_KEY',
       'YOUR_SECRET_KEY',
       'CSVWithNames'
   );
   

   Use wildcards for multiple files:

   SELECT *
   FROM gcs(
   'https://storage.googleapis.com/clickhouse-docs-example-bucket/*.parquet',
   'GOOG1E...YOUR_ACCESS_KEY',
   'YOUR_SECRET_KEY',
   'Parquet'
   );
   

   HMAC authentication in ClickPipes for GCS

   ClickPipes uses HMAC (Hash-based Message Authentication Code) keys to authenticate with Google Cloud Storage.

   When setting up a GCS ClickPipe:

   1. Select Credentials under Authentication method during ClickPipe setup
   2. Provide HMAC credentials obtained in the previous steps

   Service account authentication isn’t currently supported - you must use HMAC keys The GCS bucket URL must use the format: https://storage.googleapis.com/<bucket>/<path> (not gs://)

   The HMAC keys must be associated with a service account that has the roles/storage.objectViewer role, which includes:

   • storage.objects.list: to list objects in the bucket
   • storage.objects.get: to fetch/read objects

Best practices

Use separate service accounts for different environments

Create separate service accounts for development, staging, and production environments. For example:

• [e-mail]
• [e-mail]
• [e-mail]

This allows you to easily revoke access for a specific environment without affecting others.

Apply least-privilege access

Grant only the minimum required permissions:

• Use Storage Object Viewer for read-only access
• Grant access to specific buckets rather than project-wide
• Consider using bucket-level conditions to restrict access to specific paths

Rotate HMAC keys regularly

Implement a key rotation schedule:

• Generate new HMAC keys
• Update ClickHouse configurations with new keys
• Verify functionality with new keys
• Delete old HMAC keys

Google Cloud doesn’t enforce HMAC key expiration, so you must implement your own rotation policy.

Monitor access with Cloud Audit Logs

Enable and monitor Cloud Audit Logs for Cloud Storage:

1. Navigate to IAM & Admin → Audit Logs
2. Find Cloud Storage in the list
3. Enable Admin Read, Data Read, and Data Write logs
4. Use these logs to monitor access patterns and detect anomalies