You are viewing documentation for an older version.

Click here to view the latest documentation.

Warehouse Permissions

RudderStack supports Snowflake, Redshift, Databricks, and BigQuery for creating unified user profiles.

To read and write data to the warehouse, RudderStack requires specific warehouse permissions as explained in the following sections.

Keeping separate schemas for projects running via CLI and web is recommended. This way projects run from the CLI will never risk overwriting your production data.

Snowflake

Snowflake uses a combination of DAC and RBAC models for access control. However, RudderStack chooses an RBAC-based access control mechanism as multiple users can launch the Profile Builder CLI.

Also, it is not ideal to tie the result of an individual user run with that user. Hence, it is recommended to create a generic role (for example, PROFILES_ROLE) with the following privileges:

• Read access to all the inputs to the model (can be shared in case of multiple schemas/tables).
• Write access to the schemas and common tables as the PB project creates material (output) tables.

If you want to access any material created from the project run, the role (PROFILES_ROLE) must also have read access to all of those schemas.

Below are some sample commands which grant the required privileges to the role (PROFILES_ROLE) in a Snowflake warehouse:

-- Create role
CREATE ROLE PROFILES_ROLE;
SHOW ROLES; -- To validate

-- Create user
CREATE USER PROFILES_TEST_USER PASSWORD='<StrongPassword>' DEFAULT_ROLE='PROFILES_ROLE';
SHOW USERS; -- To validate

-- Grant role to user and database
GRANT ROLE PROFILES_ROLE TO USER PROFILES_TEST_USER;
GRANT USAGE ON DATABASE YOUR_RUDDERSTACK_DB TO ROLE PROFILES_ROLE;

-- Create separate schema for Profiles and grant privileges to role
CREATE SCHEMA YOUR_RUDDERSTACK_DB.RS_PROFILES;
GRANT ALL PRIVILEGES ON SCHEMA YOUR_RUDDERSTACK_DB.RS_PROFILES TO ROLE PROFILES_ROLE;
GRANT USAGE ON WAREHOUSE RUDDER_WAREHOUSE TO ROLE PROFILES_ROLE;
GRANT USAGE ON SCHEMA YOUR_RUDDERSTACK_DB.EVENTSSCHEMA TO ROLE PROFILES_ROLE;

For accessing input sources, you can individually grant select on tables/views, or give blanket grant to all in a schema.

-- Assuming we want read access to tables/views in schema EVENTSSCHEMA
GRANT SELECT ON ALL TABLES IN SCHEMA YOUR_RUDDERSTACK_DB.RS_PROFILES TO PROFILES_ROLE;
GRANT SELECT ON FUTURE TABLES IN SCHEMA YOUR_RUDDERSTACK_DB.RS_PROFILES TO PROFILES_ROLE;
GRANT SELECT ON ALL VIEWS IN SCHEMA YOUR_RUDDERSTACK_DB.RS_PROFILES TO PROFILES_ROLE;
GRANT SELECT ON FUTURE VIEWS IN SCHEMA YOUR_RUDDERSTACK_DB.RS_PROFILES TO PROFILES_ROLE;

-- Assuming we want read access to tracks and identifies tables in schema EVENTSSCHEMA
GRANT SELECT ON TABLE YOUR_RUDDERSTACK_DB.RS_PROFILES.TRACKS TO PROFILES_ROLE;
GRANT SELECT ON TABLE YOUR_RUDDERSTACK_DB.RS_PROFILES.IDENTIFIES TO PROFILES_ROLE;

Redshift

Suppose the inputs/edge sources are in a single schema website_eventstream and the name of the newly created Profiles user is rudderstack_admin. In this case, the requirements are as follows:

• A separate schema rs_profiles (to store all the common and output tables).
• The rudderstack_admin user should have all the privileges on the above schema and the associated tables.
• The rudderstack_admin user should have USAGE privilege on schemas that have the edge sources and input tables (website_eventstream) and read (SELECT) privileges on specific tables as well. This privilege can extend to the migration schema and other schemas from where data from warehouses comes in.
• The rudderstack_admin user should have privileges to use plpythonu to create some UDFs.

The sample commands are as follows:

CREATE USER rudderstack_admin WITH PASSWORD '<strong_unique_password>';
CREATE SCHEMA rs_profiles;
GRANT ALL ON SCHEMA "rs_profiles" TO rudderstack_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA "rs_profiles" TO rudderstack_admin;
GRANT USAGE ON SCHEMA "website_eventstream" TO rudderstack_admin;
GRANT USAGE ON LANGUAGE plpythonu TO rudderstack_admin;

