Skip to content

Redshift Spectrum

This topic describes how to configure access control for Redshift Spectrum PolicySync using Privacera Manager.

Privacera supports access control for Redshift Spectrum only on the following:

  • Create Database
  • Usage Schema

Prerequisites

The following prerequisites must be met to use the Redshift Spectrum:

  1. You will require an Amazon Redshift cluster and a SQL client connected to the cluster.

  2. The AWS Region in which the Amazon Redshift cluster and Amazon S3 bucket are located must be the same.

Configuration

Redshift Spectrum configuration is similar to Redshift configuration. For more information about Redshift configuration, see Redshift.

Getting started

Redshift Spectrum supports the creation of external tables within Redshift cluster in four simple steps:

  1. Create an IAM role for Amazon Redshift.

  2. Associate the IAM role with your Amazon Redshift cluster.

  3. Create an external schema and an external table.

  4. Query your data in Amazon S3.

Major Security Concern

Redshift does not support Access control lists (ACLs) on EXTERNAL TABLES; to gain access to the data (EXTERNAL TABLES), you must provide USAGE schema permission on the EXTERNAL SCHEMA.

Limitations

The following are the limitations with Redshift Spectrum:

  • If the USAGE permission is granted to EXTERNAL SCHEMA, the user gains access to all of its tables.

  • Access to any of the external tables cannot be explicitly granted or revoked.

  • The creation of Redshift managed tables (not EXTERNAL TABLES) is not permitted within an "EXTERNAL SCHEMA".

  • The creation of secure views is not permitted within an EXTERNAL SCHEMA.

Privacera has never managed external tables due to the limitations listed above. By default, we manage permissions for external schemas at the schema level.

Support for Row Level Filter and Column Masking on the basis of Secure Views on EXTERNAL SCHEMA is possible, but only with the user's CONSENT, as the user will also have direct access to the EXTERNAL TABLE If they query the table's data, neither the Row Level Filter nor the Column Masking will be applied.

Note

We do not recommend this solution, but if you agree that users will not query the data directly (via external tables), we can enable it by adding a REDSHIFT_ENABLE_EXTERNAL_SCHEMA_SUPPORT property (default behavior is set to false).

Proposed Solution

On an EXTERNAL TABLE, we supports Row Level Filter and Column Masking to a limited extent.

  • Instead of creating a table, we create a secure view with the _secure postfix added to the schema name (as we cannot create Redshift views inside external schemas).

  • To GRANT access to secure view, we must grant USAGE permission to the Source Schema because the secure view schema will be separated from the EXTERNAL SCHEMA. As a result, permission is granted to the source (actual) table.

  • Only Select Permission to the EXTERNAL TABLE is supported. DataAdmin permission is ineffective because USAGE permission to EXTERNAL SCHEMA allows direct access to EXTERNAL TABLE. 

Property Configuration

Note

Due to limitations, EXTERNAL SCHEMA support for Row Level Filter and Column Masking is not recommended. 

This following property should not be enabled without consent after reading the documentation.

Property Description Default Value Example
REDSHIFT_ENABLE_EXTERNAL_SCHEMA_SUPPORT Set this property to true to enable Row Level Filter and Column Masking policies on secure views after reading the limitations. false true/false

The values of the following properties must be left blank: 

REDSHIFT_SECURE_VIEW_NAME_PREFIX: ""
REDSHIFT_SECURE_VIEW_NAME_POSTFIX: ""

The values of the following properties must be set:

REDSHIFT_SECURE_VIEW_SCHEMA_NAME_PREFIX: ""
REDSHIFT_SECURE_VIEW_SCHEMA_NAME_POSTFIX: "_secure"
For more information about the properties, see Redshift Custom Properties.