Skip to content

Redshift

This topic covers how you can configure PolicySync Redshift access control using Privacera Manager.

CLI Configuration

  1. SSH to the instance where Privacera is installed.

  2. Run the following commands.

    cd ~/privacera/privacera-manager
    cp config/sample-vars/vars.policysync.redshift.yml config/custom-vars/
    vi config/custom-vars/vars.policysync.redshift.yml
    
  3. Set the properties for your specific installation. For property details and description, see the Configuration Properties section that follows.

    Note

    Along with the above properties, you can add custom properties that are not included by default. For more information about these properties, see Redshift Connector.

  4. Run the following commands.

    cd ~/privacera/privacera-manager/
    ./privacera-manager.sh update
    

Configuration Properties

JDBC configuration

Name Type Default Required Description
REDSHIFT_JDBC_URL string Yes

Specifies the JDBC URL for the Amazon Redshift connector.

REDSHIFT_JDBC_USERNAME string Yes

Specifies the JDBC username to use.

For PolicySync to push policies to Amazon Redshift, this user must have superuser privileges.

REDSHIFT_JDBC_PASSWORD string Yes

Specifies the JDBC password to use.

REDSHIFT_JDBC_DB string Yes

Specifies the name of the JDBC database to use.

PolicySync also uses the connection to this database to load metadata and create principals such as users and groups.

REDSHIFT_DEFAULT_USER_PASSWORD string Yes

Specifies the password to use when PolicySync creates new users.

The password must meet the following requirements:

  • It must be between 8 and 64 characters long.
  • It must contain at least one uppercase letter, one lowercase letter, and one number.
  • It can use any ASCII character with the ASCII codes 33–126 except: ', ", ,, /, or @
REDSHIFT_OWNER_ROLE string No

Specifies the role that owns the resources managed by PolicySync. You must ensure that this user exists as PolicySync does not create this user.

  • If a value is not specified, resources are owned by the creating user. In this case, the owner of the resource will have all access to the resource.

  • If a value is specified, the owner of the resource will be changed to the specified value.

The following resource types are supported:

  • Database
  • Schemas
  • Tables
  • Views

Load keys and intervals

Name Type Default Required Description
REDSHIFT_LOAD_RESOURCES_KEY string load_from_database_columns No

Specifies how PolicySync loads resources from Amazon Redshift. The following values are allowed:

  • load_md: Load resources from Amazon Redshift with a top-down resources approach, that is, it first loads the databases and then the schemas followed by tables and its columns.

  • load_from_database_columns: Load resources one by one for each resource type that is, it loads all databases first, then it loads all schemas in all databases, followed by all tables in all schemas and its columns. This mode is recommended since it is faster than the load mode.

REDSHIFT_RESOURCE_SYNC_INTERVAL integer 60 No

Specifies the interval in seconds for PolicySync to wait before checking for new resources or changes to existing resources.

REDSHIFT_PRINCIPAL_SYNC_INTERVAL integer 420 No

Specifies the interval in seconds for PolicySync to wait before reconciling principals with those in the data source, such as users, groups, and roles. When differences are detected, PolicySync updates the principals in the data source accordingly.

REDSHIFT_PERMISSION_SYNC_INTERVAL integer 540 No

Specifies the interval in seconds for PolicySync to wait before reconciling Apache Ranger access control policies with those in the data source. When differences are detected, PolicySync updates the access control permissions on data source accordingly.

REDSHIFT_AUDIT_SYNC_INTERVAL integer 30 No

Specifies the interval in seconds to elapse before PolicySync retrieves access audits and saves the data in Privacera.

Resources management

Name Type Default Required Description
REDSHIFT_MANAGE_DATABASE_LIST string No

Specifies a comma-separated list of database names for which PolicySync manages access control. If unset, access control is managed for all databases. If specified, use the following format. You can use wildcards. Names are case-sensitive.

An example list of databases might resemble the following: testdb1,testdb2,sales db*.

If specified, REDSHIFT_IGNORE_DATABASE_LIST takes precedence over this setting.

REDSHIFT_MANAGE_SCHEMA_LIST string No

Specifies a comma-separated list of schema names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

Use the following format when specifying a schema:

<DATABASE_NAME>.<SCHEMA_NAME>

If specified, REDSHIFT_IGNORE_SCHEMA_LIST takes precedence over this setting.

If you specify a wildcard, such as in the following example, all schemas are managed:

<DATABASE_NAME>.*

The specified value, if any, is interpreted in the following ways:

  • If unset, access control is managed for all schemas.
  • If set to none no schemas are managed.
REDSHIFT_MANAGE_TABLE_LIST string No

Specifies a comma-separated list of table names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

Use the following format when specifying a table:

<DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME>

If specified, REDSHIFT_IGNORE_TABLE_LIST takes precedence over this setting.

If you specify a wildcard, such as in the following example, all matched tables are managed:

<DATABASE_NAME>.<SCHEMA_NAME>.*

The specified value, if any, is interpreted in the following ways:

  • If unset, access control is managed for all tables.
  • If set to none no tables are managed.
REDSHIFT_IGNORE_DATABASE_LIST string No

Specifies a comma-separated list of database names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all databases are subject to access control.

For example:

testdb1,testdb2,sales_db*

This setting supersedes any values specified by REDSHIFT_MANAGE_DATABASE_LIST.

REDSHIFT_IGNORE_SCHEMA_LIST string No

Specifies a comma-separated list of schema names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all schemas are subject to access control.

For example:

testdb1.schema1,testdb2.schema2,sales_db*.sales*

This setting supersedes any values specified by REDSHIFT_MANAGE_SCHEMA_LIST.

REDSHIFT_IGNORE_TABLE_LIST string No

Specifies a comma-separated list of table names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all tables are subject to access control. Specify tables using the following format:

<DATABASE_NAME>.<SCHEMA_NAME>.<TABLE_NAME>

This setting supersedes any values specified by REDSHIFT_MANAGE_TABLE_LIST.

Users/Groups/Roles management

Name Type Default Required Description
REDSHIFT_USER_NAME_REPLACE_FROM_REGEX string [~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}] No

Specifies a regular expression to apply to a username and replaces each matching character with the value specified by the REDSHIFT_USER_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

REDSHIFT_USER_NAME_REPLACE_TO_STRING string _ No

Specifies a string to replace the characters matched by the regex specified by the REDSHIFT_USER_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

REDSHIFT_GROUP_NAME_REPLACE_FROM_REGEX string [~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}] No

Specifies a regular expression to apply to a group and replaces each matching character with the value specified by the REDSHIFT_GROUP_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

REDSHIFT_GROUP_NAME_REPLACE_TO_STRING string _ No

Specifies a string to replace the characters matched by the regex specified by the REDSHIFT_GROUP_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