GRANT SELECT ON ALL TABLES IN SCHEMA "website_eventstream" TO rudderstack_admin;

GRANT SELECT ON TABLE "<YOUR_SCHEMA>"."<YOUR_TABLE>" TO rudderstack_admin;

Supported inputs

RudderStack supports the following input types for Redshift warehouse/serverless:

• Redshift cluster with DC2 type nodes with following types as inputs:
  • Redshift internal tables
  • External schema and tables only for inputs (not supported as output)
  • CSV files stored on S3 as inputs
• Redshift cluster with RA3 type nodes with following types as inputs:
  • Redshift internal tables
  • External schema and tables only for inputs (not supported as output)
  • CSV files stored on S3 as inputs
  • Cross DB input tables
• Redshift serverless with following types as inputs:
  • Redshift internal tables
  • External schema and tables (not supported as output)
  • CSV files stored on S3 as inputs
  • Cross DB input tables RudderStack also supports various authentication mechanisms to authenticate the user running the Profiles project. Refer Redshift warehouse connection for more information.

Databricks

1. Open the Databricks UI.
2. Create a new user.
3. Reuse an existing catalog or create a new one by clicking Create Catalog.
4. Grant USE SCHEMA privilege on the catalog.
5. Create a separate schema to write objects created by RudderStack Profiles.
6. Grant all privileges on this schema.
7. Grant privileges to access relevant schemas for the input tables. For example, if an input schema is in a schema named website_eventstream, then you can run the following commands to assign a blanket grant to all schemas or only specific tables/views referred in your Profiles project:

CREATE USER rudderstack_admin WITH PASSWORD <strong_unique_password>;
GRANT USE SCHEMA ON CATALOG <catalog name> TO rudderstack_admin;
CREATE SCHEMA RS_PROFILES;
GRANT ALL PRIVILEGES ON SCHEMA RS_PROFILES TO rudderstack_admin;
GRANT SELECT ON SCHEMA website_eventstream TO rudderstack_admin;

GRANT SELECT ON ALL TABLES IN SCHEMA "website_eventstream" TO rudderstack_admin;

GRANT SELECT ON TABLE public.input_table TO rudderstack_admin; 

BigQuery

For BigQuery, RudderStack recommends you to use a view instead of table for streaming data sets.

You must first assign the BigQuery Job User role to your service account. To do so:

1. Open the BigQuery UI (Google Cloud Console).
2. Select IAM & Admin > IAM from the left sidebar.
3. Click GRANT ACCESS to grant permissions to your service account.
4. Enter your service account email in the New principals field.
5. In the Assign roles section, select BigQuery Job User role from the role list.
6. Click Save.

Alternatively, you can add the following IAM Policy Binding:

{
  "bindings": [
    {
      "role": "roles/bigquery.jobUser",
      "members": [
        "serviceAccount:your-service-account@your-project.iam.gserviceaccount.com"
      ]
    }
  ]
}

Further, assign the dataset or project level roles to your service account:

{
  "bindings": [
    {
      "role": "roles/bigquery.dataViewer",
      "members": [
        "serviceAccount:your-service-account@your-project.iam.gserviceaccount.com"
      ],
      "condition": {
        "title": "Access to dataset1",
        "description": "Allow data viewing access to dataset1",
        "expression": "resource.name.startsWith('projects/your-project-id/datasets/dataset1')"
      }
    },
    {
      "role": "roles/bigquery.dataEditor",
      "members": [
        "serviceAccount:your-service-account@your-project.iam.gserviceaccount.com"
      ],
      "condition": {
        "title": "Access to dataset2",
        "description": "Allow data editing access to dataset2",
        "expression": "resource.name.startsWith('projects/your-project-id/datasets/dataset2')"
      }
    }
  ]
}

{
  "bindings": [
    {
      "role": "roles/bigquery.dataViewer",
      "members": [
        "serviceAccount:your-service-account@your-project.iam.gserviceaccount.com"
      ]
    },
    {
      "role": "roles/bigquery.dataEditor",
      "members": [
        "serviceAccount:your-service-account@your-project.iam.gserviceaccount.com"
      ]
    }
  ]
}

Questions? Contact us by email or on Slack