REDSHIFT_ROLE_NAME_REPLACE_FROM_REGEX string [~`$&+:;=?@#|'<>.^*()_%\\\\[\\\\]!\\\\-\\\\/\\\\\\\\{}] No

Specifies a regular expression to apply to a role name and replaces each matching character with the value specified by the REDSHIFT_ROLE_NAME_REPLACE_TO_STRING setting.

If not specified, no find and replace operation is performed.

REDSHIFT_ROLE_NAME_REPLACE_TO_STRING string _ No

Specifies a string to replace the characters matched by the regex specified by the REDSHIFT_ROLE_NAME_REPLACE_FROM_REGEX setting.

If not specified, no find and replace operation is performed.

REDSHIFT_USER_NAME_PERSIST_CASE_SENSITIVITY boolean false No

Specifies whether Amazon Redshift supports case sensitivity for users. Because case sensitivity in Amazon Redshift is global, enabling this enables case sensitivity for users, groups, roles, and resources.

REDSHIFT_GROUP_NAME_PERSIST_CASE_SENSITIVITY boolean false No

Specifies whether Amazon Redshift supports case sensitivity for groups. Because case sensitivity in Amazon Redshift is global, enabling this enables case sensitivity for users, groups, roles, and resources.

REDSHIFT_ROLE_NAME_PERSIST_CASE_SENSITIVITY boolean false No

Specifies whether Amazon Redshift supports case sensitivity for roles. Because case sensitivity in Amazon Redshift is global, enabling this enables case sensitivity for users, groups, roles, and resources.

REDSHIFT_ENABLE_CASE_SENSITIVE_IDENTIFIER boolean false No

Specifies whether Amazon Redshift preserves case for user, group, role, and resource names. By default, Amazon Redshift converts all user, group, role, and resource names to lowercase. If set to true, PolicySync enables case sensitivity on a per connection basis.

REDSHIFT_ENABLE_CASE_SENSITIVE_IDENTIFIER_QUERY string SET enable_case_sensitive_identifier=true; No

Specifies a query for Amazon Redshift that enables case sensitivity per connection. If you enable REDSHIFT_ENABLE_CASE_SENSITIVE_IDENTIFIER, then this setting defines the query that PolicySync runs.

REDSHIFT_USER_NAME_CASE_CONVERSION string lower No

Specifies how user name conversions are performed. The following options are valid:

  • lower: Convert to lowercase
  • upper: Convert to uppercase
  • none: Preserve case

This setting applies only if REDSHIFT_USER_NAME_PERSIST_CASE_SENSITIVITY is set to true.

REDSHIFT_GROUP_NAME_CASE_CONVERSION string lower No

Specifies how group name conversions are performed. The following options are valid:

  • lower: Convert to lowercase
  • upper: Convert to uppercase
  • none: Preserve case

This setting applies only if REDSHIFT_GROUP_NAME_PERSIST_CASE_SENSITIVITY is set to true.

REDSHIFT_ROLE_NAME_CASE_CONVERSION string lower No

Specifies how role name conversions are performed. The following options are valid:

  • lower: Convert to lowercase
  • upper: Convert to uppercase
  • none: Preserve case

This setting applies only if REDSHIFT_ROLE_NAME_PERSIST_CASE_SENSITIVITY is set to true.

REDSHIFT_CREATE_USER boolean true No

Specifies whether PolicySync creates local users for each user in Privacera.

REDSHIFT_CREATE_USER_ROLE boolean true No

Specifies whether PolicySync creates local roles for each user in Privacera.

REDSHIFT_MANAGE_USERS boolean true No

Specifies whether PolicySync maintains user membership in roles in the Amazon Redshift data source.

REDSHIFT_MANAGE_GROUPS boolean true No

Specifies whether PolicySync creates groups from Privacera in the Amazon Redshift data source.

REDSHIFT_MANAGE_ROLES boolean true No

Specifies whether PolicySync creates roles from Privacera in the Amazon Redshift data source.

REDSHIFT_MANAGE_USER_LIST string No

Specifies a comma-separated list of user names for which PolicySync manages access control. You can use wildcards. Names are case-sensitive.

If not specified, PolicySync manages access control for all users.

If specified, REDSHIFT_IGNORE_USER_LIST takes precedence over this setting.

An example user list might resemble the following: user1,user2,dev_user*.

REDSHIFT_MANAGE_GROUP_LIST string No

Specifies a comma-separated list of group names for which PolicySync manages access control. If unset, access control is managed for all groups. If specified, use the following format. You can use wildcards. Names are case-sensitive.

An example list of projects might resemble the following: group1,group2,dev_group*.

If specified, REDSHIFT_IGNORE_GROUP_LIST takes precedence over this setting.

REDSHIFT_MANAGE_ROLE_LIST string No

Specifies a comma-separated list of role names for which PolicySync manages access control. If unset, access control is managed for all roles. If specified, use the following format. You can use wildcards. Names are case-sensitive.

An example list of projects might resemble the following: role1,role2,dev_role*.

If specified, REDSHIFT_IGNORE_ROLE_LIST takes precedence over this setting.

REDSHIFT_IGNORE_USER_LIST string No

Specifies a comma-separated list of user names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all users are subject to access control.

This setting supersedes any values specified by REDSHIFT_MANAGE_USER_LIST.

REDSHIFT_IGNORE_GROUP_LIST string No

Specifies a comma-separated list of group names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all groups are subject to access control.

This setting supersedes any values specified by REDSHIFT_MANAGE_GROUP_LIST.

REDSHIFT_IGNORE_ROLE_LIST string No

Specifies a comma-separated list of role names that PolicySync does not provide access control for. You can specify wildcards. If not specified, all roles are subject to access control.

This setting supersedes any values specified by REDSHIFT_MANAGE_ROLE_LIST.

REDSHIFT_USER_ROLE_PREFIX string priv_user_ No

Specifies the prefix that PolicySync uses when creating local users. For example, if you have a user named <USER> defined in Privacera and the role prefix is priv_user_, the local role is named priv_user_<USER>.

REDSHIFT_GROUP_ROLE_PREFIX string priv_group_ No

Specifies the prefix that PolicySync uses when creating local roles. For example, if you have a group named etl_users defined in Privacera and the role prefix is prefix_, the local role is named prefix_etl_users.

REDSHIFT_ROLE_ROLE_PREFIX string priv_role_ No

Specifies the prefix that PolicySync uses when creating roles from Privacera in the Amazon Redshift data source.

For example, if you have a role in Privacera named finance defined in Privacera and the role prefix is role_prefix_, the local role is named role_prefix_finance.

REDSHIFT_USE_NATIVE_PUBLIC_GROUP boolean true No

Specifies whether PolicySync uses the Amazon Redshift native public group for access grants whenever a policy refers to a public group. The default value is true.

REDSHIFT_MANAGE_USER_FILTERBY_GROUP boolean false No

Specifies whether to manage only the users that are members of groups specified by REDSHIFT_MANAGE_GROUP_LIST. The default value is false.

REDSHIFT_MANAGE_USER_FILTERBY_ROLE boolean false No

Specifies whether to manage only users that are members of the roles specified by REDSHIFT_MANAGE_ROLE_LIST. The default value is false.

Access control management

Name Type Default Required Description
REDSHIFT_ENABLE_VIEW_BASED_MASKING boolean true No

Specifies whether to use secure view based masking. The default value is true.

REDSHIFT_ENABLE_VIEW_BASED_ROW_FILTER boolean true No

Specifies whether to use secure view based row filtering. The default value is true.

While Amazon Redshift supports native filtering, PolicySync provides additional functionality that is not available natively. Enabling this setting is recommended.

REDSHIFT_SECURE_VIEW_CREATE_FOR_ALL boolean true No

Specifies whether to create secure views for all tables and views that are created by users. If enabled, PolicySync creates secure views for resources regardless of whether masking or filtering policies are enabled.

REDSHIFT_MASKED_NUMBER_VALUE integer 0 No

Specifies the default masking value for numeric column types.

REDSHIFT_MASKED_TEXT_VALUE string <MASKED> No

Specifies the default masking value for text and string column types.

REDSHIFT_SECURE_VIEW_NAME_PREFIX string No

Specifies a prefix string for secure views. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name prefix, specify a value for this setting. For example, if the prefix is dev_, then the secure view name for a table named example1 is dev_example1.

REDSHIFT_SECURE_VIEW_NAME_POSTFIX string _secure No

Specifies a postfix string for secure views. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name postfix, specify a value for this setting. For example, if the postfix is _dev, then the secure view name for a table named example1 is example1_dev.

REDSHIFT_SECURE_VIEW_SCHEMA_NAME_PREFIX string No

Specifies a prefix string to apply to a secure schema name. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name prefix, specify a value for this setting. For example, if the prefix is dev_, then the secure view schema name for a schema named example1 is dev_example1.

REDSHIFT_SECURE_VIEW_SCHEMA_NAME_POSTFIX string No

Specifies a postfix string to apply to a secure view schema name. By default view-based row filter and masking-related secure views have the same schema name as the table schema name.

If you want to change the secure view schema name postfix, specify a value for this setting. For example, if the postfix is _dev, then the secure view name for a schema named example1 is example1_dev.

REDSHIFT_SECURE_VIEW_NAME_REMOVE_SUFFIX_LIST string No

Specifies a suffix to remove from a table or view name. For example, if the table is named example_suffix you can remove the _suffix string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes.

REDSHIFT_SECURE_VIEW_SCHEMA_NAME_REMOVE_SUFFIX_LIST string No

Specifies a suffix to remove from a schema name. For example, if a schema is named example_suffix you can remove the _suffix string. This transformation is applied before any custom prefix or postfix is applied.

You can specify a single suffix or a comma separated list of suffixes.

REDSHIFT_GRANT_UPDATES boolean false No

Specifies whether PolicySync performs grants and revokes for access control and creates, updates, and deletes queries for users, groups, and roles. The default value is false.

REDSHIFT_GRANT_UPDATES_MAX_RETRY_ATTEMPTS integer 2 No

Specifies the maximum number of attempts that PolicySync makes to execute a grant query if it is unable to do so successfully. The default value is 2.

REDSHIFT_ENABLE_DATA_ADMIN boolean true No

This property is used to enable the data admin feature. With this feature enabled you can create all the policies on native tables/views, and respective grants will be made on the secure views of those native tables/views. These secure views will have row filter and masking capability. In case you need to grant permission on the native tables/views then you can select the permission you want plus data admin in the policy. Then those permissions will be granted on both the native table/view as well as its secure view.

Access audits management

Name Type Default Required Description
REDSHIFT_AUDIT_ENABLE boolean false No

Specifies whether Privacera fetches access audit data from the data source.

REDSHIFT_AUDIT_EXCLUDED_USERS string REDSHIFT_JDBC_USERNAME No

Specifies a comma separated list of users to exclude when fetching access audits. For example: "user1,user2,user3".

REDSHIFT_AUDIT_INITIAL_PULL_MINUTES integer 30 No

Specifies the initial delay, in minutes, before PolicySync retrieves access audits from Amazon Redshift.

Note

As there is no concept of role in Redshift, PolicySync internally creates a group for that user and grants permission to that group to which the user belongs.

For example, when you add a role in the Privacera Portal, you will notice that CREATE GROUP priv_role_rol1 is created instead of CREATE ROLE priv_role_rol1.

Limitations with Dynamic Masking and Row Filter

  • Updating Group/Role will not update Dynamic Row Filter or Dynamic Masking.

  • In case of dynamic view, you must have Usage permission on both VIEW Schema as well as Table Schema.

  • Assuming row filter is enabled, you have cleared RocksDB cache and PolicySync is up, you will not be able to disable the row filter.